Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to summarize 3-4 worksheets worth of data into a summary scree
Hi all
As aprt of doing a mail merge I need data from 3 worksheets to be summarised in a 4th worksheet for mail merge reasons. So what i have currently is a bunch of colums and rows whcih will continously added to. Currently have around 100 candicates and on each person there are several further colums for their data. Each person has a similar setup in 2 other worksheets for different sets of data. What i want is basically the 4th worksheet to show all of these and also whenever more data is added to either sheet, this is added to the 4th worksheet.... Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to summarize 3-4 worksheets worth of data into a summary scree
Gareth,
I recommend starting he http://www.rondebruin.nl/tips.htm -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Gareth" wrote: Hi all As aprt of doing a mail merge I need data from 3 worksheets to be summarised in a 4th worksheet for mail merge reasons. So what i have currently is a bunch of colums and rows whcih will continously added to. Currently have around 100 candicates and on each person there are several further colums for their data. Each person has a similar setup in 2 other worksheets for different sets of data. What i want is basically the 4th worksheet to show all of these and also whenever more data is added to either sheet, this is added to the 4th worksheet.... Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to summarize 3-4 worksheets worth of data into a summary s
Thanks thomas
i have had a look at this and seems somewhart complicated for what i am asking. "Thomas [PBD]" wrote: Gareth, I recommend starting he http://www.rondebruin.nl/tips.htm -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Gareth" wrote: Hi all As aprt of doing a mail merge I need data from 3 worksheets to be summarised in a 4th worksheet for mail merge reasons. So what i have currently is a bunch of colums and rows whcih will continously added to. Currently have around 100 candicates and on each person there are several further colums for their data. Each person has a similar setup in 2 other worksheets for different sets of data. What i want is basically the 4th worksheet to show all of these and also whenever more data is added to either sheet, this is added to the 4th worksheet.... Thanks in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to summarize 3-4 worksheets worth of data into a summary s
Gareth,
Are all of these Worksheets in the same Workbook? It can be easier to write a macro to combine the information into the 4th sheet if so. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Gareth" wrote: Thanks thomas i have had a look at this and seems somewhart complicated for what i am asking. "Thomas [PBD]" wrote: Gareth, I recommend starting he http://www.rondebruin.nl/tips.htm -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Gareth" wrote: Hi all As aprt of doing a mail merge I need data from 3 worksheets to be summarised in a 4th worksheet for mail merge reasons. So what i have currently is a bunch of colums and rows whcih will continously added to. Currently have around 100 candicates and on each person there are several further colums for their data. Each person has a similar setup in 2 other worksheets for different sets of data. What i want is basically the 4th worksheet to show all of these and also whenever more data is added to either sheet, this is added to the 4th worksheet.... Thanks in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to summarize 3-4 worksheets worth of data into a summary s
Hi Thomas
yes all 3 worksheets are in same workbook / file. ta "Thomas [PBD]" wrote: Gareth, Are all of these Worksheets in the same Workbook? It can be easier to write a macro to combine the information into the 4th sheet if so. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Gareth" wrote: Thanks thomas i have had a look at this and seems somewhart complicated for what i am asking. "Thomas [PBD]" wrote: Gareth, I recommend starting he http://www.rondebruin.nl/tips.htm -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Gareth" wrote: Hi all As aprt of doing a mail merge I need data from 3 worksheets to be summarised in a 4th worksheet for mail merge reasons. So what i have currently is a bunch of colums and rows whcih will continously added to. Currently have around 100 candicates and on each person there are several further colums for their data. Each person has a similar setup in 2 other worksheets for different sets of data. What i want is basically the 4th worksheet to show all of these and also whenever more data is added to either sheet, this is added to the 4th worksheet.... Thanks in advance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to summarize 3-4 worksheets worth of data into a summary s
Gareth,
Here is a quick macro that seems to work, as I dont know your criteria or columns etc... its a rough idea of what could be done. Note: please create a tab named "Summary" Public Sub Comb_Summary() For Each sh In Sheets sh.Select If ActiveSheet.Name < "Summary" Then sh.UsedRange.Copy Sheets("Summary").Select x = WorksheetFunction.CountA(Columns("A:A")) Cells(x + 1, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False If x < 0 Then Rows(x + 1).Delete End If End If Next End Sub -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Gareth" wrote: Hi Thomas yes all 3 worksheets are in same workbook / file. ta "Thomas [PBD]" wrote: Gareth, Are all of these Worksheets in the same Workbook? It can be easier to write a macro to combine the information into the 4th sheet if so. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Gareth" wrote: Thanks thomas i have had a look at this and seems somewhart complicated for what i am asking. "Thomas [PBD]" wrote: Gareth, I recommend starting he http://www.rondebruin.nl/tips.htm -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Gareth" wrote: Hi all As aprt of doing a mail merge I need data from 3 worksheets to be summarised in a 4th worksheet for mail merge reasons. So what i have currently is a bunch of colums and rows whcih will continously added to. Currently have around 100 candicates and on each person there are several further colums for their data. Each person has a similar setup in 2 other worksheets for different sets of data. What i want is basically the 4th worksheet to show all of these and also whenever more data is added to either sheet, this is added to the 4th worksheet.... Thanks in advance. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to summarize 3-4 worksheets worth of data into a summary s
Hi thomas
thank you for the help so far. When i create a macros and enter this in. it does actually seem to work, however it copies the data twice. The other issue I will have soon is that I need to data to be show horizontally; its hard to describe what i am after without sending you an example. can i do this? Further issues I think could arise in that the Master file with all currnt data on it will need to be updated daily and edited. So therefore this new summary file will also need to be updated without having a user to do it all. is this possible? I do feel that what i am asking is getting beyond excels capabilities is this right? For example Worksheet 1 may contain Name DOB Job Addres mr X 19/09/08 cleaner 137 marther rd Mrs C 01/01/01 teacher 5 nowhere Mr D 20/09/08 admin 65 somewhere Mr A 16/08/08 driver 12 park work sheet 2 may contain further info such as Name Qualifications telephone no status Mr x................................................. ............................................... Mrs c................................... Mr D...................... Mr A.............. Finally to summarise all of this, i would like a summary sheet to show name DOB job address qualifications tele no status So all data is present horizontallty and can be updated automatically in the correct fields. I do hope this makes sense? Thanks once again. Gareth |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to summarize 3-4 worksheets worth of data into a summary s
Hi Gareth
You could do this with formulae. Copy Sheet1 (or whichever sheet contains most of your data) to a new sheet - Summary On Summary, insert 2 new rows at the top. In the first blank column after your data, enter the Sheet name from which you want to copy the data e.g Sheet2, into the first row of that column. In the second row of this column, enter the name of heading you want to pick up. In the third row, enter this formula =IF(COUNTIF(INDIRECT(B$1&"!A:A"),$A3), VLOOKUP($A3,INDIRECT(B$1&"!A:Z"), MATCH(B$2,INDIRECT(B$1&"!A1:Z1"),0),0),"") and copy down as far as required. Repeat the procedure for as many extra columns of information you want to pick up. -- Regards Roger Govier "Gareth" wrote in message ... Hi thomas thank you for the help so far. When i create a macros and enter this in. it does actually seem to work, however it copies the data twice. The other issue I will have soon is that I need to data to be show horizontally; its hard to describe what i am after without sending you an example. can i do this? Further issues I think could arise in that the Master file with all currnt data on it will need to be updated daily and edited. So therefore this new summary file will also need to be updated without having a user to do it all. is this possible? I do feel that what i am asking is getting beyond excels capabilities is this right? For example Worksheet 1 may contain Name DOB Job Addres mr X 19/09/08 cleaner 137 marther rd Mrs C 01/01/01 teacher 5 nowhere Mr D 20/09/08 admin 65 somewhere Mr A 16/08/08 driver 12 park work sheet 2 may contain further info such as Name Qualifications telephone no status Mr x................................................. ............................................... Mrs c................................... Mr D...................... Mr A.............. Finally to summarise all of this, i would like a summary sheet to show name DOB job address qualifications tele no status So all data is present horizontallty and can be updated automatically in the correct fields. I do hope this makes sense? Thanks once again. Gareth |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to summarize 3-4 worksheets worth of data into a summary s
Hi Roger
Sorry dont really understand what your asking me to do here. If i have 2 worksheets, Work 1 and Work 2 (from now on lets call work 2 Summary) You have said to enter the sheet name in the first blank colum after all the data I have copied into the summary sheet/ So would this sheet name be Work 1 or Summary? Once done, do I enter this after all the data ? if so then the colum you suggest I put above into would be BU1. if so BU1 would say Work 1 BU2 would be ID (the heading I want) 3rd row would (=IF(COUNTIF(INDIRECT(B$1&"!A:A"),$A3), VLOOKUP($A3,INDIRECT(B$1&"!A:Z"), MATCH(B$2,INDIRECT(B$1&"!A1:Z1"),0),0),"") This is fine so far but it doesnt seem to do anything just produces a lot of the above caluatlation with no effect? Stuck! "Roger Govier" wrote: Hi Gareth You could do this with formulae. Copy Sheet1 (or whichever sheet contains most of your data) to a new sheet - Summary On Summary, insert 2 new rows at the top. In the first blank column after your data, enter the Sheet name from which you want to copy the data e.g Sheet2, into the first row of that column. In the second row of this column, enter the name of heading you want to pick up. In the third row, enter this formula =IF(COUNTIF(INDIRECT(B$1&"!A:A"),$A3), VLOOKUP($A3,INDIRECT(B$1&"!A:Z"), MATCH(B$2,INDIRECT(B$1&"!A1:Z1"),0),0),"") and copy down as far as required. Repeat the procedure for as many extra columns of information you want to pick up. -- Regards Roger Govier "Gareth" wrote in message ... Hi thomas thank you for the help so far. When i create a macros and enter this in. it does actually seem to work, however it copies the data twice. The other issue I will have soon is that I need to data to be show horizontally; its hard to describe what i am after without sending you an example. can i do this? Further issues I think could arise in that the Master file with all currnt data on it will need to be updated daily and edited. So therefore this new summary file will also need to be updated without having a user to do it all. is this possible? I do feel that what i am asking is getting beyond excels capabilities is this right? For example Worksheet 1 may contain Name DOB Job Addres mr X 19/09/08 cleaner 137 marther rd Mrs C 01/01/01 teacher 5 nowhere Mr D 20/09/08 admin 65 somewhere Mr A 16/08/08 driver 12 park work sheet 2 may contain further info such as Name Qualifications telephone no status Mr x................................................. ............................................... Mrs c................................... Mr D...................... Mr A.............. Finally to summarise all of this, i would like a summary sheet to show name DOB job address qualifications tele no status So all data is present horizontallty and can be updated automatically in the correct fields. I do hope this makes sense? Thanks once again. Gareth |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to summarize 3-4 worksheets worth of data into a summary s
Hi Gareth
As you have spaces in your worksheet names, then the values in cell BU1 etc need to be enclosed in single quotes 'WORK 1' The following amended formula achieves this =IF(COUNTIF(INDIRECT("'"&B$1&"'!A:A"),$A3), VLOOKUP($A3,INDIRECT("'"&B$1&"'!A:Z"), MATCH(B$2,INDIRECT("'"&B$1&"'!A1:Z1"),0),0),"") In your original posting, you talked about 3-4 sheets being Summarised. That being so, I was suggesting that we still had 4 sheets of raw Data. Sheet Summary, would be a copy of WORK 1 (assuming that held all of the data you wanted). In cell BU1 you would enter WORK 2, in cell BU2 you would enter ID, assuming there was a value in column ID of Sheet WORK 2 that was associated with the name in column A of Summary Then in BU3 use the new formula as above. If you are wanting all columns from All sheets, then don't copy WORK 1 to Summary, just copy the Names from Column A of that sheet to cell A3 of Summary. Then selectively Enter the Sheet and Column details from each of the 4 sheets for data that you wish to extract. If you can't get it to work, mail me a copy of your workbook and I will set it up for you. roger at technology4u dot co dot uk Change the at and dots to make a valid email address. -- Regards Roger Govier "Gareth" wrote in message ... Hi Roger Sorry dont really understand what your asking me to do here. If i have 2 worksheets, Work 1 and Work 2 (from now on lets call work 2 Summary) You have said to enter the sheet name in the first blank colum after all the data I have copied into the summary sheet/ So would this sheet name be Work 1 or Summary? Once done, do I enter this after all the data ? if so then the colum you suggest I put above into would be BU1. if so BU1 would say Work 1 BU2 would be ID (the heading I want) 3rd row would (=IF(COUNTIF(INDIRECT(B$1&"!A:A"),$A3), VLOOKUP($A3,INDIRECT(B$1&"!A:Z"), MATCH(B$2,INDIRECT(B$1&"!A1:Z1"),0),0),"") This is fine so far but it doesnt seem to do anything just produces a lot of the above caluatlation with no effect? Stuck! "Roger Govier" wrote: Hi Gareth You could do this with formulae. Copy Sheet1 (or whichever sheet contains most of your data) to a new sheet - Summary On Summary, insert 2 new rows at the top. In the first blank column after your data, enter the Sheet name from which you want to copy the data e.g Sheet2, into the first row of that column. In the second row of this column, enter the name of heading you want to pick up. In the third row, enter this formula =IF(COUNTIF(INDIRECT(B$1&"!A:A"),$A3), VLOOKUP($A3,INDIRECT(B$1&"!A:Z"), MATCH(B$2,INDIRECT(B$1&"!A1:Z1"),0),0),"") and copy down as far as required. Repeat the procedure for as many extra columns of information you want to pick up. -- Regards Roger Govier "Gareth" wrote in message ... Hi thomas thank you for the help so far. When i create a macros and enter this in. it does actually seem to work, however it copies the data twice. The other issue I will have soon is that I need to data to be show horizontally; its hard to describe what i am after without sending you an example. can i do this? Further issues I think could arise in that the Master file with all currnt data on it will need to be updated daily and edited. So therefore this new summary file will also need to be updated without having a user to do it all. is this possible? I do feel that what i am asking is getting beyond excels capabilities is this right? For example Worksheet 1 may contain Name DOB Job Addres mr X 19/09/08 cleaner 137 marther rd Mrs C 01/01/01 teacher 5 nowhere Mr D 20/09/08 admin 65 somewhere Mr A 16/08/08 driver 12 park work sheet 2 may contain further info such as Name Qualifications telephone no status Mr x................................................. ............................................... Mrs c................................... Mr D...................... Mr A.............. Finally to summarise all of this, i would like a summary sheet to show name DOB job address qualifications tele no status So all data is present horizontallty and can be updated automatically in the correct fields. I do hope this makes sense? Thanks once again. Gareth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help using data from multiple worksheets to create a Summary | Excel Discussion (Misc queries) | |||
summarize data from multiple worksheets | Excel Discussion (Misc queries) | |||
help need to total a months worth of worksheets into one report | New Users to Excel | |||
Creating a summary sheet from data across multiple worksheets | Excel Discussion (Misc queries) | |||
Summarize multiple worksheet detail on summary sheet | Excel Discussion (Misc queries) |