Use output of concat function as a formula in Excel

Question

I don't want to write a macro (due to end user restrictions). I want the output of something like =CONCAT("='",A2,"'!K14") to be used as ='Sheet1'!K14. This doesn't happen unless i explicitly go into the cell & hit a enter.

Thanks. This worked. please post it as an answer so I can close this. — Anand Vasudevan
– Anand Vasudevan, Commented May 12, 2021 at 7:02

Dan Nixon · Accepted Answer · 2021-05-11 21:22:06Z

1

Just make sure you are aware of the performance limits with Indirect.

It is a volatile function, meaning it calculates every time there is a change to the workbook. Not a problem when you use it a couple of times, but will probably freeze up if you have thousands of them.

answered May 11, 2021 at 21:22

Dan Nixon

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SIE_Vict0ria · Accepted Answer · 2021-05-11 19:59:15Z

0

Use the Indirect Function. You can also simplify by replacing the CONCAT() function with just the AND operator. Try this:

=INDIRECT("'"&A2&"'!K14")

answered May 11, 2021 at 19:59

SIE_Vict0ria

1942 silver badges14 bronze badges

Collectives™ on Stack Overflow

Use output of concat function as a formula in Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related