| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
| Change the width of a single column in a column chart | Charts and Charting in Excel | |||
Linear Mode
