SOLVED

Execute a concatenated text string?

%3CLINGO-SUB%20id%3D%22lingo-sub-362827%22%20slang%3D%22en-US%22%3EExecute%20a%20concatenated%20text%20string%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362827%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20referencing%20a%20specific%20named%20tab%20in%20a%20reference%20workbook%2C%20and%20have%20the%20following%20command.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCONCATENATE(%22'%5BReference%20file%20example.xlsx%5D%22%2CB8%2C%20%22'!%24B2%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20present%20(separate)%20workbook%2C%20I%20have%20a%20cell%20B8%20labelled%20Monday%20and%20formatted%20as%20a%20text%20string.%26nbsp%3B%20When%20I%20hit%20enter%2C%20the%20following%20string%20appears%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'%5BReference%20file%20example.xlsx%5DMonday'!%24B2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20be%20able%20to%20execute%20the%20command.%26nbsp%3B%20%26nbsp%3BI%20know%20it%20works%2C%20because%20if%20I%20copy%20and%20paste%20as%20plain%20text%20into%20another%20cell%2C%20then%20add%20an%20%3D%20sign%20at%20the%20beginning%2C%20it%20executes%20the%20command.%26nbsp%3B%20How%20can%20I%20get%20the%20original%20string%20to%20execute%20the%20concatenated%20command%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-362827%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362967%22%20slang%3D%22en-US%22%3ERe%3A%20Execute%20a%20concatenated%20text%20string%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362967%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20got%20it%20to%20work%20the%20way%20you%20have%20it.%26nbsp%3B%20%26nbsp%3BI%20don't%20know%20what%20I%20was%20doing%20wrong%2C%20but%20I%20found%20a%20work%20around%20using%20the%202%20step%20approach%2C%20which%20actually%20works%20better%20for%20what%20I%20was%20doing.%26nbsp%3B%20%26nbsp%3BSomething%20I%20did%20in%20the%20past%20couple%20of%20hours%20fixed%20the%20syntax.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20VERY%20MUCH%20for%20the%20help!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362907%22%20slang%3D%22en-US%22%3ERe%3A%20Execute%20a%20concatenated%20text%20string%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362907%22%20slang%3D%22en-US%22%3E%3CP%3EFormula%20works%2C%20I%20reproduced%20it%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20475px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86102i8A374D4BA5C569CB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20sample%20file%20to%20check%20what's%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362865%22%20slang%3D%22en-US%22%3ERe%3A%20Execute%20a%20concatenated%20text%20string%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362865%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20able%20to%20use%20the%20indirect%20function%2C%20but%20had%20to%20do%20it%20in%202%20steps%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%2C%20for%20say%2C%20cell%20H25%20I%20place%20the%20formula%3A%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCONCATENATE(%22'%5BReference%20file%20example.xlsx%5D%22%2CB20%2C%20%22'!%24B2%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20produces%20the%20string%26nbsp%3B%3C%2FP%3E%3CP%3E'%5BReference%20file%20example.xlsx%5DMonday'!%24B2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20then%20use%20the%20statement%3C%2FP%3E%3CP%3E%3DINDIRECT(H25)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20produce%20the%20desired%20result%3C%2FP%3E%3CP%3EMonday%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20do%20this%20in%202%20steps%2C%20but%20I'd%20rather%20do%20it%20all%20in%20one%20statement.%26nbsp%3B%20If%20I%20do%2C%20it%20gives%20me%20a%20%23REF!%20error.%26nbsp%3B%20The%20other%20workbook%20is%20open.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20the%20help!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362832%22%20slang%3D%22en-US%22%3ERe%3A%20Execute%20a%20concatenated%20text%20string%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362832%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20need%20to%20wrap%20with%20INDIRECT%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1772742%22%20slang%3D%22en-US%22%3ERe%3A%20Execute%20a%20concatenated%20text%20string%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1772742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bhoping%20you%20can%20help%20with%20a%20similar%20issue.%26nbsp%3B%20Cell%20B14%20is%20where%20i%20am%20trying%20to%20get%20a%20relatively%20complicated%20output.%26nbsp%3B%20When%20i%20wrap%20the%20existing%20concatenate%20formula%20with%20Indirect%2C%20it%20get%20a%20%23REF%20error%2C%20and%20when%20I%20do%20a%20separate%20cell%20with%20%3DIndirect(%24B%2414)%2C%20i%20still%20get%20the%20%23REF%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eidea%20behind%20the%20formula%20for%20B14%20is%3A%3C%2FP%3E%3CP%3E(B10%2FB11)%2C%20Rounded%20as%20per%20the%20values%20in%20B12%20and%20B13%2C%20so%20in%20this%20case%3A%20ROUNDUP(B10%2FB11%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20even%20attempted%20a%20few%20reworkings%20of%20the%20concatenate%20formula%20to%20nest%20indirect%20within%20the%20formula%20to%20pull%20values%20in%20to%20the%20string%2C%20but%20all%20errored.%26nbsp%3B%20Any%20assistance%20would%20be%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JoeCavasin_0-1602532289449.png%22%20style%3D%22width%3A%20531px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226108i4023E64DAC2D6257%2Fimage-dimensions%2F531x372%3Fv%3D1.0%22%20width%3D%22531%22%20height%3D%22372%22%20role%3D%22button%22%20title%3D%22JoeCavasin_0-1602532289449.png%22%20alt%3D%22JoeCavasin_0-1602532289449.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3Ejoe%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1772823%22%20slang%3D%22en-US%22%3ERe%3A%20Execute%20a%20concatenated%20text%20string%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1772823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F248194%22%20target%3D%22_blank%22%3E%40JoeCavasin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20named%20formula%20to%20evaluate%20the%20expression%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226121i8CEC08ADA5F16554%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ebut%20with%20it%20workbook%20shall%20be%20saved%20as%20macro-enabled%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
Best Response confirmed by Bill114 (New Contributor)
Solution

You need to wrap with INDIRECT

Highlighted

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

 

 

 

 

 

Highlighted

Formula works, I reproduced it

image.png

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

Highlighted

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

Highlighted

@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

 

Highlighted

@JoeCavasin 

You may use named formula to evaluate the expression as

image.png

but with it workbook shall be saved as macro-enabled