merge/extract data from identical worksheets in a workbook

M

Michelle K

Hi,

I have a workbook that contains 100 identical worksheets. I need to count
how many 1's are in say H1, how many 2's, etc.

Can anyone please help me with this? I read somewhere in the discussions
about having "dummy" sheets named Start and End and then just do a range.
Can someone remind me of that formula?

Thanks so much,
Michelle
 
B

Bernie Deitrick

Michelle,

The formula that you are thinking of is

=SUM(Start:End!H1)

but you can't modify it to count the way that you want.

What is your worksheet naming convention?

HTH,
Bernie
MS Excel MVP
 
M

Michelle K

they all have the same name: survey , survey (2), survey (3)...

should i rename them?

after that what's next?
 
B

Bernie Deitrick

Michelle,

The reason I asked is that you can often build an INDIRECT formula to pull
your data together into a table for further processing.

The only sheet you should nedd to rename would be "survey" Rename it to
"survey (1)" to keep your naming style consistent.

Then insert a new sheet, and in cell A1 of that sheet, enter the formula

=INDIRECT("'survey (" & ROW() & ")'!H1")

and copy down for 100 rows (or the same number of rows as your highest
number survey (xxx) sheet)

Then you can use formulas on that list, like

=COUNTIF(A1:A100,1)

to count the 1's etc.

HTH,
Bernie
MS Excel MVP
 
M

Michelle K

Perfect!

Thanks Bernie!

Michelle

Bernie Deitrick said:
Michelle,

The reason I asked is that you can often build an INDIRECT formula to pull
your data together into a table for further processing.

The only sheet you should nedd to rename would be "survey" Rename it to
"survey (1)" to keep your naming style consistent.

Then insert a new sheet, and in cell A1 of that sheet, enter the formula

=INDIRECT("'survey (" & ROW() & ")'!H1")

and copy down for 100 rows (or the same number of rows as your highest
number survey (xxx) sheet)

Then you can use formulas on that list, like

=COUNTIF(A1:A100,1)

to count the 1's etc.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Top