Cell value = sheetname to pull information from in excel formula

Report
Posts
12
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
September 30, 2021
-
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
-
I am trying to pull data from another sheet using the formula (located in B26) below, but I want to replace the sheetname ('9.60'! in this case) with the cell value from A26 which is the same as the sheetname I'm looking for. This way I can copy the formula instead of retyping individual sheetnames. The file stores all information on individual sheets named after the product. The file is looking for the last time this product was run and returning the date. I have over 180 sheets currently in the workbook with more added daily and I'm trying to make it self adjusting/updating as new sheets are added.

Formula in B column
{=MAX(IF('9.60'!C6:C3006=A26,'9.60'!A6:A3006,))}

Example
Column
A B
9.50 6/7/2021
9.60 8/18/2021

3 replies

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
487
Hi Tbonekiller,

Give the following formula a try:
{=MAX(IF(INDIRECT("'"&A26&"'!"&"C6:C3006")=A26,INDIRECT("'"&A26&"'!"&"A6:A3006"),))}

Best regards,
Trowa
Posts
12
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
September 30, 2021

That's what I had tried and I was getting a #REF error. Didn't know if I was typing something wrong or because my sheets are using numbers (i.e. 9.50, 9.60, 18.15, etc...) if that was causing an issue. OK so it looks like only the numbers ending in 0 are giving the #REF error (9.50, 7.50, 8.50, etc...). As long as it has an actual number (ex. 11.51) then the indirect formula works otherwise I have to specify the sheetname so that it covers that place holder 0.

Any thoughts?
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
487
Hi Tbonekiller,

Change the number format of the column containing the sheet references to text by going into the cell properties. That should keep the zero at the end.

Best regards,
Trowa