Referencing in Excel

Question

I want to reference this cell in another worksheet for my current worksheet, which is calculated by

=[2015_RunRate.xlsx]January!$C$66

However, I want to replace the word "January" in that reference with the value from cell C19 in my current worksheet.

I tried something along the lines of:

=([2015_RunRate.xlsx])([Current_Worksheet.xlsx]!$C$19)!$C$66

That clearly didn't work. Any ideas?

Thanks.

Answer 1

You can use Indirect function. E.g.:

INDIRECT("[Book2.xlsx]"&B1&"!$A$1")

Where B1 is a cell containing text string with name of a sheet in the other workbook. Like "January", "February", etc.

Answer 2

You can use the INDIRECT function to convert a string you've stitched together into a valid cell reference.

=INDIRECT("[2015_RunRate.xlsx]"&$C$19&"!C66")

The INDIRECT function cannot reference a closed workbook. It is also considered a volatile¹ function. Since C66 is now nothing but text that looks like a cell reference, there is no need to add the absolute row and column indicators (e.g. $). The text will not change when moved or copied to another location.

---

¹ _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.}