Excel Formula Issue with variable cell reference

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

1 Answer

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.