Forum Discussion
Execute a concatenated text string?
I am referencing a specific named tab in a reference workbook, and have the following command.
=CONCATENATE("'[Reference file example.xlsx]",B8, "'!$B2")
In my present (separate) workbook, I have a cell B8 labelled Monday and formatted as a text string. When I hit enter, the following string appears:
'[Reference file example.xlsx]Monday'!$B2
I need to be able to execute the command. I know it works, because if I copy and paste as plain text into another cell, then add an = sign at the beginning, it executes the command. How can I get the original string to execute the concatenated command?
You need to wrap with INDIRECT
6 Replies
You need to wrap with INDIRECT
I was able to use the indirect function, but had to do it in 2 steps:
In, for say, cell H25 I place the formula:
=CONCATENATE("'[Reference file example.xlsx]",B20, "'!$B2")
It produces the string
'[Reference file example.xlsx]Monday'!$B2
I can then use the statement
=INDIRECT(H25)
To produce the desired result
Monday
I can do this in 2 steps, but I'd rather do it all in one statement. If I do, it gives me a #REF! error. The other workbook is open.
I appreciate the help!!
Formula works, I reproduced it
Could you attach sample file to check what's wrong?
