Multiple IF statements in one cell

[DeanH]

Excel 2003 on XP.
I have a project control listing that has, in part of it, nine columns that
will hold the dates that certain stages are met, i.e. a date will be entered
as each stage is met and the following stages will be empty, until Stage 9 -
Project Complete/Signed Off.
Stages 1-9 each have a unique Stage Name/defintion.
I would like a following cell in the row to automatically show the Stage
Name for the latest date in the corresponding stage cell for that project.
I have a nested IF statement that works but as the project list is to get
very large I would like a neater way of doing the autochange of the Stage
Name as new dates are entered, that will not add too much to the file size.
Hope this clear?
Thanks in advance for any help.
DeanH

 

[Pete_UK]

Can you give details of the cells/columns that you will be using in
your expanded project?

Pete

 

[DeanH]

Sorry forgot to say, the Defintions I am picking up from a list on another
sheet.

 

[DeanH]

Pete,
ex:
Stage 1 = col I, Stage 9 = Q
Data starts on row = 6
So for the first project (row 6) in cell R6 (Stage Defintion) I have a
nested IF statement (of the maximum 8 nests) that looks back over cells
I6-Q6. When a date is enter into I6, text of "Created", as more dates are
added under the relevent stage, the Stage Defintion changes, so if dates are
added upto N6 (stage 6) the relevent stage definition appears in R6. And then
when date added into O6 (stage 7) the defintion changes again.

As I have hit the limit of nests, I have used Conditional formatting to hide
the Created defintion until a date is entered. The rest of the stages are
covered by the nested IF statement, but this is not nice to look at and will
I am sure affect file size in the future as this large formual is repeated.

Hope this helps
DeanH

 

[Don Guillett]

This is not so easy to understand without seeing. If desired, send your wb
to my address below with a clear explanation and before/after examples.

 

[Pete_UK]

Dean,

put this in R6:

=IF(I6="","",INDEX(I$5:Q$5,MATCH(LOOKUP(10E10,I6:Q6),I6:Q6,0)))

and then copy down as required.

I've assumed that the dates on any one row are different, and that the
cells are empty until you put a date in them.

Hope this helps.

Pete

 

[DeanH]

Fantastic Pete, works a treat.
I would never have got this, but why the 10E10?
Many thanks
DeanH

 

[DeanH]

Pete wrote:
"the dates on any one row are different" - not necessarily, some stages can
be done in the same day, so you could get a repeat on one line. I have just
noticed that the formula returns the occurance of the first date not the
last, ie an earlier stage than required. How can be corrected?

"the cells are empty until you put a date in them" - correct.

Thanks again Pete.
DeanH

 

[Pete_UK]

Dean,

I'm glad it (partially) works for you.

The 10E10 is just a large number, so that the LOOKUP finds the last
entry in that range.

The MATCH function is then finding which cell of the range that occurs
in, and unfortunately it works by returning the first occurence of any
duplicate dates, so the formula will then report the wrong column.

One way around it without changing the formula is to enter duplicate
dates with a (nominal) time element as well, so if you already have
28th April in one column and need to put that same date in the next
column then enter the second date as 28/4/09 12:00 (for example - the
first date won't need a time added to it). If you have a third entry
of the same date, put this in as 28/4/09 18:00, and so on. Format all
those cells as dates so that the time element won't display.

Hope this helps.

Pete

 

[Don Guillett]

Try
=INDEX($I$5:$Q5,1,MATCH(9999999,I6:Q6))

 

[Don Guillett]

Minor correction to add a $
=IF(I8="","",INDEX($I$5:$Q$5,1,MATCH(9999999,I8:Q8)))