Lookup wordstring across mulitple worksheets & return sums

[se7098]

i have a workbook with 10 worksheets labeled with employees names.

i am attempting to create a summary sheet to capture a snapshot of the time
spent on each task.

The worksheets are basically the same with some minor task variances.

I need to lookup tasks across the multiple worksheets and return the sum for
each task.

i.e., lookup "creating and posting requisitions" on every worksheet and
return the sum of all employees performing that task

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Teethless mama]

=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3,4,5,6,7,8,9,10}&"!A1:A100"),C1,INDIRECT("Sheet"&{1,2,3,4,5,6,7,8,9,10}&"!B1:B100")))

or

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

....then use this formula

=SUMPRODUCT(--(THREED(Sheet1:Sheet10!A1:A100)=C1),THREED(Sheet1:Sheet10!B1:B100))

 

[Ashish Mathur]

Hi,

In such cases, it is best to consolidate data in one sheet and then use the
desired function.

1. Insert a blank sheet and type the columns heading (for data which which
we will pull from the child "sheets"). Say the headings are in A2 and B2;
2. Click on cell A3 and Go to Data > Consolidate;
3. In the reference box, select the range of data on the first sheet and
click Add;
4. Please repeat step 2 for all the sheets
5. Check the box for "Create Links to Source Data"
6. In the function box (at the top), select Count
7. Now click on OK

You will now see grouped rows and when you ungroup them (by clicking on the
plus sign), you will see all the data from the individual sheets. However
you will also see data being summarised by the COUNT function which we do
not need. This is the procedure you can use to remove all the COUNT rows:

1. Select any one column of the range;
2. In the Replace box (Ctrl+H), find COUNTA( and replace with COUNTAF(. Now
click on OK
3. All the COUNT() functions will be replaced with errors;
4. Now press Ctrl+G > Special
5. Select the formulas radio button and uncheck all boxes except errors
(This procedure will highlight all errors) and click on OK
5. Now simply do Alt+E+D+R ( to delete all rows which are selected)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Herbert Seidenberg]

Summarize the sheets in various ways
without using complicated formulas by
using Excel 2007 Pivot Tables.
http://www.savefile.com/files/1851070

 

[se7098]

Thanks for your help...i downloaded the morefunc add-in, however, when i try
this inserting the names of my spreadsheets in placed of sheet1:sheet10
i am getting an error.

 

[se7098]

thanks for your help...however, i keep receiving a cannot consolidate error
when i attempt this.

 

[se7098]

thank you for your help, however, i believe i have too many different
variable for pivot tables to be effect.