Forum Discussion
Execute a concatenated text string?
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?
SergeiBaklan 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
You may use named formula to evaluate the expression as
but with it workbook shall be saved as macro-enabled
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!!
