How can I drag Excel formulas down without changing the row number, or right without changing the column number?

Question

Removed the = from the formulas to allow me to post this...

I have a sheet with 200 columns, and in all of them I need to achieve the following:

Col A

CLEAN('Sheet003'!C2)
CLEAN('Sheet003'!D2)
CLEAN('Sheet003'!E2)
CLEAN('Sheet003'!F2)

Dragging down changes the row number, but I need that to stay the same. I just need the column letter to change as above.

Col B

CLEAN('Sheet003'!C3)
CLEAN('Sheet003'!D3)
CLEAN('Sheet003'!E3)
CLEAN('Sheet003'!F3)

If I drag to the right to apply the formula to the entire row (which is what I want), it changes the column, but I want that to stay the same.

Does anyone have a solution to this that only involves dragging or some quick trick to achieve the drag across the entire row?

So, let me see if I understand, As you drag down you want to change the Column and when you drag across you want to change the row? — Scott Craner
For Col A you can put the "$" in front of the number ie 'Sheet3'!C$2, so when you drag down the number won't change but the column will if you drag right. Similar argument for putting a "$" in front of the letter... — Solar Mike

Lisa Lisa · Accepted Answer · 2018-11-30T15:59:58

OFFSET can be used here. If the data of Sheet003 starts at C2, input the formula below in cell A2 and you can drag down or right:

=CLEAN(OFFSET(Sheet003!$C$2,COLUMN()-1,ROW()-2))