Search code examples
excelreferencespreadsheetexcel-indirect

Indirect Function with Sheet Names

I have a list of all of the sheet names in my workbook on Sheet1 and I am trying, without success, to use the INDIRECT function to reference a cell on another sheet using said name. I am aware that this is a common question, I have looked at about 10 other questions/answers on this website in addition to others. For some reason, those solutions are not working and I am kinda losing my mind.

  • The sheet names are all in column A
  • They are named after dates (format mm-dd-yy) <- I'm assuming this is the problem?
  • The cell that I am trying to reference is always in O1 on every sheet in the workbook.

I have tried:

=INDIRECT(A1&"!O1")
=INDIRECT("'"A1"'&!O1")

and a few other amalgamations. Again, I have looked into so many other solutions at this point I am just left with asking for help with my situation, which is apparently unique (or more likely it is some blaring user error). Please help me before immediately removing the post.

Solution

  • If I have this right, the names of your sheets are dates, and you have the names listed as dates in Column A. So, they will appear as dates, but are actually Excel's background number for a date (days since 1/1/1900).

    If you use the Formulas tab in the ribbon to Evaluate Formula, you should see the date pop in to your formula as a 5 digit number.

    If you are not doing anything else with these dates, you could try selecting column A and formatting as text. This solved the issue in my test setup. This will still work if you are using the date for other functions, but you will have to tell Excel it's a date using DateValue()

    After formatting Column A as text, I used =INDIRECT("'"&A1&"'!O1")