Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup wordstring across mulitple worksheets & return sums
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/comm...et.f unctions |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup wordstring across mulitple worksheets & return sums
=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)) "se7098" wrote: 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/comm...et.f unctions |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup wordstring across mulitple worksheets & return sums
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 "se7098" wrote in message ... 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/comm...et.f unctions |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup wordstring across mulitple worksheets & return sums
Summarize the sheets in various ways
without using complicated formulas by using Excel 2007 Pivot Tables. http://www.savefile.com/files/1851070 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup wordstring across mulitple worksheets & return sums
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. "Teethless mama" wrote: =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)) "se7098" wrote: 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/comm...et.f unctions |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup wordstring across mulitple worksheets & return sums
thanks for your help...however, i keep receiving a cannot consolidate error
when i attempt this. "se7098" wrote: 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/comm...et.f unctions |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup wordstring across mulitple worksheets & return sums
thank you for your help, however, i believe i have too many different
variable for pivot tables to be effect. "se7098" wrote: 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/comm...et.f unctions |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to return mulitple corresponding values | Excel Worksheet Functions | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
How to return mulitple values based on the contents of another cel | Excel Discussion (Misc queries) | |||
Lookup mulitple values and return one | Excel Worksheet Functions | |||
I need to return mulitple results in several columns please help | Excel Discussion (Misc queries) |