Forumla Help- Change Column value not row
 

In a column have the forumla
='AUT 150 MHz'!B9
I want to drag this down the column but have it change the source column not
row.
Example:
='AUT 150 MHz'!B9
drag down to get
='AUT 150 MHz'!C9
='AUT 150 MHz'!D9

What I am getting is
='AUT 150 MHz'!B9
drag down I get
='AUT 150 MHz'!B10
='AUT 150 MHz'!B11

Thanks

Forumla Help- Change Column value not row
 

Best I can do is...
Drag across the row
Copy paste special down the column
check transpose.

"PboB" wrote:

In a column have the forumla
='AUT 150 MHz'!B9
I want to drag this down the column but have it change the source column not
row.
Example:
='AUT 150 MHz'!B9
drag down to get
='AUT 150 MHz'!C9
='AUT 150 MHz'!D9

What I am getting is
='AUT 150 MHz'!B9
drag down I get
='AUT 150 MHz'!B10
='AUT 150 MHz'!B11

Thanks

Forumla Help- Change Column value not row
 

This formula should work for you:

=INDIRECT("'AUT 150 MHz'!"&ADDRESS(9,ROW()+1))

This assumes your formula is placed in row 1 of your second sheet. If
you're starting in a different row, then adjust the ROW()+X portion
accordingly. Where X = the difference between the row number the formula is
in and 2. So if you start in row 15, you would use ROW()-13

HTH,
Elkar


"PboB" wrote:

In a column have the forumla
='AUT 150 MHz'!B9
I want to drag this down the column but have it change the source column not
row.
Example:
='AUT 150 MHz'!B9
drag down to get
='AUT 150 MHz'!C9
='AUT 150 MHz'!D9

What I am getting is
='AUT 150 MHz'!B9
drag down I get
='AUT 150 MHz'!B10
='AUT 150 MHz'!B11

Thanks

Forumla Help- Change Column value not row
 

Elkar wrote:
This formula should work for you:

=INDIRECT("'AUT 150 MHz'!"&ADDRESS(9,ROW()+1))

This assumes your formula is placed in row 1 of your second sheet. If
you're starting in a different row, then adjust the ROW()+X portion
accordingly. Where X = the difference between the row number the formula is
in and 2. So if you start in row 15, you would use ROW()-13

HTH,
Elkar


"PboB" wrote:

In a column have the forumla
='AUT 150 MHz'!B9
I want to drag this down the column but have it change the source column not
row.
Example:
='AUT 150 MHz'!B9
drag down to get
='AUT 150 MHz'!C9
='AUT 150 MHz'!D9

What I am getting is
='AUT 150 MHz'!B9
drag down I get
='AUT 150 MHz'!B10
='AUT 150 MHz'!B11

Thanks


I believe what you need to do is lock the reference to the row by
adding the $ in front of it, that way when you drag down, it stays
consistent. Remember, when you lock a reference by hitting F4, it
locks row and column, but you don't have to stick to the default.

=Aut 150 MHZ!B$9 should do it for you.

if you need to extend the columns as well just fill the formula down,
select the column, do find replace $ with nothing, reselect the column
and drag/fill to your next column.


I hope that helps.

Dan

Forumla Help- Change Column value not row
 

Or.... ROW(A1)+1 or ROW(1:1)+1 then you wont need to adjust.

Cheers!
Jean-Guy

"Elkar" wrote:

This formula should work for you:

=INDIRECT("'AUT 150 MHz'!"&ADDRESS(9,ROW()+1))

This assumes your formula is placed in row 1 of your second sheet. If
you're starting in a different row, then adjust the ROW()+X portion
accordingly. Where X = the difference between the row number the formula is
in and 2. So if you start in row 15, you would use ROW()-13

HTH,
Elkar


"PboB" wrote:

In a column have the forumla
='AUT 150 MHz'!B9
I want to drag this down the column but have it change the source column not
row.
Example:
='AUT 150 MHz'!B9
drag down to get
='AUT 150 MHz'!C9
='AUT 150 MHz'!D9

What I am getting is
='AUT 150 MHz'!B9
drag down I get
='AUT 150 MHz'!B10
='AUT 150 MHz'!B11

Thanks

Forumla Help- Change Column value not row
 

Hmm... Good point! I suppose we may as well take it even one step further,
and eliminate the +1. ROW(A2)


"pinmaster" wrote:

Or.... ROW(A1)+1 or ROW(1:1)+1 then you wont need to adjust.

Cheers!
Jean-Guy

"Elkar" wrote:

This formula should work for you:

=INDIRECT("'AUT 150 MHz'!"&ADDRESS(9,ROW()+1))

This assumes your formula is placed in row 1 of your second sheet. If
you're starting in a different row, then adjust the ROW()+X portion
accordingly. Where X = the difference between the row number the formula is
in and 2. So if you start in row 15, you would use ROW()-13

HTH,
Elkar


"PboB" wrote:

In a column have the forumla
='AUT 150 MHz'!B9
I want to drag this down the column but have it change the source column not
row.
Example:
='AUT 150 MHz'!B9
drag down to get
='AUT 150 MHz'!C9
='AUT 150 MHz'!D9

What I am getting is
='AUT 150 MHz'!B9
drag down I get
='AUT 150 MHz'!B10
='AUT 150 MHz'!B11

Thanks

Forumla Help- Change Column value not row
 

Try this non-volatile approach:

=INDEX('AUT 150 MHz'!$9:$9,ROWS($1:2))

Copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PboB" wrote in message
...
In a column have the forumla
='AUT 150 MHz'!B9
I want to drag this down the column but have it change the source column
not
row.
Example:
='AUT 150 MHz'!B9
drag down to get
='AUT 150 MHz'!C9
='AUT 150 MHz'!D9

What I am getting is
='AUT 150 MHz'!B9
drag down I get
='AUT 150 MHz'!B10
='AUT 150 MHz'!B11

Thanks

Forumla Help- Change Column value not row
 

Thanks everybody for the suggestions.

I haven't tried them because after posting I finally figured out another
way. What I did was use the formula for
transpose (array)
and then follow the directions hitting F2 then CTRL+Shift+ Enter

Thanks again for your responces!!



"PboB" wrote:

In a column have the forumla
='AUT 150 MHz'!B9
I want to drag this down the column but have it change the source column not
row.
Example:
='AUT 150 MHz'!B9
drag down to get
='AUT 150 MHz'!C9
='AUT 150 MHz'!D9

What I am getting is
='AUT 150 MHz'!B9
drag down I get
='AUT 150 MHz'!B10
='AUT 150 MHz'!B11

Thanks

Forumla Help- Change Column value not row
 

Forgot one little thing, sorry
You have to enter the formula in the starting cell, highlight the range you
are going to fill THEN hit CTRL+SHIFT+Enter

"PboB" wrote:

Thanks everybody for the suggestions.

I haven't tried them because after posting I finally figured out another
way. What I did was use the formula for
transpose (array)
and then follow the directions hitting F2 then CTRL+Shift+ Enter

Thanks again for your responces!!



"PboB" wrote:

In a column have the forumla
='AUT 150 MHz'!B9
I want to drag this down the column but have it change the source column not
row.
Example:
='AUT 150 MHz'!B9
drag down to get
='AUT 150 MHz'!C9
='AUT 150 MHz'!D9

What I am getting is
='AUT 150 MHz'!B9
drag down I get
='AUT 150 MHz'!B10
='AUT 150 MHz'!B11

Thanks