Unable to write data to different Excel sheets after specifiying filename in Formula Tool

[Paul_s_Moody]

I'm working with a block of member data from a pension scheme and after cleaning and transforming need to generate an Excel file for use in downstream processing.

The data needs to be written to different sheets depending on categories specified in Filer tools.

If I "manually" set up the output tools with the complete filename and sheet names then everything works and the data is written to the file and sheets appropriately.

However, I'm trying to make it easier to update the template so that the user can update the FileName in a formula tool rather than the "manual" process as there are a number of sheets that need to be updated.

I've set up a trivial eaxmple workflow to replicate the error:

I've configured the output tools with a "dummy" filename and sheet and set to change entire path:

However, I'm getting an error in each output tool that "You must specify a sheet name"...

Any ideas what I need to to in the configuration to get things to work?

[Paul_s_Moody]

After talking through with colleagues, the only approach we've managed to find is to add additional formula tools just before the output (and after the filter) setting, e.g.  [Filename] = [FileName] + "|||Dave" for each branch. 

[Christina_H · Accepted solution]

You just need to update your formula tool to add the sheet name onto the file path.  Then you only need a single output tool and it will automatically write the data onto the different sheets.

[Paul_s_Moody]

Thank you!

The actual data didn't have the sheet name needed, but I've been able to append using a second formula tool.

All works fine!