Anyone know how to do this? Here is what I mean:

I have about 40 columns across the page that will have data summed up in row
49. I need the totals in row 49 put into a separate chart for calculations.
(i.e. Column C will have numbers in 1-48 totaled in row 49. Same thing for
all columns from C to BZ)

I want to add the column letters, not the numbers, and that is where I have
the problem.

I have the fomula to read "=C49", so that my total in C49 is pulled to where
I want it for further calculations. Then in the next row below needs to read
"=D49" and then "=E49" and so on, so that all the column totals are now in
one column from top to bottom. How do I get fill down/fill series to add the
column letters? When I use fill down, it starts going C49, then C50, then
C51, which is NOT what i want it to do. I need it to read =C49, the D49, ext
to BZ49.

I also have a more complex formula "=ROUND(C49*C5*24,2)", which will be
using the totals I have moved over, and when I use fill down with it, it adds
the numbers as well, so it goes to "=ROUND(C50*C6*24,2)" instead of
"=ROUND(D49*D6*24,2)".

How do get the letters to "increase" but not the numbers? I suspect I will
have to put a $ in front of the numbers, but I have no idea what to do with
the letters.
Any ideas?

"Rachael" wrote in message
...
Anyone know how to do this? Here is what I mean:

I have about 40 columns across the page that will have data summed up in
row
49. I need the totals in row 49 put into a separate chart for
calculations.
(i.e. Column C will have numbers in 1-48 totaled in row 49. Same thing for
all columns from C to BZ)

I want to add the column letters, not the numbers, and that is where I
have
the problem.

I have the fomula to read "=C49", so that my total in C49 is pulled to
where
I want it for further calculations. Then in the next row below needs to
read
"=D49" and then "=E49" and so on, so that all the column totals are now in
one column from top to bottom. How do I get fill down/fill series to add
the
column letters? When I use fill down, it starts going C49, then C50, then
C51, which is NOT what i want it to do. I need it to read =C49, the D49,
ext
to BZ49.

I also have a more complex formula "=ROUND(C49*C5*24,2)", which will be
using the totals I have moved over, and when I use fill down with it, it
adds
the numbers as well, so it goes to "=ROUND(C50*C6*24,2)" instead of
"=ROUND(D49*D6*24,2)".

How do get the letters to "increase" but not the numbers? I suspect I will
have to put a $ in front of the numbers, but I have no idea what to do
with
the letters.
Any ideas?

If your reference to C49 is going in C50, then try
=OFFSET(C$49,0,ROW()-50) and copy down from there

The same technique will work for your other formula.
--
David Biddulph

THanks, however, I am not sure how to use that formula. I copied it, but I
think there should be something in the second brackets??

Ok..here is a "graphic" representation of my problem:

A B C D E F G H I J
1
2
etc
48
49 totals....................

Now I want the totals from Row 49 to be like this (below it in the same
worksheet)

=A49
=B49
=C49
etc
=BZ49

There are too many for me to enter manually, one by one. If I did, I would
have to click on the cell where I want the total brought down, hit = then
click on the cell with the total in row 49. That's just not possible.

If I put in =A49 in the first row, and highlight, and fill down, it goes
like this

=A49
=A50
=A51

When I use =OFFSET(C$49,0,ROW()-50), it just copies the same thing..no
changes.





"David Biddulph" wrote:

"Rachael" wrote in message
...
Anyone know how to do this? Here is what I mean:

I have about 40 columns across the page that will have data summed up in
row
49. I need the totals in row 49 put into a separate chart for
calculations.
(i.e. Column C will have numbers in 1-48 totaled in row 49. Same thing for
all columns from C to BZ)

I want to add the column letters, not the numbers, and that is where I
have
the problem.

I have the fomula to read "=C49", so that my total in C49 is pulled to
where
I want it for further calculations. Then in the next row below needs to
read
"=D49" and then "=E49" and so on, so that all the column totals are now in
one column from top to bottom. How do I get fill down/fill series to add
the
column letters? When I use fill down, it starts going C49, then C50, then
C51, which is NOT what i want it to do. I need it to read =C49, the D49,
ext
to BZ49.

I also have a more complex formula "=ROUND(C49*C5*24,2)", which will be
using the totals I have moved over, and when I use fill down with it, it
adds
the numbers as well, so it goes to "=ROUND(C50*C6*24,2)" instead of
"=ROUND(D49*D6*24,2)".

How do get the letters to "increase" but not the numbers? I suspect I will
have to put a $ in front of the numbers, but I have no idea what to do
with
the letters.
Any ideas?

If your reference to C49 is going in C50, then try
=OFFSET(C$49,0,ROW()-50) and copy down from there

The same technique will work for your other formula.
--
David Biddulph

"Rachael" wrote in message
...

"David Biddulph" wrote:

"Rachael" wrote in message
...
Anyone know how to do this? Here is what I mean:

I have about 40 columns across the page that will have data summed up
in
row
49. I need the totals in row 49 put into a separate chart for
calculations.
(i.e. Column C will have numbers in 1-48 totaled in row 49. Same thing
for
all columns from C to BZ)

I want to add the column letters, not the numbers, and that is where I
have
the problem.

I have the fomula to read "=C49", so that my total in C49 is pulled to
where
I want it for further calculations. Then in the next row below needs to
read
"=D49" and then "=E49" and so on, so that all the column totals are now
in
one column from top to bottom. How do I get fill down/fill series to
add
the
column letters? When I use fill down, it starts going C49, then C50,
then
C51, which is NOT what i want it to do. I need it to read =C49, the
D49,
ext
to BZ49.

I also have a more complex formula "=ROUND(C49*C5*24,2)", which will be
using the totals I have moved over, and when I use fill down with it,
it
adds
the numbers as well, so it goes to "=ROUND(C50*C6*24,2)" instead of
"=ROUND(D49*D6*24,2)".

How do get the letters to "increase" but not the numbers? I suspect I
will
have to put a $ in front of the numbers, but I have no idea what to do
with
the letters.
Any ideas?

If your reference to C49 is going in C50, then try
=OFFSET(C$49,0,ROW()-50) and copy down from there

The same technique will work for your other formula.

THanks, however, I am not sure how to use that formula. I copied it, but I
think there should be something in the second brackets??

Ok..here is a "graphic" representation of my problem:

A B C D E F G H I J
1
2
etc
48
49 totals....................

Now I want the totals from Row 49 to be like this (below it in the same
worksheet)

=A49
=B49
=C49
etc
=BZ49

There are too many for me to enter manually, one by one. If I did, I would
have to click on the cell where I want the total brought down, hit = then
click on the cell with the total in row 49. That's just not possible.

If I put in =A49 in the first row, and highlight, and fill down, it goes
like this

=A49
=A50
=A51

When I use =OFFSET(C$49,0,ROW()-50), it just copies the same thing..no
changes.

Have you looked at the help for the functions OFFSET and ROW()?

In which cell are you trying to put the formula to point to A49?
If you are in row nn, then use =OFFSET(A$49,0,ROW()-nn) and this will point
at A49, i.e. an offset of zero columns as ROW() is nn and ROW()-nn is zero.
When you copy this down a row, to row nn+1, then ROW() is now nn+1, and
ROW()-nn will be 1, giving an offset of 1 column, and hence it will point at
B49.

Try it.
--
David Biddulph

A B C D E F G H I J
1
2
etc
48
49 totals....................


In row 50, put the formula

=a49 =b49 =c49 and drag across as far as you need, this will give you
the formulas you want. Go back to each and add in the absolute
reference or =$a$49, =$b$49 (I found it would give me a REF# error if I
didn't, though it may work for you)

Copy the whole range you want. Go to the column and cell you want for
the originating cell and edit /paste special / select radio for
formulas and box for transpose.

This should paste down the colum with the totals as you want it.

cls


--
Foodbuy
------------------------------------------------------------------------
Foodbuy's Profile: http://www.excelforum.com/member.php...o&userid=35348
View this thread: http://www.excelforum.com/showthread...hreadid=551086

"David Biddulph" wrote:

"Rachael" wrote in message
...

"David Biddulph" wrote:

"Rachael" wrote in message
...
Anyone know how to do this? Here is what I mean:

I have about 40 columns across the page that will have data summed up
in
row
49. I need the totals in row 49 put into a separate chart for
calculations.
(i.e. Column C will have numbers in 1-48 totaled in row 49. Same thing
for
all columns from C to BZ)

I want to add the column letters, not the numbers, and that is where I
have
the problem.

I have the fomula to read "=C49", so that my total in C49 is pulled to
where
I want it for further calculations. Then in the next row below needs to
read
"=D49" and then "=E49" and so on, so that all the column totals are now
in
one column from top to bottom. How do I get fill down/fill series to
add
the
column letters? When I use fill down, it starts going C49, then C50,
then
C51, which is NOT what i want it to do. I need it to read =C49, the
D49,
ext
to BZ49.

I also have a more complex formula "=ROUND(C49*C5*24,2)", which will be
using the totals I have moved over, and when I use fill down with it,
it
adds
the numbers as well, so it goes to "=ROUND(C50*C6*24,2)" instead of
"=ROUND(D49*D6*24,2)".

How do get the letters to "increase" but not the numbers? I suspect I
will
have to put a $ in front of the numbers, but I have no idea what to do
with
the letters.
Any ideas?

If your reference to C49 is going in C50, then try
=OFFSET(C$49,0,ROW()-50) and copy down from there

The same technique will work for your other formula.

THanks, however, I am not sure how to use that formula. I copied it, but I
think there should be something in the second brackets??

Ok..here is a "graphic" representation of my problem:

A B C D E F G H I J
1
2
etc
48
49 totals....................

Now I want the totals from Row 49 to be like this (below it in the same
worksheet)

=A49
=B49
=C49
etc
=BZ49

There are too many for me to enter manually, one by one. If I did, I would
have to click on the cell where I want the total brought down, hit = then
click on the cell with the total in row 49. That's just not possible.

If I put in =A49 in the first row, and highlight, and fill down, it goes
like this

=A49
=A50
=A51

When I use =OFFSET(C$49,0,ROW()-50), it just copies the same thing..no
changes.

Have you looked at the help for the functions OFFSET and ROW()?

In which cell are you trying to put the formula to point to A49?
If you are in row nn, then use =OFFSET(A$49,0,ROW()-nn) and this will point
at A49, i.e. an offset of zero columns as ROW() is nn and ROW()-nn is zero.
When you copy this down a row, to row nn+1, then ROW() is now nn+1, and
ROW()-nn will be 1, giving an offset of 1 column, and hence it will point at
B49.

Try it.
--
David Biddulph

OK...This is what I did. I looked up help on OFFSET, but I had no clue how
it related. I am using office 97 if that means anything.

in cell A53, I want it to equal the total in cell C49. So I put in:
=OFFSET(C$49,0,ROW()-A53) then I used fill down.

in cell A54, I want it to equal the total in cell D49, but the formula now
reads =OFFSET(C$49,0,ROW()-A54)

Also, nothing is displayed in the box but zero. I need it to show the totals
from the cells C49, D49, etc.

I use excel for basic calculations (sums, multiplying, averages, basic math)
but that's it. I don't know much about other formulas.

"Rachael" wrote in message
...


"David Biddulph" wrote:

"Rachael" wrote in message
...

"David Biddulph" wrote:

"Rachael" wrote in message
...
Anyone know how to do this? Here is what I mean:

I have about 40 columns across the page that will have data summed
up
in
row
49. I need the totals in row 49 put into a separate chart for
calculations.
(i.e. Column C will have numbers in 1-48 totaled in row 49. Same
thing
for
all columns from C to BZ)

I want to add the column letters, not the numbers, and that is where
I
have
the problem.

I have the fomula to read "=C49", so that my total in C49 is pulled
to
where
I want it for further calculations. Then in the next row below needs
to
read
"=D49" and then "=E49" and so on, so that all the column totals are
now
in
one column from top to bottom. How do I get fill down/fill series to
add
the
column letters? When I use fill down, it starts going C49, then C50,
then
C51, which is NOT what i want it to do. I need it to read =C49, the
D49,
ext
to BZ49.

I also have a more complex formula "=ROUND(C49*C5*24,2)", which will
be
using the totals I have moved over, and when I use fill down with
it,
it
adds
the numbers as well, so it goes to "=ROUND(C50*C6*24,2)" instead of
"=ROUND(D49*D6*24,2)".

How do get the letters to "increase" but not the numbers? I suspect
I
will
have to put a $ in front of the numbers, but I have no idea what to
do
with
the letters.
Any ideas?

If your reference to C49 is going in C50, then try
=OFFSET(C$49,0,ROW()-50) and copy down from there

The same technique will work for your other formula.

THanks, however, I am not sure how to use that formula. I copied it,
but I
think there should be something in the second brackets??

Ok..here is a "graphic" representation of my problem:

A B C D E F G H I J
1
2
etc
48
49 totals....................

Now I want the totals from Row 49 to be like this (below it in the same
worksheet)

=A49
=B49
=C49
etc
=BZ49

There are too many for me to enter manually, one by one. If I did, I
would
have to click on the cell where I want the total brought down, hit =
then
click on the cell with the total in row 49. That's just not possible.

If I put in =A49 in the first row, and highlight, and fill down, it
goes
like this

=A49
=A50
=A51

When I use =OFFSET(C$49,0,ROW()-50), it just copies the same thing..no
changes.

Have you looked at the help for the functions OFFSET and ROW()?

In which cell are you trying to put the formula to point to A49?
If you are in row nn, then use =OFFSET(A$49,0,ROW()-nn) and this will
point
at A49, i.e. an offset of zero columns as ROW() is nn and ROW()-nn is
zero.
When you copy this down a row, to row nn+1, then ROW() is now nn+1, and
ROW()-nn will be 1, giving an offset of 1 column, and hence it will point
at
B49.

Try it.
--
David Biddulph

OK...This is what I did. I looked up help on OFFSET, but I had no clue how
it related. I am using office 97 if that means anything.

in cell A53, I want it to equal the total in cell C49. So I put in:
=OFFSET(C$49,0,ROW()-A53) then I used fill down.

in cell A54, I want it to equal the total in cell D49, but the formula now
reads =OFFSET(C$49,0,ROW()-A54)

Also, nothing is displayed in the box but zero. I need it to show the
totals
from the cells C49, D49, etc.

I use excel for basic calculations (sums, multiplying, averages, basic
math)
but that's it. I don't know much about other formulas.

Your problem is a small one, but a significant one.

In A53 instead of =OFFSET(C$49,0,ROW()-A53) you should have had
=OFFSET(C$49,0,ROW()-53)
The final term is 53, not A53. Not only does that remove the circular
reference that you've got by referring to A53 from A53, but it now gives you
the right number (53) to subtract from the row number (also 53 at this
stage) to give you zero offset from C49 and hence point at C49.
For the next row when you copy the formula down to A54 it will stay the same
=OFFSET(C$49,0,ROW()-53) instead of =OFFSET(C$49,0,ROW()-A54)
You are now subtracing 53 from the new row number (54) to give an offset of
one column, & thus point to D49, and so on across the columns as you copy
down to A55 pointing at E49, etc.
--
David Biddulph

"David Biddulph" wrote:

"Rachael" wrote in message
...


"David Biddulph" wrote:

"Rachael" wrote in message
...

"David Biddulph" wrote:

"Rachael" wrote in message
...
Anyone know how to do this? Here is what I mean:

I have about 40 columns across the page that will have data summed
up
in
row
49. I need the totals in row 49 put into a separate chart for
calculations.
(i.e. Column C will have numbers in 1-48 totaled in row 49. Same
thing
for
all columns from C to BZ)

I want to add the column letters, not the numbers, and that is where
I
have
the problem.

I have the fomula to read "=C49", so that my total in C49 is pulled
to
where
I want it for further calculations. Then in the next row below needs
to
read
"=D49" and then "=E49" and so on, so that all the column totals are
now
in
one column from top to bottom. How do I get fill down/fill series to
add
the
column letters? When I use fill down, it starts going C49, then C50,
then
C51, which is NOT what i want it to do. I need it to read =C49, the
D49,
ext
to BZ49.

I also have a more complex formula "=ROUND(C49*C5*24,2)", which will
be
using the totals I have moved over, and when I use fill down with
it,
it
adds
the numbers as well, so it goes to "=ROUND(C50*C6*24,2)" instead of
"=ROUND(D49*D6*24,2)".

How do get the letters to "increase" but not the numbers? I suspect
I
will
have to put a $ in front of the numbers, but I have no idea what to
do
with
the letters.
Any ideas?

If your reference to C49 is going in C50, then try
=OFFSET(C$49,0,ROW()-50) and copy down from there

The same technique will work for your other formula.

THanks, however, I am not sure how to use that formula. I copied it,
but I
think there should be something in the second brackets??

Ok..here is a "graphic" representation of my problem:

A B C D E F G H I J
1
2
etc
48
49 totals....................

Now I want the totals from Row 49 to be like this (below it in the same
worksheet)

=A49
=B49
=C49
etc
=BZ49

There are too many for me to enter manually, one by one. If I did, I
would
have to click on the cell where I want the total brought down, hit =
then
click on the cell with the total in row 49. That's just not possible.

If I put in =A49 in the first row, and highlight, and fill down, it
goes
like this

=A49
=A50
=A51

When I use =OFFSET(C$49,0,ROW()-50), it just copies the same thing..no
changes.

Have you looked at the help for the functions OFFSET and ROW()?

In which cell are you trying to put the formula to point to A49?
If you are in row nn, then use =OFFSET(A$49,0,ROW()-nn) and this will
point
at A49, i.e. an offset of zero columns as ROW() is nn and ROW()-nn is
zero.
When you copy this down a row, to row nn+1, then ROW() is now nn+1, and
ROW()-nn will be 1, giving an offset of 1 column, and hence it will point
at
B49.

Try it.
--
David Biddulph

OK...This is what I did. I looked up help on OFFSET, but I had no clue how
it related. I am using office 97 if that means anything.

in cell A53, I want it to equal the total in cell C49. So I put in:
=OFFSET(C$49,0,ROW()-A53) then I used fill down.

in cell A54, I want it to equal the total in cell D49, but the formula now
reads =OFFSET(C$49,0,ROW()-A54)

Also, nothing is displayed in the box but zero. I need it to show the
totals
from the cells C49, D49, etc.

I use excel for basic calculations (sums, multiplying, averages, basic
math)
but that's it. I don't know much about other formulas.

Your problem is a small one, but a significant one.

In A53 instead of =OFFSET(C$49,0,ROW()-A53) you should have had
=OFFSET(C$49,0,ROW()-53)
The final term is 53, not A53. Not only does that remove the circular
reference that you've got by referring to A53 from A53, but it now gives you
the right number (53) to subtract from the row number (also 53 at this
stage) to give you zero offset from C49 and hence point at C49.
For the next row when you copy the formula down to A54 it will stay the same
=OFFSET(C$49,0,ROW()-53) instead of =OFFSET(C$49,0,ROW()-A54)
You are now subtracing 53 from the new row number (54) to give an offset of
one column, & thus point to D49, and so on across the columns as you copy
down to A55 pointing at E49, etc.
--
David Biddulph


Thank you. That does make sense now. It doesn't actually change the value in
the formula to read "D49" or "E49" but it still pulls the numbers down.
Thanks. Now I just need to play with the other formula to see if I can get it
to work.