fill series that runs along opposite axis?

Anne1320 · #1 07-01-2006, 09:40 AM

I can’t even figure out how to ask this!

How do I create a custom fill series/use some formula(?) that will: increase linearly with a step value of 1, but along the opposite axis from the direction of the target cells. For example,

A1=B1*24
How do I then fill down column A so that:
A2=C1*24
A3=D1*24
A4=E1*24
Etc.

Also need to do the opposite:
B1=A1*24
C1=A2*24
D1=A3*24

I feel like there should be an easy way to do this, but for the life of me…! Thanks for any suggestions.

duane · #2 07-01-2006, 10:41 AM

for the first, in cell a1 and copied down

=OFFSET(A1,-ROW()+1,ROW())*24

for the second, in cell b1 and copied across

=OFFSET(B1,COLUMN()-2,-COLUMN()+1)*24

Anne1320 · #3 07-01-2006, 11:16 AM

Thank you, Duane! That worked fine, and while my actual project is a little different from that exact example (some includes text, and not always on the same sheets, I've now got a good way to tackle the specifics.

Appreciate your help.

#4 07-01-2006, 11:40 AM

Another way ... non-volatile:

Enter anywhere and copy down:
=INDEX($1:$1,ROWS($1:2))*24

Enter anywhere and copy across:
=INDEX($A:$A,COLUMNS($A:A))*24

--

HTH,

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

"Anne1320" <Anne1320.2a9vnb_1151767201.7445@excelforum-nospam.com> wrote in
message news:Anne1320.2a9vnb_1151767201.7445@excelforum-nospam.com...

Thank you, Duane! That worked fine, and while my actual project is a
little different from that exact example (some includes text, and not
always on the same sheets, I've now got a good way to tackle the
specifics.

Appreciate your help.

--
Anne1320
------------------------------------------------------------------------
Anne1320's Profile:
http://www.excelforum.com/member.php...o&userid=35969
View this thread: http://www.excelforum.com/showthread...hreadid=557570

Anne1320 · #5 07-01-2006, 05:32 PM

Thank you for the suggestions. Now the problem I've run into is that the cells being referenced are on one worksheet and the target cells are on another in the same workbook. ???

Can the solution to this be based upon either of the suggestions so far, or do I need to look in another direction?

Thank you for your help.

#6 07-01-2006, 06:10 PM

With the data on Sheet1, and the formulas elsewhere, try this:

=INDEX(Sheet1!$1:$1,ROWS(Sheet1!$1:1))*24
And copy down.

=INDEX(Sheet1!$A:$A,COLUMNS(Sheet1!$A:A))*24
And copy across.
--

HTH,

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

"Anne1320" <Anne1320.2aad0b_1151789701.7185@excelforum-nospam.com> wrote in
message news:Anne1320.2aad0b_1151789701.7185@excelforum-nospam.com...

Thank you for the suggestions. Now the problem I've run into is that the
cells being referenced are on one worksheet and the target cells are on
another in the same workbook. ???

Can the solution to this be based upon either of the suggestions so
far, or do I need to look in another direction?

Thank you for your help.

--
Anne1320
------------------------------------------------------------------------
Anne1320's Profile:
http://www.excelforum.com/member.php...o&userid=35969
View this thread: http://www.excelforum.com/showthread...hreadid=557570

Anne1320 · #7 07-01-2006, 06:38 PM

this set of data is in Row 66, and I've got it referencing the correct row on the correct sheet, but the data doesn't begin in Column A - where do I put the reference to the beginning column (in this case HP)?

Thanks again

#8 07-01-2006, 08:10 PM

You could do it either way:

=INDEX(Sheet1!$66:$66,ROWS(Sheet1!$1:224))*24

OR

=INDEX(Sheet1!$HP$66:$IV$66,ROWS($1:1))*24

--

HTH,

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

"Anne1320" <Anne1320.2aag0n_1151793601.9743@excelforum-nospam.com> wrote in
message news:Anne1320.2aag0n_1151793601.9743@excelforum-nospam.com...

this set of data is in Row 66, and I've got it referencing the correct
row on the correct sheet, but the data doesn't begin in Column A -
where do I put the reference to the beginning column (in this case
HP)?

Thanks again

--
Anne1320
------------------------------------------------------------------------
Anne1320's Profile:
http://www.excelforum.com/member.php...o&userid=35969
View this thread: http://www.excelforum.com/showthread...hreadid=557570

Anne1320 · #9 07-01-2006, 08:22 PM

Thanks, RD!

#10 07-02-2006, 12:25 AM

You're welcome, and appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Anne1320" <Anne1320.2aakvo_1151799902.4796@excelforum-nospam.com> wrote in
message news:Anne1320.2aakvo_1151799902.4796@excelforum-nospam.com...
>
> Thanks, RD!
>
>
> --
> Anne1320
> ------------------------------------------------------------------------
> Anne1320's Profile:
http://www.excelforum.com/member.php...o&userid=35969
> View this thread: http://www.excelforum.com/showthread...hreadid=557570
>

Welcome to the Excel Forum

Forum Statistics