SOLVED

Execute a concatenated text string?

Copper Contributor

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?

 

6 Replies
best response confirmed by Bill114 (Copper Contributor)
Solution

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

image.png

Could you attach sample file to check what's wrong?

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!!

@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

 

JoeCavasin_0-1602532289449.png

 

Thanks

joe

 

@JoeCavasin 

You may use named formula to evaluate the expression as

image.png

but with it workbook shall be saved as macro-enabled

1 best response

Accepted Solutions
best response confirmed by Bill114 (Copper Contributor)
Solution

You need to wrap with INDIRECT

View solution in original post