Referencing a tab in a formula
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)
Dont 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!
--------------------
(Ms-Exl-Learner)
--------------------
"Wombat" wrote:
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 :-)
|