How to change reference area automatically by dragging Index Match Formula down

**Nuada** · 06-20-2013, 11:19 AM

Hi All,
I am really really stuck.
I have the following formula:
{=INDEX(A16:A67,MATCH(TRUE,C16:C67<>"",0))} which gives me the result I need which is a date from the very far left of a worksheet
The worksheet is a wages report from Sage which was exported to excel, unfortunelt the layout is also like a report and not neatly laid out in columns for each categroy of data, they are stacked instead.
anyway I need to find the fist date a person was paid, I trying to this by locating a the first cell with an entry in a certain area of cells ..... C16:C67... and then returning the value from the far left cell which is the first payment date.
Simple right?
Howeve I need to be able to drag the formula or somehow automatically move it to the new cell down but also to change the area of cells being searched downwards by exactly 60 cells so as it searches the area of C76:C127 , and so on downwards through the column with my formula.
Can anyone please help me , it is very urgent. THANK YOU!!!!!!!!

**XOR LX** · 06-20-2013, 01:06 PM

Hi,

You don't say, but I presume that the range in the A column needs to change similarly?

Try this in your first row and drag down:

=INDEX(INDIRECT("A"&16+60*(ROWS($A$1:$A1)-1)&":A"&67+60*(ROWS($A$1:$A1)-1)),MATCH(TRUE,INDIRECT("C"&16+60*(ROWS($A$1:$A1)-1)&":C"&67+60*(ROWS($A$1:$A1)-1))<>"",0))

Regards