Formula going across but referencing a cell going down

[DEllis]

I have two worksheets. On one I want to copy formula across 6 cells (Column H - M). I want to reference the data that is on the second spreadsheet in Column I, Row 9 for H and Column I, Row 10 for I, etc.

Is there a way to do this?

Thank you,
DEllis

 

[Jonmo1]

Try

=INDEX(sheet2!$I:$I,(COLUMNS($A1:A1)-1)+9)

 

[DEllis]

This gives me the referenced cell on the second sheet in row 9 but when I copy the fomula across on the first page I get the same results, not what is going down on row 10, row 11, etc. from the second sheet.

 

[Jonmo1]

Works for me.

Make sure you enter the formula EXACTLY as Posted, change only the sheetname if needed.
Enter it in only 1 cell, then FILL it to the right.

Excel Workbook
	H	I	J	K	L	M
1	A	B	C	D	E	F
Sheet1

Excel Workbook
	I
9	A
10	B
11	C
12	D
13	E
14	F
Sheet2

 

[DEllis]

Wow! This is awesome. Can you explain to me what you did so I will not have to ask for this again at another time!?