Dynamic Input Adding Sheet Name

[felixlam]

Hi all,

I am attempting to add a specific sheet name to a file path and have done it successfully for the same file with a different sheet using the formula tool and this formula:

[FullPath] + "|Sheet Name"

However I am now repeating the process, but with a different sheet. The result is an error message saying "does not match a sheet or named range" in the specified file "or the file is corrupted". Evidently the file cannot be corrupted as it works it perfectly fine for the other sheet. The only issue I can find is that for the first sheet. there is no space before the name, however for this second error-producing sheet, there is a space before the name of the sheet. I cannot change the sheet name in the excel file so is there a way to fix this with the formula tool/ formula I am already using. I also have tried adding a space before the name in the formula, adding an asterisks, and an undescore and none of those options worked. Any help would be greatly appreciated!

[apathetichell · Accepted solution]

your sheet name should be in in  a quote ie `my sheet name$` is your full sheet name. Also - use a batch macro - not dynamic input. 

[felixlam]

Thank you so much! I ended up just adding in the sheet name in the same way I was doing it but used ||| 'SheetName$' instead and that worked.