Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing a tab in a formula
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 :-) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing a tab in a formula
=SUM(INDIRECT(A3&"!A1:A2"))
On Oct 22, 1:25*pm, 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 :-) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing current tab name in formula | Excel Discussion (Misc queries) | |||
referencing date so you can use A value or B value in a formula | Excel Discussion (Misc queries) | |||
Referencing Workbook Name in Formula | Excel Worksheet Functions | |||
A formula referencing another | Excel Worksheet Functions | |||
Referencing a formula | Excel Discussion (Misc queries) |