Cell value = sheetname to pull information from in excel formula

Solved
Tbonekiller
Posts
15
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
February 28, 2022
- Sep 29, 2021 at 12:22 PM
TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
- Oct 4, 2021 at 11:44 AM
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

TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
512
Sep 30, 2021 at 11:18 AM
Hi Tbonekiller,

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

Best regards,
Trowa
0
Tbonekiller
Posts
15
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
February 28, 2022

Sep 30, 2021 at 12:14 PM
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?
0
TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
512
Oct 4, 2021 at 11:44 AM
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
0