merge/extract data from identical worksheets in a workbook

[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

[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


"Michelle K" <[email protected]> wrote in message
news:[email protected]...
> 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

[Michelle K]

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

should i rename them?

after that what's next?

"Bernie Deitrick" wrote:

> 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
>
>
> "Michelle K" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[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


"Michelle K" <[email protected]> wrote in message
news:[email protected]...
> they all have the same name: survey , survey (2), survey (3)...
>
> should i rename them?
>
> after that what's next?
>
> "Bernie Deitrick" wrote:
>
> > 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
> >
> >
> > "Michelle K" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[Michelle K]

Perfect!

Thanks Bernie!

Michelle

"Bernie Deitrick" wrote:

> 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
>
>
> "Michelle K" <[email protected]> wrote in message
> news:[email protected]...
> > they all have the same name: survey , survey (2), survey (3)...
> >
> > should i rename them?
> >
> > after that what's next?
> >
> > "Bernie Deitrick" wrote:
> >
> > > 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
> > >
> > >
> > > "Michelle K" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

[Bernie Deitrick]

Michelle,

You're quite welcome.

Bernie
MS Excel MVP

> Perfect!
>
> Thanks Bernie!
>
> Michelle