Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarising information from different sheets in a summary sheet
Dear Excel experts,
I am stuck with a problem here which I am trying to solve manually, but knowing Excel, there MUST be a more efficient (time-saving and less prone to mistakes) way to deal with this. The essence of the problem is as follows: - Let's say I have a workbook with 4 worksheets - Summary, CompanyX, CompanyY, CompanyZ (in reality, I need to update three workbooks with 100+ sheets quarterly and reflect them in the "Summary" sheet) - Eeach of the "Company" sheets follows the same format (with most important information - let's say (a) company name, (b) number of staff, (c) address and (d) sales - all placed in the same cell locations) - The summary sheet summarises (1) Companies against (2) No of staff / Address / Sales etc. For the time being, I am creating references for company names by pointing to each individual sheet and have to do the same for the second part but there is certainly a way to automatise referencing to different sheets?! I would expect that I'd have to do this manually for the first company but could then do something like ="CompanyA+1"!A2 (read: look up the same cell, A2, in one sheet after the "CompanyA) but this is not working. Any ideas what WOULD work? Thanks a million, N. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarising information from different sheets in a summary sheet
Since the only difference appears to be the name of the referenced
worksheet(s), you could look at selecting all the cells on a copied/other existing sheet and changing the name of the sheet used in the formulas using the Edit | Replace function with the "look in formulas" option selected. "Naida T" wrote: Dear Excel experts, I am stuck with a problem here which I am trying to solve manually, but knowing Excel, there MUST be a more efficient (time-saving and less prone to mistakes) way to deal with this. The essence of the problem is as follows: - Let's say I have a workbook with 4 worksheets - Summary, CompanyX, CompanyY, CompanyZ (in reality, I need to update three workbooks with 100+ sheets quarterly and reflect them in the "Summary" sheet) - Eeach of the "Company" sheets follows the same format (with most important information - let's say (a) company name, (b) number of staff, (c) address and (d) sales - all placed in the same cell locations) - The summary sheet summarises (1) Companies against (2) No of staff / Address / Sales etc. For the time being, I am creating references for company names by pointing to each individual sheet and have to do the same for the second part but there is certainly a way to automatise referencing to different sheets?! I would expect that I'd have to do this manually for the first company but could then do something like ="CompanyA+1"!A2 (read: look up the same cell, A2, in one sheet after the "CompanyA) but this is not working. Any ideas what WOULD work? Thanks a million, N. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarising information from different sheets in a summary sheet
In your "Summary",
In say, cols K across a. List the specific cell refs to be extracted from each sheet in L1 across eg: B2, B1, B6, H2, .. b. List the sheetnames* in K2 down (in any order) *If there's a whole LOT of sheetnames to list, you can run the sub (given below) to list it all in a new sheet (it'll be listed in A2 down), then just easily copy n paste over into K2 down. Note that sheetnames listed need to match exactly with what's on the tabs (except for case) With the above done, place in L2: =IF(COUNTA($K2,L$1)<2,"",INDIRECT("'"&$K2&"'!"&L$1 )) Copy L2 across / fill down as far as required. This will extract all specified data from each company's sheet in one easy swoop into your summary. *Sub to list sheetnames ' ---- begin --- Sub ListSheetNames() Dim wkSht As Worksheet Range("A2").Select For Each wkSht In Worksheets Selection = wkSht.Name ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Next wkSht End Sub ' --- end --- Success ? Celebrate it, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "Naida T" wrote: The essence of the problem is as follows: - Let's say I have a workbook with 4 worksheets - Summary, CompanyX, CompanyY, CompanyZ (in reality, I need to update three workbooks with 100+ sheets quarterly and reflect them in the "Summary" sheet) - Each of the "Company" sheets follows the same format (with most important information - let's say (a) company name, (b) number of staff, (c) address and (d) sales - all placed in the same cell locations) - The summary sheet summarises (1) Companies against (2) No of staff / Address / Sales etc. For the time being, I am creating references for company names by pointing to each individual sheet and have to do the same for the second part but there is certainly a way to automate referencing to different sheets?! I would expect that I'd have to do this manually for the first company but could then do something like ="CompanyA+1"!A2 (read: look up the same cell, A2, in one sheet after the "CompanyA) but this is not working. Any ideas what WOULD work? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to get sum from one 31 sheets to a summary sheet. | Excel Discussion (Misc queries) | |||
Summary Sheet help with multiple sheets | Excel Discussion (Misc queries) | |||
Displaying information (contained in defined names) on a summary sheet, in different row numbers? | Excel Discussion (Misc queries) | |||
Summarising Information from other files | Excel Discussion (Misc queries) | |||
Creating A Summary Sheet With Information From Several Worksheets | Excel Discussion (Misc queries) |