SOLVED
Home

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
Bill114
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?

 

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