Automatically enter cell address??

[ntsheehy]

I'm trying to make a formula which adds numbers from the same cel
address (i.e. column letter and row number) from various worksheets.
This same type of formula will be used over 200 times but each time th
cell address will change. My question is: is there some function whic
will automatically use the selected cells address so that I can creat
one formula and paste it into many different cells.

Otherwise I'm going to have to enter 200+ different cell addresses on
cell at a time!!!

Please help!!

feel free to email me at (e-mail address removed)

Thanks,
Nei

 

[ntsheehy]

here's an example formula to help understand my question:

=SUM('Sheet1'!E92)+('Sheet2'!E92)+('Sheet3'!E92)

but then I'll need to do the exact same thing for M92....is ther
anyway to do this without manually changing the formula and replacin
all of the E92 with M92???

 

[RagDyer]

First of all, you can shorten your existing formula slightly with this:

=SUM(Sheet1:Sheet3!E92)
As long as there isn't any other sheet tab *physically* in-between sheets 1
and 3.

You only mentioned your second set of cells to total, M92.
This is 8 colimns over from the original.
If all the rest follow the same pattern, you could try this formula, which
you can copy down to increment to the other columns.

=SUM(OFFSET(Sheet1!$E$92,,ROW(A1)*8),OFFSET(Sheet2!$E$92,,ROW(A1)*8),OFFSET(
Sheet3!$E$92,,ROW(A1)*8))

I can't seem to figure out how to shorten this.
Maybe someone else will have an idea.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


here's an example formula to help understand my question:

=SUM('Sheet1'!E92)+('Sheet2'!E92)+('Sheet3'!E92)

but then I'll need to do the exact same thing for M92....is there
anyway to do this without manually changing the formula and replacing
all of the E92 with M92????

 

[Myrna Larson]

You can write this as a 3D formula, which eliminates some typing:

=SUM(Sheet1:Sheet3!E92)

If you copy that identical formula into other cells (to speed that up, select
all of them, type the formula once, then press CTRL+ENTER, it will put the
formula in all selected cells. Then you can use Search and Replace on some of
the cells to replace !E with !M, etc.

But there may be a different formula that will work in all cells. It depends
on the locations of the cell containing the formula and the cell referred to
by the formula. Can you provide more information on that?

 

[ntsheehy]

all of my worksheets are identical basically....

the "final" worksheet needs to sum up the numbers from the othe
worksheets. Since the worksheets are identical, cell E92 in th
"final" worksheet refers to the sets of data from the other worksheet
that I would need to sum.

If this helps:
Joe Smith's data would be in E92 for every worksheet and in the fina
worksheet E92 would be the sum of all of Smith's data.

Someone please help or I'll be doing ALOT of monotonous work....:

 

[Bryan]

Having shortened the initial formula as suggested, a simple copy and past to
the other cells on the summary sheet will automatically insert and adjust
the formula to add up the relevant cells from the individual sheets.

Bryan

 

[RagDyer]

First of all, I see that you're posting the same question to other groups.
It's best to remain with the same thread, so that others who might have some
suggestions can see what did and/or did not satisfy you.

Now, that being said, to this question.
I don't believe that you understood my original post.
I suggested a formula that would work, *IF* the cells you wish to total have
some sort of repeating placemrnt (address) pattern.

The formula works by just being copied down the column, *IF* the totaled
cells repeat every 8 columns.
So, if that doesn't work for you, post back with *where* the cells are
located, that you wish to sum.
--


Regards,

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

all of my worksheets are identical basically....

the "final" worksheet needs to sum up the numbers from the other
worksheets. Since the worksheets are identical, cell E92 in the
"final" worksheet refers to the sets of data from the other worksheets
that I would need to sum.

If this helps:
Joe Smith's data would be in E92 for every worksheet and in the final
worksheet E92 would be the sum of all of Smith's data.

Someone please help or I'll be doing ALOT of monotonous work....

 

[ntsheehy]

sorry for posting outside the group, just thought I may pick up som
others not looking in this forum!!

Ok, so the actual data is situated a little more complicated than
originally posted. Keeping with the Joe Smith example, he would hav
data in cells E92,E93,E94,G92,G93,G94,I92,I93,I94

What I need to do is add up the data from E92 in Sheets1-3 and have i
totalled in E92 in Sheet 4, doing this for each of the above mentione
cells.

So it would come in handy if I could just copy and paste a formula tha
automatically recognizes the cell it is in and adds up numbers from th
same cell in different Sheets.

Let me know if this is still too confusing.

Sorry again!

 

[RagDyer]

Try this formula, which should do what you're asking.

It'll sum the cells in sheets 1 to 3, equal to whatever cell you paste it
into.

Don't paste it in sheet 1, 2, or 3, or you'll get a circular reference.

=SUM(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN())),INDIRECT("Sheet2!"&ADDRESS(
ROW(),COLUMN())),INDIRECT("Sheet3!"&ADDRESS(ROW(),COLUMN())))

I wish I knew how to make it shorter.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

sorry for posting outside the group, just thought I may pick up some
others not looking in this forum!!

Ok, so the actual data is situated a little more complicated than I
originally posted. Keeping with the Joe Smith example, he would have
data in cells E92,E93,E94,G92,G93,G94,I92,I93,I94

What I need to do is add up the data from E92 in Sheets1-3 and have it
totalled in E92 in Sheet 4, doing this for each of the above mentioned
cells.

So it would come in handy if I could just copy and paste a formula that
automatically recognizes the cell it is in and adds up numbers from the
same cell in different Sheets.

Let me know if this is still too confusing.

Sorry again!!

 

[Bryan]

Still think it's a simple case of type the first formula into e92 sheet 4
then right click that cell, select "copy", right click e93 select "paste",
right click e94 select "paste" and so on. Excel will automatically adjust
the formula for each cell. Shouldn't take a few seconds.

 

[RagDyer]

You're correct when it comes to E92, E93, and E94.
What does the OP do for G92, I92 and the other 200 cells he's looking to
formulate?
--


Regards,

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

Still think it's a simple case of type the first formula into e92 sheet 4
then right click that cell, select "copy", right click e93 select "paste",
right click e94 select "paste" and so on. Excel will automatically adjust
the formula for each cell. Shouldn't take a few seconds.

 

[Ragdyer]

Bryan, when I'm right, I'm right.
But when I'm wrong, I'm very, very, very wrong !!!

You are absolutely correct.
I had my mind wrapped around the OP, and therefore couldn't see the light
when the expanded explanation was posted.

To ntsheehy:

Follow Bryan's advice, and with the setup that you have on your "final" WS,
all you'll have to do is type in only *one* formula.

Enter this in E92 of Sheet4:
=SUM(Sheet1:Sheet3!E92)

Now, just copy this formula anywhere you wish, on Sheet 4, and you'll have
all the totals returned that you need, with no need to type any more.
--
Regards,

RD

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

Still think it's a simple case of type the first formula into e92 sheet 4
then right click that cell, select "copy", right click e93 select "paste",
right click e94 select "paste" and so on. Excel will automatically adjust
the formula for each cell. Shouldn't take a few seconds.

 

[Bryan]

No problem - was beginning to have doubts about my understanding though!

Bryan

Bryan, when I'm right, I'm right.
But when I'm wrong, I'm very, very, very wrong !!!

You are absolutely correct.
I had my mind wrapped around the OP, and therefore couldn't see the light
when the expanded explanation was posted.

To ntsheehy:

Follow Bryan's advice, and with the setup that you have on your "final" WS,
all you'll have to do is type in only *one* formula.

Enter this in E92 of Sheet4:
=SUM(Sheet1:Sheet3!E92)

Now, just copy this formula anywhere you wish, on Sheet 4, and you'll have
all the totals returned that you need, with no need to type any more.
--
Regards,

RD

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