Formula help please (lookup across multiple sheets)

[froggybsb03]

I'm trying to create a workbook function and I'm not sure how to do it, hopefully you can help me at least understand what I'm trying to do. I am organizing several hundred workbooks into a dozen or so groupings and I want to automate the value lookup (instead of copy and paste into a whole separate document which is what we've been doing).



I have a workbook with multiple sheets (anywhere from two or three to thirty or more). I want to make a summary sheet at the beginning of the workbook that will list certain values from the rest of the sheets in a list, like a table of contents or summary. The fields to be looked up will always be the same fields (B7, B8, D40, etc) across all sheets. Right now I'm building the summary page by manually typing each lookup for each field for each sheet (I am typing three different lookup formulas for every single line on the summary sheet, even if I copy/paste I still have to update either the sheet name or the field number). Is there a way to at least reference one sheet at a time (pull B7, B8, and D40 from sheet1, then sheet2, etc), or better yet, reference by sheet position regardless of sheet name? For example, on the {summary sheet fields 4A, 4B, 4C} I want to display [sheet2 B7, B8, D40]. Then {summary sheet 5A, 5B, 5C} I want to display [sheet3 B7, B8, D40], and so on. Is there a better way to do this than retyping the formula into each and every cell on the summary sheet?

Thanks!

[xor]

Maybe you can use the attached.

[xor]

I tried to help you with something you asked for help to.

It would be nice to know if you have even seen my post #2!
Or is that too much to expect?