Formula between worksheets

Question

I am working in a spreadsheet with 20 worksheets. On the main index worksheet I want a formula to gather information from each sheet.

Example: ='009'!E9

That is taking the data from worksheet 009, cell E9 and bringing it to the index page.

Is there any formula where i can have it automatically change from worksheet 009 to 010, 011, 012 etc? I know how to anchor the E9 so that doesn't change but I can not figure out of there is a way to switch worksheet numbers. Nothing I have done so far has worked.

Any help would be appreciated. Thank you!

Answer

Workbook has a tab named 009, and another named 010.

On a different sheet, format col A as text. In A1:A2 enter

009 010

In B1, enter

=INDIRECT(A1&"!E9")

It will return the value in cell E9 of tab 009.

Now copy that formula in B1 down to B2. Cell B2 will return the value in cell E9 of tab 010.