Previous Topic Next Topic

Cell value = sheetname to pull information from in excel formula

Solved/Closed
Tbonekiller Posts 16 Registration date Wednesday August 21, 2019 Status Member Last seen July 20, 2022 - Sep 29, 2021 at 12:22 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - 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

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
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
Answer 2 / 3
Tbonekiller Posts 16 Registration date Wednesday August 21, 2019 Status Member Last seen July 20, 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
Answer 3 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
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