Referencing a tab in a formula

[Wombat]

I want to make a formula in a summary table which will do a search in all the
other tabs in my work book.

The cell I want the information from is the same in each tab.

My problem is that at the moment, I am having to work on each formula to put
in the tab name in by hand. For example:

=sum(AAA!a1:a2)
=sum(BBB!a1:a2)

Is there a way I can spare myself having to type in the AAA, BBB, CCC part
in? I have all the tab names with the same formatting in the summary table
but I cant use a cell reference in the formula (eg: =sum('A3'!a1:a2))

Any ideas?

Thanks for any help

 

[muddan madhu]

=SUM(INDIRECT(A3&"!A1:A2"))

 

[Ms-Exl-Learner]

Try this…

Assume that you are having 10 Sheets. In Sheet 1 paste this formula it will
do sum of all sheets from Sheet2 to Sheet9 for the range A1 to A2.

=SUM(Sheet2:Sheet9!A1:A2)

If you have named your worksheets as per your desired names then just insert
a New Sheet in front of all sheets and name that sheet as Start.

Like that insert another sheet as last sheet in your workbook with the name
of End.

=SUM(start:end!A1:A2)

Don’t use this formula in any of the sheet which is referred in formula
(i.e. Start to End sheet) and that the Range you are referred that is (A1:A2)
because it will create circular reference issue.

Another method is just mention Start and End Sheet of your workbook on which
the formula should look.

=SUM(“Name of your Start Sheetâ€:â€Name of your End Sheetâ€!A1:A2)

If this post helps, Click Yes!