Change Column Reference When Formula is Dragged Down

[billbilliamson]

How can I change the Column reference as a formula is dragged down?

For example,

Sumif(sheet1!CY:CY,

When I drag down the formula, I want it to change to be:

Sumif(sheet1!CZ:CZ,

And so on.

Is it some kind of Indirect or Offset formula?

Thanks!

 

[shknbk2]

Offset works. I entered some sample data into a blank worksheet to prove the concept to myself. Use this as a starting point and alter any portions as necessary. The A1, A2, A3, etc. change as I drag down.

Book1
	A	B	C	D	E	F
1	3		1	1	1	1
2	2		1	1	1	2
3	4		2	2	2	2
4	1		1	3	3
5					1
6					5
7					1
Sheet1

Cell Formulas
Range	Formula
A1	=SUMIF(OFFSET(C:C,0,ROW(A1)-1),"="&1)
A2	=SUMIF(OFFSET(C:C,0,ROW(A2)-1),"="&1)
A3	=SUMIF(OFFSET(C:C,0,ROW(A3)-1),"="&1)
A4	=SUMIF(OFFSET(C:C,0,ROW(A4)-1),"="&1)

 

[ask2tsp]

As an example values on Sheet1

Excel Workbook
	A	B	C	D	E
1	11	22	33	44	55
values

formulas on Sheet2

Excel Workbook
	A	B
1	Sheet1!A1	11
2	Sheet1!B1	22
3	Sheet1!C1	33
4	Sheet1!D1	44
5	Sheet1!E1	55
Sheet