How to work with Many Worksheets

  • Thread starter Thread starter chrisk
  • Start date Start date
C

chrisk

I have 31 worksheets in a workbook.
Each worksheet has col A the site and col B the person who went.
On worksheet 32 I would like to
count how many times person A has been to site A
count how many times person A has been to site B
count how many times person A has been to site C
count how many times person B has been to site A
count how many times person B has been to site B
count how many times person B has been to site C
and so on.
Wanted to use an index:match type thing but don’t know how to refer it to
all 31 sheets.
Thanks
 
Hi,

You can typically use pivot tables to solve such problems. If it is OK with
you, could you share your workbook with me at (e-mail address removed).

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
One play which compiles/stacks it quickly from all 31 data sheets into a
summary sheet, then a fast cleanup using autofilter, with a couple of pivot
table drags n drops for the required results ..

Assuming your 31 sheets are named: Sheet1, Sheet2, ... Sheet31,
and that data starts in row2 down in cols A and B (in A2:B2 down)
Assume max extent of data in any sheet is 100 rows

In your summary sheet (your Sheet32 ?),
Put in A1:
=OFFSET(INDIRECT("'Sheet"&INT((ROWS($1:1)-1)/100)+1&"'!A2"),MOD(ROWS($1:1)-1,100),)

Put in B1:
=OFFSET(INDIRECT("'Sheet"&INT((ROWS($1:1)-1)/100)+1&"'!B2"),MOD(ROWS($1:1)-1,100),)

Select A1:B1, copy down by 3100 rows (ie 100 rows x 31 sheets). This will
extract and stack data from all 31 sheets in one swoop into cols A and B
(Empty data lines will be returned as zeros).

Freeze cols A and B with an "in-place" copy n paste special as values,
insert a new top row for the labels: Site, Name. Autofilter on col A for
zeros, delete all these rows, remove autofilter.

Then create a pivot on cols A and B. In Layout: place Site in COLUMN area,
Name in ROW area, Name in DATA area (it'll appear as Count of Name), and
you'd have the required results that you're after.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik
 
Hi Chrisk,

Not knowing how many Site/Person combinations there are, the following
assumes that there are less than 100 for all worksheets;

In a new worksheet,

Column A - Site
Column B - Person
Range C2:AE2 - 1,2,3,...,31

copy and paste the following formula into C3, then copy into C3:AE100 -

=SUM(IF((INDIRECT("Sheet"&C$2&"!$B$2:$B$1000")=$B3)*(INDIRECT("Sheet"&C$2&"!$A$2:$A$1000")=$A3),1))

Note: enter this array formula with ctrl+shift+enter

Hope this helps
 
Back
Top