How to work with Many Worksheets

[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

 

[Ashish Mathur]

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

 

[Max]

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

 

[Peggy]

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