use a string to reference a sheet

hello

Thanks again to everyone for all their help. The atatched sheet should show what I'm looking for pretty well, but here's my problem:

I have a summary sheet that has a column called "sheet name" for about 5000 sheets, then another colum for the "total" cell in each of those sheets. I would like to do a standard ='sheet'!D5 type reference, except use the value in the name column as the sheet name in my reference.

Something like

=' & A2 & '!D5
=' & A3 & '!D5
=' & A4 & '!D5
=' & A5 & '!D5
=' & A6 & '!D5

Reason is: I could just do this formula once, and then fill-down, and everything would be set. Excell would change the A$ part each time.

This brings me to another problem: Could I get the D5 part to be constant while filling down, while the A$ part changes?

Thanks! That works great!

Please Login or Register  to view this content.

This will will check all the worksheets in your workbook, and put the reference