I have two sheets, one named Spread and one named TSN.
On 'Spread' I want to show the value that appears in TSN!A2 which is: 11/24/2023
If I put into Spread!A2 the formula =TSN!A2 I get the expected value.
If I put the value TSN in Spread!A1, then put in the formula: ==INDIRECT( "'" & $A1 &"'!A2" ) into Spread!A2 I get the value 45254. I don't get an error. Just a weird value. There is no reference to 45254 on TSN...
I want people to be able to input the name of the sheet in the cell Spread!A1 and have it populate data from the target sheet...
I've tried some variations of INDIRECT all lead to the same value.
Edit:
The value of TSN!B2 is 47.529. If I change the INDIRECT call to pull in B2 instead of A2 it gets the correct value. Does that mean the underlying value of A2 in TSN is actually some kind of hidden Time value?
45254 is the 'Excel date' value for 11/24/2023
You can test this by typing the value in a cell, then switching it from General to a Date format.
See more here