cell value = sheetname to pull information from in excel formulaSolved

Question

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

TrowaD · Answer

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

Tbonekiller · Answer

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?

TrowaD · Answer

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

Cell value = sheetname to pull information from in excel formula

3 replies

Your reply

Subscribe To Our Newsletter!