0

I have a formula referencing a cell in another workbook.

='[Workbook2.xlsx]5683-4'!$C$23

The 5683-4 is a variable cell value in workbook 1 found at A2. How can I build a formula where the the formula changes based on the value of the A column?

For example: If column A3 is 5683-5 then the formula would populate

='[Workbook2.xlsx]5683-5'!$C$23
  • The only method is INDIRECT, but the drawback to INDIRECT is that the workbook being references must be open to get the values. – Scott Craner May 7 '18 at 17:38
1

If your formula is in Workbook1 and A3 contains the text name of a worksheet (i.e. "5683-5"), then this formula should do what you're looking for:

=INDIRECT("'[Workbook2.xlsx]"&A3&"'!$C$23")

The INDIRECT() function returns a valid cell or range reference from a text string. The formula above assembles the text string pointing to your other Workbook2 by concatenating the known parts (in quotes) with the variable part in A3.

|improve this answer|||||
  • Thank you to both of you. Bandersnatch's formula without = has worked but there is still one small issue. When I paste the formula, I have to double click the pasted formula then click enter for the new value to populate. Is there a workaround? – ArtimousGordon May 7 '18 at 18:03
  • I figured it out. Issue resolved.Thank you again for your help – ArtimousGordon May 7 '18 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.