I have a workbook with a large number of worksheets; on each worksheet is an
array of data. I'm creating a summary worksheet to sum all of the other
worksheets. The problem is that all of the worksheets aren't aligned....the
data to be added is all in column "E" but on its on a different line on every
sheet.

Each of the data lines does have a common identifier so that I can use the
V-lookup formula to find the data to be added, but with so many sheets, it
would be a very, very long formula.

Question, if I make all of the data arrays the same size, can I use the
Vlookup formula across multiple worksheets....if "yes", how?

If not Vlookup, is there another means to do it?

Thanks

If you realign your data on each sheet, will the values have the same cell
address on each sheet?

You could create a formula similar to this if all the data was in say D50:

=Sum (Sheet1:Sheet30!D50)

Post back if your question is more complicated.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"jkiser" wrote in message
...
I have a workbook with a large number of worksheets; on each worksheet is
an
array of data. I'm creating a summary worksheet to sum all of the other
worksheets. The problem is that all of the worksheets aren't
aligned....the
data to be added is all in column "E" but on its on a different line on
every
sheet.

Each of the data lines does have a common identifier so that I can use the
V-lookup formula to find the data to be added, but with so many sheets, it
would be a very, very long formula.

Question, if I make all of the data arrays the same size, can I use the
Vlookup formula across multiple worksheets....if "yes", how?

If not Vlookup, is there another means to do it?

Thanks

Yes....I'm trying to avoid having to align the data because there are so many
sheets and so much data; I'd be adding a bunch of blank lines. I'm hoping
there's a way to do it with a formula.

"RagDyer" wrote:

If you realign your data on each sheet, will the values have the same cell
address on each sheet?

You could create a formula similar to this if all the data was in say D50:

=Sum (Sheet1:Sheet30!D50)

Post back if your question is more complicated.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"jkiser" wrote in message
...
I have a workbook with a large number of worksheets; on each worksheet is
an
array of data. I'm creating a summary worksheet to sum all of the other
worksheets. The problem is that all of the worksheets aren't
aligned....the
data to be added is all in column "E" but on its on a different line on
every
sheet.

Each of the data lines does have a common identifier so that I can use the
V-lookup formula to find the data to be added, but with so many sheets, it
would be a very, very long formula.

Question, if I make all of the data arrays the same size, can I use the
Vlookup formula across multiple worksheets....if "yes", how?

If not Vlookup, is there another means to do it?

Thanks

If your data is setup in such a way that a single SUMIF() function would
work if you inserted it into every sheet, you could then place that Sumif in
a location where each sheet would have their totals in exactly the same
cell.

Don't forget, you can group your sheets and insert that formula in every
sheet with a *single* entry.

If this doesn't sound plausible, post back with your data configuration, and
specific examples of what you're looking to accomplish.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"jkiser" wrote in message
...
Yes....I'm trying to avoid having to align the data because there are so
many
sheets and so much data; I'd be adding a bunch of blank lines. I'm hoping
there's a way to do it with a formula.

"RagDyer" wrote:

If you realign your data on each sheet, will the values have the same
cell
address on each sheet?

You could create a formula similar to this if all the data was in say
D50:

=Sum (Sheet1:Sheet30!D50)

Post back if your question is more complicated.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"jkiser" wrote in message
...
I have a workbook with a large number of worksheets; on each worksheet
is
an
array of data. I'm creating a summary worksheet to sum all of the
other
worksheets. The problem is that all of the worksheets aren't
aligned....the
data to be added is all in column "E" but on its on a different line
on
every
sheet.

Each of the data lines does have a common identifier so that I can use
the
V-lookup formula to find the data to be added, but with so many
sheets, it
would be a very, very long formula.

Question, if I make all of the data arrays the same size, can I use
the
Vlookup formula across multiple worksheets....if "yes", how?

If not Vlookup, is there another means to do it?

Thanks

Hi,

You may refer to Question 9 on the following link -
http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"jkiser" wrote in message
...
I have a workbook with a large number of worksheets; on each worksheet is
an
array of data. I'm creating a summary worksheet to sum all of the other
worksheets. The problem is that all of the worksheets aren't
aligned....the
data to be added is all in column "E" but on its on a different line on
every
sheet.

Each of the data lines does have a common identifier so that I can use the
V-lookup formula to find the data to be added, but with so many sheets, it
would be a very, very long formula.

Question, if I make all of the data arrays the same size, can I use the
Vlookup formula across multiple worksheets....if "yes", how?

If not Vlookup, is there another means to do it?

Thanks

I'm going to have to study this one for a while to see how it works....looks
like it has promise.

"jkiser" wrote:

I have a workbook with a large number of worksheets; on each worksheet is an
array of data. I'm creating a summary worksheet to sum all of the other
worksheets. The problem is that all of the worksheets aren't aligned....the
data to be added is all in column "E" but on its on a different line on every
sheet.

Each of the data lines does have a common identifier so that I can use the
V-lookup formula to find the data to be added, but with so many sheets, it
would be a very, very long formula.

Question, if I make all of the data arrays the same size, can I use the
Vlookup formula across multiple worksheets....if "yes", how?

If not Vlookup, is there another means to do it?

Thanks