Creating absolute formula

[vbthedog]

I have been trying to get this sorted on and off for ages. I have a expenses spreadsheet with a tab for each month and a tab for a totals consolidation. If in the consolidated tab containing the formula for October's Groceries is ='Exp. Oct'!C!41, how do a manually enter the formula for Fuel which when pasted is ='Exp. Oct'!D4 so I can quickly replicate it down the column. That is, the foirmula to keep the row constant but the column relative. Every version I have tried using !, $ and ' either changes the row number (keeping column constant), gives an error or gives an #REF message.

TIA

 

[maabadi]

Try:

='Exp. Oct'!C$41

 

[vbthedog]

Try:

='Exp. Oct'!C$41

No. When I replicate that down, all cells then have the same formula. And if I remove the '!' I get an error.

 

[maabadi]

Try this:
for Method 1, First select all range, input formula then CTRL+SHIFT+ENTER

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3
4			45	50	55	60	65	70	75
5
6			Method 1	Method 2	Method 3
7			45	45	45
8			50	50	50
9			55	55	55
10			60	60	60
11			65	65	65
12			70	70	70
13			75	75	75
14
15
Sheet1

Cell Formulas
Range	Formula
C7:C13	C7	=TRANSPOSE(C4:I4)
D7:D13	D7	=OFFSET($C$4,0,ROWS($D$6:D6)-1,1,1)
E7:E13	E7	=INDEX($C$4:$I$4,ROWS($E$6:E6))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[vbthedog]

Try this:
for Method 1, First select all range, input formula then CTRL+SHIFT+ENTER

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3
4			45	50	55	60	65	70	75
5
6			Method 1	Method 2	Method 3
7			45	45	45
8			50	50	50
9			55	55	55
10			60	60	60
11			65	65	65
12			70	70	70
13			75	75	75
14
15
Sheet1

Cell Formulas
Range	Formula
C7:C13	C7	=TRANSPOSE(C4:I4)
D7:D13	D7	=OFFSET($C$4,0,ROWS($D$6:D6)-1,1,1)
E7:E13	E7	=INDEX($C$4:$I$4,ROWS($E$6:E6))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[vbthedog]

Sorry I may be thick, but don't quite follow..... I thought it would be a simple use of ! and $ in the right places?

 

[maabadi]

You're Welcome & Thanks for follow-up.