Dragging Across

[mlocurci]

How Can I drag across in excel and perserve the Row Reference.

ie, I need to from =B2, =B3, =B4, ...etc.

 

[xlbo]

Bit confused.....If you drag across (ie left to right), the row reference shouldn't change - the column reference will tho.
??????? Rgds
~Geoff~

 

[mlocurci]

Lwt me put it this way, when I drah across, i need it to do be

b2,b3,b4,b5


Right now, thats not what its doing.

-Michael

 

[xlbo]

Right - so you want to preserve the column reference and increment the row reference.
Suggestion....drag DOWN instead - it'll do exactly as you ask but you'll get a vertical set rather than a horizontal one. To make a horizontal set increment in the way you want is backwards to the way excel works
The only way round this without code is to put the row numbers in your horizontal order ie
insert a row at the top of your spreadsheet (so that B2 is now B3)
In C1, enter 3
In D1, enter 4
select both and drag right until the number increments to the max number of rows you want to pick up in "B"
In C2, enter =INDIRECT("B" &B1)
Drag this formula right - your results should show what is in col B

HTH Rgds
~Geoff~

 

[jmuscat]

One other way is to input B1 in first cell , b2 in second cell across, then highlight both and and fill across as far as you would need.
JMuscat

 

[KatherineCoombs]

an easier way is to use absolute references. In the first instance of the formula tye =$b1. When you drag it across, it will change the number (row) but not the column. The dollar indicates that no matter what it will always refer to "b".

Rather than hand-typing this, you can type =b1 and then press f4 and keep pressing it until you get to =$b1

 

[xlbo]

Sorry Katherine but that ain't correct. If you drag ACROSS, the row will NOT increment. You will get =$b1 in every cell you drag across to

Jmuscat - you would still need the INDIRECT fuction with your suggestion Rgds
~Geoff~

 

[Sopman]

You can do what Jmucat said and then use the replace command. Highlight the row, then replace b with =b

Sopman

 

[mlocurci]

No way to do like a =(b12)+1, =(b12)+2, (b12)+3???

 

[xlbo]

Ok - final stab at this
If you have a list in B2 to B10
In B1, enter
=indirect("B" & column())
You will be able to drag this across and it will increment
You will need to modify depending on which column your start cell is in
For example if you want to start in C1, you would need
=indirect("B" & column()-1)
to return the data in B2 and then drag across

HTH Rgds
~Geoff~