Same cell from Multiple tabs

I am after help with pulling the same cell from mulitple tabs/worksheets.

Eg. i have 10 tabs and i want data from cell L20 from each tab to be listed on tab 11 from A1 to A10. but i need to be able to just copy the formula down the A column if i end up adding in more tabs.

My brain just isn't functioning today.

Thanks

Matt

You can use the INDIRECT() function to build formulas from pieces of information like that so that it increments as you copy down. Read up on that function.

I can't see any need for VBA!

The formulas should work

='1'!L20

Attach the workbook for someone to check

For INDIRECT to work in this manner you should really use a consistent sheet naming convention (ie 1,2, 3 to 7) - it is the inconsistency (1, 2, sheet3, sheet4 etc...) that presently precludes this approach from working.

If the sheet names are inconsistent then you may wish to use alternative methods (ie store the sheet name in each row in a given Column and refer to that in the INDIRECT call)

FWIW, be aware that INDIRECT is Volatile (see link in sig. for more info.) - if you over use this function you may find overall performance of your file is affected when run in Auto Calculation mode.

If they are all digits then why do you have "Sheet " in the sheet name ?

=INDIRECT("'"&ROWS(A$1:A1)&"'!L2")

again though I make the point that if you also wish to include sheets that do not follow this rule then simply add the sheet name in each row and refer to it

=INDIRECT("'"&$Z1&"'!L2")

where Z1, Z2 etc holds the respective sheet name to be used.

INDIRECT in essence executes the text string generated within the parentheses as a range reference - so you simply need to ensure the string generated matches that required of the "standard" reference were you to create it manually.