Adding "letters (column headings)"

[Rachael]

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?

[David Biddulph]

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

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

[David Biddulph]

"Rachael" <[email protected]> wrote in message
news:[email protected]...

> "David Biddulph" wrote:
>
>> "Rachael" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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

[Foodbuy]

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

[Rachael]

"David Biddulph" wrote:

> "Rachael" <[email protected]> wrote in message
> news:[email protected]...
>
> > "David Biddulph" wrote:
> >
> >> "Rachael" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.

[David Biddulph]

"Rachael" <[email protected]> wrote in message
news:[email protected]...
>
>
> "David Biddulph" wrote:
>
>> "Rachael" <[email protected]> wrote in message
>> news:[email protected]...
>>
>> > "David Biddulph" wrote:
>> >
>> >> "Rachael" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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]

"Rachael" <[email protected]> wrote in message
news:[email protected]...
>
>
> "David Biddulph" wrote:
>
>> "Rachael" <[email protected]> wrote in message
>> news:[email protected]...
>>
>> > "David Biddulph" wrote:
>> >
>> >> "Rachael" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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]

"Rachael" <[email protected]> wrote in message
news:[email protected]...
>
>
> "David Biddulph" wrote:
>
>> "Rachael" <[email protected]> wrote in message
>> news:[email protected]...
>>
>> > "David Biddulph" wrote:
>> >
>> >> "Rachael" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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

[Rachael]

"David Biddulph" wrote:

> "Rachael" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > "David Biddulph" wrote:
> >
> >> "Rachael" <[email protected]> wrote in message
> >> news:[email protected]...
> >>
> >> > "David Biddulph" wrote:
> >> >
> >> >> "Rachael" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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.