Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Summary of data from 20 sheets
I have a workbook with 20 sheets (countries) all formatted the same,
contained inj the sheets in cells A74;A90 are names of suppliers and the same supplier name could appear in any of those cells on any of the sheets. I'm trying to build a summary sheet so I can select a suppliers name in one cell and then in various cells below the names of the countries (sheets) appears. |
#2
|
|||
|
|||
Have you tried the SUMIF function?
Reserve say cell A1 on the summary sheet for the supplier name on the summary sheet, put in a sumif for each country using: =SUMIF('sheetname'!A74:A90,A1,'sheetname'!B74:B90) assuming the data to be summarized is in the B column... "Allan Skyner" wrote: I have a workbook with 20 sheets (countries) all formatted the same, contained inj the sheets in cells A74;A90 are names of suppliers and the same supplier name could appear in any of those cells on any of the sheets. I'm trying to build a summary sheet so I can select a suppliers name in one cell and then in various cells below the names of the countries (sheets) appears. |
#4
|
|||
|
|||
With the supplier name in A1 and a list of all sheet
names (countries) in E1:E20, insert this into A2, press ctrl/shift/enter, and copy down to A21: =INDEX($E$1:$E$20,SMALL(IF(COUNTIF(INDIRECT ("'"&$E$1:$E$20&"'!A74:A90"),$A$1),ROW(INDIRECT ("1:20"))),ROW()-1)) HTH Jason Atlanta, GA -----Original Message----- I have a workbook with 20 sheets (countries) all formatted the same, contained inj the sheets in cells A74;A90 are names of suppliers and the same supplier name could appear in any of those cells on any of the sheets. I'm trying to build a summary sheet so I can select a suppliers name in one cell and then in various cells below the names of the countries (sheets) appears. . |
#5
|
|||
|
|||
Hi
apart from the fact that doing this for 20 sheets is quite labourious - i think Data / Consolidation (with all three boxes ticked) will give you what you want (try it on three sheets first to see). Cheers JulieD "Allan Skyner" wrote in message ... I have a workbook with 20 sheets (countries) all formatted the same, contained inj the sheets in cells A74;A90 are names of suppliers and the same supplier name could appear in any of those cells on any of the sheets. I'm trying to build a summary sheet so I can select a suppliers name in one cell and then in various cells below the names of the countries (sheets) appears. |
#6
|
|||
|
|||
Hi Jason
Thanks for the speedy reply but I get an error message that there is a problem with the formula. "Jason Morin" wrote: With the supplier name in A1 and a list of all sheet names (countries) in E1:E20, insert this into A2, press ctrl/shift/enter, and copy down to A21: =INDEX($E$1:$E$20,SMALL(IF(COUNTIF(INDIRECT ("'"&$E$1:$E$20&"'!A74:A90"),$A$1),ROW(INDIRECT ("1:20"))),ROW()-1)) HTH Jason Atlanta, GA -----Original Message----- I have a workbook with 20 sheets (countries) all formatted the same, contained inj the sheets in cells A74;A90 are names of suppliers and the same supplier name could appear in any of those cells on any of the sheets. I'm trying to build a summary sheet so I can select a suppliers name in one cell and then in various cells below the names of the countries (sheets) appears. . |
#7
|
|||
|
|||
Make sure to press ctrl/shift/enter after inserting the
first formula. Also, some cells with error out with #NUM! which is OK. You can hide those if you wish. Make sure that the list of worksheet names in E1:E20 typed in correctly. I can send you an example workbook if you wish. Jason -----Original Message----- Hi Jason Thanks for the speedy reply but I get an error message that there is a problem with the formula. "Jason Morin" wrote: With the supplier name in A1 and a list of all sheet names (countries) in E1:E20, insert this into A2, press ctrl/shift/enter, and copy down to A21: =INDEX($E$1:$E$20,SMALL(IF(COUNTIF(INDIRECT ("'"&$E$1:$E$20&"'!A74:A90"),$A$1),ROW(INDIRECT ("1:20"))),ROW()-1)) HTH Jason Atlanta, GA -----Original Message----- I have a workbook with 20 sheets (countries) all formatted the same, contained inj the sheets in cells A74;A90 are names of suppliers and the same supplier name could appear in any of those cells on any of the sheets. I'm trying to build a summary sheet so I can select a suppliers name in one cell and then in various cells below the names of the countries (sheets) appears. . . |
#8
|
|||
|
|||
Hi Jason
I've tried again and ensured that all details are correct but still return an error message with the formula. It would be most helpful if you could send me a sample workbook, my email address is Thanks so much for taking time to help. "Jason Morin" wrote: Make sure to press ctrl/shift/enter after inserting the first formula. Also, some cells with error out with #NUM! which is OK. You can hide those if you wish. Make sure that the list of worksheet names in E1:E20 typed in correctly. I can send you an example workbook if you wish. Jason -----Original Message----- Hi Jason Thanks for the speedy reply but I get an error message that there is a problem with the formula. "Jason Morin" wrote: With the supplier name in A1 and a list of all sheet names (countries) in E1:E20, insert this into A2, press ctrl/shift/enter, and copy down to A21: =INDEX($E$1:$E$20,SMALL(IF(COUNTIF(INDIRECT ("'"&$E$1:$E$20&"'!A74:A90"),$A$1),ROW(INDIRECT ("1:20"))),ROW()-1)) HTH Jason Atlanta, GA -----Original Message----- I have a workbook with 20 sheets (countries) all formatted the same, contained inj the sheets in cells A74;A90 are names of suppliers and the same supplier name could appear in any of those cells on any of the sheets. I'm trying to build a summary sheet so I can select a suppliers name in one cell and then in various cells below the names of the countries (sheets) appears. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
populating sheets based on data from parent sheets | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Linking sheets to a summary sheet in workbook | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
Workbook Summary Data from Sheets | Excel Worksheet Functions |