drag down formula, but i want row to stay fixed, but columns to increase

[daveyc18]

so i have cell B2 referring to another worksheet (cell e34)

now, i want to drag down the formula, but i want the column to increase but the row to stay fixed (e.g, when dragged down to cell b3, it refers to f34 in the other worksheet, b4 = g34, b5 =h34, etc.)

 

[Joe4]

Place this in cell B2 and copy down (note that you will need to change the sheet name reference):

=OFFSET(Sheet2!$E$34,0,ROW()-2)

 

[Crystalyzer]

Place this in Cell B2 and copy down.

=INDIRECT("Sheet2!R[" & COLUMN() + 32-ROW() & "]C[" & ROW()+1 & "]",FALSE)

 

[Crystalyzer]

@Joe4 Much nicer and cleaner solution!!

 

[Joe4]

@Joe4 Much nicer and cleaner solution!!

Thanks. Note that many people do not like either OFFSET or INDIRECT, due to their volatile nature, and prefer to use INDEX.

INDIRECT – Excel’s Most Evil Function

My choice for Excel’s most evil worksheet function is INDIRECT, for reasons outlined below. If you have a different choice you can cast your vote for your most evil function (and your favouri…

fastexcel.wordpress.com

Excel 2020: Use A2:INDEX() as a Non-Volatile OFFSET

There is a flexible function called OFFSET. It can point to a different-sized range that is calculated on-the-fly.

www.mrexcel.com

I admit I have never tried to use it this way, as OFFSET typically works for me, as I seldom update my data once my analysis is finished (just the nature of my business - doing lots of one time analysis on a particular data set from that day).

 

[daveyc18]

tks!