Mar 07 2019 08:41 AM - edited Mar 07 2019 08:43 AM
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?
Mar 07 2019 08:45 AM
SolutionYou need to wrap with INDIRECT
Mar 07 2019 09:31 AM - edited Mar 07 2019 09:37 AM
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!!
Mar 07 2019 10:25 AM
Formula works, I reproduced it
Could you attach sample file to check what's wrong?
Mar 07 2019 11:50 AM
I just got it to work the way you have it. I don't know what I was doing wrong, but I found a work around using the 2 step approach, which actually works better for what I was doing. Something I did in the past couple of hours fixed the syntax.
Thank you VERY MUCH for the help!!
Oct 12 2020 12:53 PM
@Sergei Baklan hoping you can help with a similar issue. Cell B14 is where i am trying to get a relatively complicated output. When i wrap the existing concatenate formula with Indirect, it get a #REF error, and when I do a separate cell with =Indirect($B$14), i still get the #REF error.
idea behind the formula for B14 is:
(B10/B11), Rounded as per the values in B12 and B13, so in this case: ROUNDUP(B10/B11,0)
I've even attempted a few reworkings of the concatenate formula to nest indirect within the formula to pull values in to the string, but all errored. Any assistance would be appreciated
Thanks
joe
Oct 12 2020 01:17 PM
You may use named formula to evaluate the expression as
but with it workbook shall be saved as macro-enabled
Mar 07 2019 08:45 AM
SolutionYou need to wrap with INDIRECT