If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|