Quote:
Originally Posted by Claus Busch
Hi John,
Am Thu, 15 Nov 2012 00:14:43 +0000 schrieb Johnrd1963:
I manage a maintenance schedule for equipment. I have a table with 11
columns and about 80 rows. The top row lists the equipment name. The
left most column lists the type of maintenance required. The
intersecting cells in the table have the date which the maintenance for
the particular piece of equipment is due to be done. Some of the dates
in the cells occur more than once and some cells are blank. I need 2
formulas, one which return the equipment name (top row) for the first
occurrence of a specific date entered in a cell on the worksheet and one
which will do the same for the work required (left most column). If the
formula fails to find a date, it needs to return a blank space in the
cell.
your search date in L1
for equipment name:
=INDEX(A1:A100,SUMPRODUCT((A1:J100=L1)*(ROW(1:100) )))
for work:
=INDEX(A1:J1,SUMPRODUCT((A1:J100=L1)*(COLUMN(A:J)) ))
Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|
Thanks for the response. I have already tried a variation of this formula with the SUMPRODUCT function. It doesn't work (returns an error) when used with dates that are listed more than once. What I need when it finds more than one cell with the same date, I need it to return the row/column header of the first occurring cell, from top to bottom, in the table.