Absolute value resolution

  • Thread starter Thread starter Salesguy
  • Start date Start date
S

Salesguy

Can I use Absolute Values in a row to reference cells in a column?
Example:

I want cells A100 to A126 to reference columns A1 to to A26 by using
Absolute Values and auto filling the column. So...
A100 = A1
A101 = B1
A102 = C1
 
If you meant [cells] A1 to Z1 rather than A1:A26, in A100 use this formula:

=OFFSET($A$1,0,ROW()-100)

Or you could select cells A100:A125 and enter this array formula:

=TRANSPOSE($A$1:$Z$1)

enter the above with CTRL+SHIFT+ENTER.
 
Thanks Tom. When I pasted this formula into the cell, I got #REF!.
Perhaps I am missing some info. Here is what I have:
A1=10, A2=10, A3=10, A4=10, A5=40

I copied your formula in B1 and drug it down to B6. Each cell says
#REF!

Thanks for your help,
John
 
You are supposed to put that formula in row 100
although if you do you might want to change Tom's formula to

=OFFSET($A$1,ROW()-100,0)

=OFFSET($A$1,ROW()-101,0)

will work from row 101

if you want to use B1

=OFFSET($A$1,ROW()-1,0)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
I believe my explination must be wrong. May I pose a new scenario to
make it easier for me?

A1=10
B1=10
C1=10
D1=10
E1=40 (sum of A1:D1)

I want to input a formula into A2 that I can drag down to A6 and auto
fill the results. So, the results would be:

A2=10
A3=10
A4=10
A5=10
A6=40

It is basically taking a L-R and converting it to Top-Bottom. If you
can tell me which formula I enter into A2 that I can drag down to A6
and accomplish the above result, that would be great.

Thanks everyone for your help with this.
John


Myrna Larson said:
If you meant [cells] A1 to Z1 rather than A1:A26, in A100 use this formula:

=OFFSET($A$1,0,ROW()-100)

Or you could select cells A100:A125 and enter this array formula:

=TRANSPOSE($A$1:$Z$1)

enter the above with CTRL+SHIFT+ENTER.

Can I use Absolute Values in a row to reference cells in a column?
Example:

I want cells A100 to A126 to reference columns A1 to to A26 by using
Absolute Values and auto filling the column. So...
A100 = A1
A101 = B1
A102 = C1
 
=OFFSET($A$1,0,ROW()-1)

But beware that in A6 you will have the same OFFSET formula, not =SUM(A1:A5)

If you want to permanently change the orientation of the data, you can do that
by Edit/Copy, then Edit/Paste Special and check the Transpose option.


I believe my explination must be wrong. May I pose a new scenario to
make it easier for me?

A1=10
B1=10
C1=10
D1=10
E1=40 (sum of A1:D1)

I want to input a formula into A2 that I can drag down to A6 and auto
fill the results. So, the results would be:

A2=10
A3=10
A4=10
A5=10
A6=40

It is basically taking a L-R and converting it to Top-Bottom. If you
can tell me which formula I enter into A2 that I can drag down to A6
and accomplish the above result, that would be great.

Thanks everyone for your help with this.
John


Myrna Larson <[email protected]> wrote in message
If you meant [cells] A1 to Z1 rather than A1:A26, in A100 use this formula:

=OFFSET($A$1,0,ROW()-100)

Or you could select cells A100:A125 and enter this array formula:

=TRANSPOSE($A$1:$Z$1)

enter the above with CTRL+SHIFT+ENTER.

Can I use Absolute Values in a row to reference cells in a column?
Example:

I want cells A100 to A126 to reference columns A1 to to A26 by using
Absolute Values and auto filling the column. So...
A100 = A1
A101 = B1
A102 = C1
 
Back
Top