INDIRECT function to reference A2 on all sheets working for some, but not others.

[carlito2002wgn]

Hi,

I am using the INDIRECT function to pull the value in A2 on all sheets into a summary sheet. This is my first time using INDIRECT; it's pretty cool! In my Summary sheet, column B contains study numbers. Column F is where the INDIRECT function is, and it looks at the study numbers in column B, and then pulls in the value in A2 of the sheet with the same name as the study number (sheet's names are study numbers). This works for some sheets, but not all.

I'll attach a file so that you can see what I'm talking about (this workbook normally has a lot more data). Can you help me understand why INDIRECT works in some cases, but not all?

Thank you!
Carlos

[XLent]

your {offending} tab names are stored as numbers with leading zeroes only appearing c/o number format and so ,for ex., sheet 350215 does not exist.

either, store as text, or if tab names always at least 8 chars long: =INDIRECT("'"&TEXT(B2,"00000000")&"'!A2")

[José Augusto]

Hi
The zero or zeros to the left are only on format not in the value
Try

Please Login or Register  to view this content.

[Fluff13]

Try

Please Login or Register  to view this content.

The values in col B where it fails are formatted to look like 00350230, but are infact 350230