Increment rows from one sheet and columns from another when dragging formula horizontally

[ExceL0ver]

Hi all,

I have the formula =Cata!AE10-'Calc'!H5, and when I drag this across, I would like the rows in column AE to increment by 1 and the columns in the Calc sheet to increment by 1, therefore formula in the cell to the right should be =Cata!AE11-'Calc'!I5 and so on.

I'm guessing that the function INDIRECT would help here, but can't figure out how to get it to work. Does anyone have a suggestion, please?

 

[JamesCanale]

MrExcelPlayground17.xlsx
	B	C	D	E	F	G	H
2	2	3	4	5	6	7	8
3	3	4	5	6	7	8	9
4	4	5	6	7	8	9	10
5	5	6	7	8	9	10	11
6	6	7	8	9	10	11	12
7	7	8	9	10	11	12	13
8	8	9	10	11	12	13	14
Sheet24

Cell Formulas
Range	Formula
B2:H8	B2	=OFFSET(Sheet22!$AE$10,ROW($A2)-ROW($A$2),0)-OFFSET(Sheet21!$H$5,0,COLUMN(B$1)-COLUMN($B$1))

 

[Fluff]

Another option

=INDEX(Cata!$AE:AE,COLUMNS($A$1:A$1)+9)-Calc!H5

 

[ExceL0ver]

MrExcelPlayground17.xlsx
	B	C	D	E	F	G	H
2	2	3	4	5	6	7	8
3	3	4	5	6	7	8	9
4	4	5	6	7	8	9	10
5	5	6	7	8	9	10	11
6	6	7	8	9	10	11	12
7	7	8	9	10	11	12	13
8	8	9	10	11	12	13	14
Sheet24

Cell Formulas
Range	Formula
B2:H8	B2	=OFFSET(Sheet22!$AE$10,ROW($A2)-ROW($A$2),0)-OFFSET(Sheet21!$H$5,0,COLUMN(B$1)-COLUMN($B$1))

When I use this formula it only seems to increment the columns in the Calc sheet, but not the rows in column AE in sheet Cata.

 

[JamesCanale]

I expect I don't understand your requirements. Are you only dragging across columns in one row? In which case, Fluff is right.

 

[ExceL0ver]

Another option

=INDEX(Cata!$AE:AE,COLUMNS($A$1:A$1)+9)-Calc!H5

Unfortunately, this doesn't work either. When dragged to the right, the formula becomes =INDEX(Cata!$AE:AF,COLUMNS($A$1:B$1)+9)-Calc!I5 and I get a #REF! error.

 

[Fluff]

Oops missed the $ before the 2nd AE, it should be

=INDEX(Cata!$AE:$AE,COLUMNS($A$1:A$1)+9)-Calc!H5

 

[JamesCanale]

So this?

MrExcelPlayground17.xlsx
	B	C	D	E	F	G	H
2	2	4	6	8	10	12	14
Sheet24

Cell Formulas
Range	Formula
B2:H2	B2	=OFFSET(Sheet22!$AE$10,COLUMN(B$1)-COLUMN($B$1),0)-Sheet21!H5

 

[ExceL0ver]

I expect I don't understand your requirements. Are you only dragging across columns in one row? In which case, Fluff is right.

I am dragging the formula to the right.

I placed the formula in the starting cell H4 in sheet "Calc", and when I dragged to the right, the column changes from H to I which is correct, however the rows in column AE should also increment but your suggestion causes AE10 to increment by columns to AF10, not rows to AE11.

 

[ExceL0ver]

Oops missed the $ before the 2nd AE, it should be

=INDEX(Cata!$AE:$AE,COLUMNS($A$1:A$1)+9)-Calc!H5

Perfect! I spent ages trying to get this to work to no avail, thank you very much!

JamesCanale, thank you for your help, too.