Incrementing cell addresses

  • Thread starter Thread starter LaRana!
  • Start date Start date
L

LaRana!

I building a worksheet where I have 3 agents, each agent has its own set of
data as follows:
02/28/08
Name - # of payments - Hours
Mark 2 8.00
john 3 9.00
jim 4 6.00
02/29
Name - # of payments - Hours
Mark 2 8.00
john 3 9.00
jim 4 6.00

I want to use the above data to be linked to cells in the following format:
mark 02/28 02/29....etc
payments 2 2
Hours 3 3

So, as I am updating each agent daily, the same data is updated in a
horizontal fashion (by date). Also, since I have several agents and I want to
able to drag my formula thru the whole month, I would need to increment each
cell address by not 1 but by 5 (as I have in my example)..Can that be done?
 
One play, illustrated in this sample:
http://www.freefilehosting.net/download/3cm4a
Extract transverse by name n date.xls

Source data as posted in cols A to C, 5 lines per block
Order of the names within each day's block is immaterial

With a DV to select the name in E2, dates listed in F2 across
Put in F3:
=IF(OR($E$2="",F$2=""),"",OFFSET(INDIRECT("A"&MATCH(F$2,$A:$A,0)),MATCH($E$2,OFFSET(INDIRECT("A"&MATCH(F$2,$A:$A,0)),,,5),0)-1,ROWS($1:1),))
Copy F3 across/fill down to populate the table
 
Back
Top