with the data as you posted, follow the procedure set out above, but use this formula:
=OFFSET(G3,0,(COLUMN()-COLUMN($E:$E))*-1,1,1)=OFFSET(G3,(ROW()-ROW($2:$2))*-1,0,1,1)
offset has the syntax:
OFFSET(reference,rows, cols,height,width)
so what we're doing is seeing if one offset value = another offset value. Lets take the first one:
=OFFSET(G3,0,(COLUMN()-COLUMN($E:$E))*-1,1,1)
offset(G3...
the G3 is the start cell. Cos we're entering this as a relative refernece (rather than, say, $e$3, it will update appropriately for all the cells we put the formula in.
=OFFSET(G3,0...
this says offset by no rows - so this one is getting the day value from the column. Question is, which one?
=OFFSET(G3,0,(COLUMN()-COLUMN($E:$E))*-1...
column() returns the column number of the cell. For cell g3 this is 7. The column we want to offset to is col e. the column number for col e is 5. So we want to offset by 2 columns. COLUMN()-COLUMN($E:$E) - the number returned by column() will change for each different column the formula is in; the number returned by COLUMN($E:$E) will always be 5 (cos it's fixed wqith an absolute reference) so we'll always know how many cols we are away from col e. The *-1 bit is 'cos positive column offsets go to the right - we need a negative one to go to the left.
=OFFSET(G3,0,(COLUMN()-COLUMN($E:$E))*-1,1,1)
the last 2 values tell excel to return the data from one cell only.
The second offset is essentially the same, but for rows:
=OFFSET(G3,(ROW()-ROW($2:$2))*-1,0,1,1
where the ROW()-ROW($2:$2))*-1 works out how many rows to offset...
If you need, change the column($e:$e) and row($2:$2) bits in the formula to whichever column / row the day names are in & it should always work...
Paddy
