Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Lists/Summary Page
,Hello,
I have a workbook with multiple sheets. Each sheet has 4 columns and depending on the amount of data, multiple rows (usually between 1 and 20). I would like to create a summary page that would search all of the worksheets for data entered and combine all of these rows of data (no need to search for duplicates - there won't be any) on one main sheet. Is this possible? It seems like an array formula may do the trick but I'm not too deft at writing my own arrays. Thank you in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Lists/Summary Page
Please give more detail as to how the data is set out in the sheets
best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "SLW612" wrote in message ... ,Hello, I have a workbook with multiple sheets. Each sheet has 4 columns and depending on the amount of data, multiple rows (usually between 1 and 20). I would like to create a summary page that would search all of the worksheets for data entered and combine all of these rows of data (no need to search for duplicates - there won't be any) on one main sheet. Is this possible? It seems like an array formula may do the trick but I'm not too deft at writing my own arrays. Thank you in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Lists/Summary Page
Example (I hope this comes out right):
Sheet 1 ("AG") Col. A is vendor name, B is vendor ID (always 5 digits), C is date, and D is notes. A B C D 1 ABC Co. 01234 8/17/07 Ships directly from warehouse 2 DEF Co. 12345 9/1/07 Does not need a PO 3 GHI Co. 23456 8/13/07 Always double quantities 4 JKL Co. 34567 8/22/07 Verify payment first Sheet 2 ("CH") A B C D 1 Hedy Co. 45678 7/16/07 Qty 10 each 2 Sign Co. 56789 9/12/07 Speak with Ted 3 Bunt Co. 67890 8/02/07 Add extra for shipping 4 LFT Co. 78901 9/02/07 Last resort vendor Each sheet is for a different product, so none of the data will be the same as on another sheet (e.g. ABC co. only does product "AG" etc). I have 10 sheets - names are AG, CH, GE, GI, RM, SC, WC, CC, OG, WN. They are all set up the same but obviously have different data. I was hoping that in addition to keeping the entries on every sheet as entered, when an entry was typed onto each page I want it to also populate on a Summary page, and look something like this: A B C D 1 ABC Co. 01234 8/17/07 Ships directly from warehouse 2 DEF Co. 12345 9/1/07 Does not need a PO 3 GHI Co. 23456 8/13/07 Always double quantities 4 JKL Co. 34567 8/22/07 Verify payment first 5 Hedy Co. 45678 7/16/07 Qty 10 each 6 Sign Co. 56789 9/12/07 Speak with Ted 7 Bunt Co. 67890 8/02/07 Add extra for shipping 8 LFT Co. 78901 9/02/07 Last resort vendor I'll settle for them all being on one page, but if they could be in alphabetical order by Vendor that would be great too. The trick I don't know how to do is to get the Summary page to recognize that an entry has been typed on one of the other sheets, and copy that information to the next available row on Summary page. I hope this makes sense! Thanks "Bernard Liengme" wrote: Please give more detail as to how the data is set out in the sheets best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "SLW612" wrote in message ... ,Hello, I have a workbook with multiple sheets. Each sheet has 4 columns and depending on the amount of data, multiple rows (usually between 1 and 20). I would like to create a summary page that would search all of the worksheets for data entered and combine all of these rows of data (no need to search for duplicates - there won't be any) on one main sheet. Is this possible? It seems like an array formula may do the trick but I'm not too deft at writing my own arrays. Thank you in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining Lists/Summary Page
That was an excellent explanation!
However, to do what you want would take some fancy programming. I am hoping someone with more VBA skill will answer you. If not I suggest you post this message on the news:microsoft.public.excel.programming newsgroup. Database managers are always wary of having the same data stored twice. Perhaps you do not need the summary sheet. It could be that there is another way to generate the data (from the other sheets) at the time when you want a specific report. You task is almost looking like an Access project. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "SLW612" wrote in message ... Example (I hope this comes out right): Sheet 1 ("AG") Col. A is vendor name, B is vendor ID (always 5 digits), C is date, and D is notes. A B C D 1 ABC Co. 01234 8/17/07 Ships directly from warehouse 2 DEF Co. 12345 9/1/07 Does not need a PO 3 GHI Co. 23456 8/13/07 Always double quantities 4 JKL Co. 34567 8/22/07 Verify payment first Sheet 2 ("CH") A B C D 1 Hedy Co. 45678 7/16/07 Qty 10 each 2 Sign Co. 56789 9/12/07 Speak with Ted 3 Bunt Co. 67890 8/02/07 Add extra for shipping 4 LFT Co. 78901 9/02/07 Last resort vendor Each sheet is for a different product, so none of the data will be the same as on another sheet (e.g. ABC co. only does product "AG" etc). I have 10 sheets - names are AG, CH, GE, GI, RM, SC, WC, CC, OG, WN. They are all set up the same but obviously have different data. I was hoping that in addition to keeping the entries on every sheet as entered, when an entry was typed onto each page I want it to also populate on a Summary page, and look something like this: A B C D 1 ABC Co. 01234 8/17/07 Ships directly from warehouse 2 DEF Co. 12345 9/1/07 Does not need a PO 3 GHI Co. 23456 8/13/07 Always double quantities 4 JKL Co. 34567 8/22/07 Verify payment first 5 Hedy Co. 45678 7/16/07 Qty 10 each 6 Sign Co. 56789 9/12/07 Speak with Ted 7 Bunt Co. 67890 8/02/07 Add extra for shipping 8 LFT Co. 78901 9/02/07 Last resort vendor I'll settle for them all being on one page, but if they could be in alphabetical order by Vendor that would be great too. The trick I don't know how to do is to get the Summary page to recognize that an entry has been typed on one of the other sheets, and copy that information to the next available row on Summary page. I hope this makes sense! Thanks "Bernard Liengme" wrote: Please give more detail as to how the data is set out in the sheets best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "SLW612" wrote in message ... ,Hello, I have a workbook with multiple sheets. Each sheet has 4 columns and depending on the amount of data, multiple rows (usually between 1 and 20). I would like to create a summary page that would search all of the worksheets for data entered and combine all of these rows of data (no need to search for duplicates - there won't be any) on one main sheet. Is this possible? It seems like an array formula may do the trick but I'm not too deft at writing my own arrays. Thank you in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining multiple lists into one ! | Excel Worksheet Functions | |||
combining two lists | Excel Worksheet Functions | |||
Combining 2 lists | New Users to Excel | |||
Q: how to create summary daily lists from another, and reverse | Excel Worksheet Functions | |||
combining excel lists | Excel Discussion (Misc queries) |