Summarising information from different sheets in a summary sheet

[Naida T]

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.

 

[JLatham]

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.

 

[Max]

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