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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies