Auto filling a formula

[shina67]

Hi All,

When autofilling a formula by dragging down, the row number changes in the formula. Is there a way of making the row number stay the same and the column number change.
i.e. =M1000 would become =N1000 in the cell beneath.

Also the same question for dragging across the row. I would want the row number changing and not the column letter.
i.e. =N1000 would become =N1001 in the cell adjacent.

Hope the above makes sense.

Any help would be appreciated as there are over 30,000 cells to apply this to.

 

[awoohaw]

To lock the row number and allow the column to change put a $ sign in front of the Row Number. To lock column put $ in front of Column letter.
if you highlight the cell reference in your first formula in the range you can press F4 to cycle through the different combinations of referencing cells. Then copy down.
There are 4 different reference versions.

here is a microsoft link on it:

Switch between relative, absolute, and mixed references - Microsoft Support

Use absolute or relative cell references in formulas, or a mix of both.

support.microsoft.com

 

[shina67]

To lock the row number and allow the column to change put a $ sign in front of the Row Number. To lock column put $ in front of Column letter.
if you highlight the cell reference in your first formula in the range you can press F4 to cycle through the different combinations of referencing cells. Then copy down.
There are 4 different reference versions.

here is a microsoft link on it:

Switch between relative, absolute, and mixed references - Microsoft Support

Use absolute or relative cell references in formulas, or a mix of both.

support.microsoft.com

Hi,

thanks for the reply. That doesn't work. please see below pic. Pic 1 shows the starting formula. If I apply the $ to the formula and drag down Pic2 is what I get.
In pic2 I need the formula to be ='Holiday Tracker'!N$1000.

The same applies if I drag across the row.

 

[awoohaw]

I though I wrote for you scenario to put the $ in front of the ROW number, so use M$1000 to lock the row value in that column, you copied $M1000 down.

If you want to always reference cell N1000 and never have any formula change then $N$1000
if you always want to show your the value in row 1000 in the rows going down then M$1000 (this will change as you copy to a column)
if you always want to use the value the value in column N as you copy to new columns then $M1000 (this will change as you copy down rows)

Did you look at the link I posted?

 

[MARK858]

The OP needs the column to increment as the formula is dragged down

 

[shina67]

That's exactly what I am requiring.
Also the row to increment as the formula is dragged across.

 

[MARK858]

As a basic example

Book1
	A	B	C	D	E	F	G	H	I	J
1
2							Row increment
3	1	2	3	4	Column incre	1	1	3	5	7
4	3					2
5	5					3
6	7					4
Sheet3

Cell Formulas
Range	Formula
G3:J3	G3	=OFFSET($A$3,COLUMN(A1)-1,0)
F3:F6	F3	=OFFSET($A$3,0,ROW(A1)-1)

Edit:
Or a bit more efficient

Book1
	A	B	C	D	E	F	G	H	I	J
1
2							Row increment
3	1	2	3	4	Column incre	1	1	3	5	7
4	3					2
5	5					3
6	7					4
Sheet3

Cell Formulas
Range	Formula
G3:J3	G3	=INDEX($A:$A,COLUMNS($A$1:C$1))
F3:F6	F3	=INDEX($3:$3,ROWS(B$1:B1))

 

[shina67]

As a basic example

Book1
	A	B	C	D	E	F	G	H	I	J
1
2							Row increment
3	1	2	3	4	Column incre	1	1	3	5	7
4	3					2
5	5					3
6	7					4
Sheet3

Cell Formulas
Range	Formula
G3:J3	G3	=OFFSET($A$3,COLUMN(A1)-1,0)
F3:F6	F3	=OFFSET($A$3,0,ROW(A1)-1)

Edit:
Or a bit more efficient

Book1
	A	B	C	D	E	F	G	H	I	J
1
2							Row increment
3	1	2	3	4	Column incre	1	1	3	5	7
4	3					2
5	5					3
6	7					4
Sheet3

Cell Formulas
Range	Formula
G3:J3	G3	=INDEX($A:$A,COLUMNS($A$1:C$1))
F3:F6	F3	=INDEX($3:$3,ROWS(B$1:B1))

Hi MARK858,

Thanks for that and works brilliantly,

The problem I have is I have 365 rows to populate.
When I've inserted the formula as in your example for the columns across 80 cells I then want to autofill down the 365 rows.

 

[MARK858]

Hi MARK858,

Thanks for that and works brilliantly,

The problem I have is I have 365 rows to populate.
When I've inserted the formula as in your example for the columns across 80 cells I then want to autofill down the 365 rows.

Then use the formula for the across and then reference those cells in the formula down (both formulas are designed to increment in the direction specified)