Nested Ifs

**Mrsm** · 05-30-2007, 07:38 AM

Please help!!! I have been trying to create a nested If and am getting frustrated.

I need to display FALSE in cell A5 where cell A1 displays the words "completed", "cancelled", "Rejected" or "Resolved" AND do not have a corresponding date in cell A2. If there is a date in A2 - I want to display true.

However, if A1 displays the words "Approved", "Submitted for Approval" "In Development" or "App for IA" I want A5 to display True where there is a date in A2 and a False where there is no date in A2.

Please help me - I'm losing the will to live!!

**NBVC** · 05-30-2007, 08:06 AM

Does this work for you?

=OR(AND(OR(A1={"completed","cancelled","Rejected","Resolved"}),A2>0),AND(OR(A1={"Approved","Submitted for Approval","In Development","App for IA"}),A2>0))

**sweep** · 05-30-2007, 08:09 AM

Hi,

Try this in A5

=IF(MATCH(A1,{"completed","cancelled","Rejected","Resolved","Approved","Submitted for Approval","In Development","App for IA"},0)<=4,IF(ISBLANK(A2),TRUE,FALSE),IF(ISBLANK(A2),FALSE,TRUE))

**Mrsm** · 05-30-2007, 08:50 AM

Thank you so Much!!! It worked and I really appreciate the help - as well as the quick reply!

Thanks again

**Mrsm** · 06-06-2007, 09:50 AM

Hi,

Apprecaite the help last time, but now I am stuck again on the same spreadsheet.

I used the formula given to me by NBVC and it worked. However, I now want to create another iF statement from the one given to me and it is not returning the desired information.

For example: The formula is now pulling True or False statements. I now want to create an IF statement that enters a value from another cell dependant on whether the statement is TRUE or FALSE.

I have attached my spreadsheet as I am finding this one difficult to explain.

If you have a look at the worksheet 'CR Quickbase' - Cell G2 has the formula given by NBVC. If you have a look in cell H2 - you will hopefully get the gist of what I am trying to achieve. Once we have the relevant info in cell H2.

The worksheet 'CR Tracking' will then 'Lookup' the value in cell H2 and return the relevant data.

Please help - it's not as complicated as I am making it sound - just have a look at the attachment.

Thanks in advance

**NBVC** · 06-06-2007, 10:05 AM

The True/False strings should not be in quotes.

Try:

=IF(G2=TRUE,$A$38,IF(G2=FALSE,$A$39,"")) copied down.

EDIT:

come to think of it, your formula in G will always give either TRUE or FALSE, so the formula in H can be further simplified to:

=IF(G2,$A$38,$A$39)

**Mrsm** · 06-06-2007, 12:12 PM

It works, thanks again for your help

**Mrsm** · 06-07-2007, 11:55 AM

Hi,

The information I gave to you in the first forum was incorrect.

I needed the formula to work as follows:

1. If the status is - 'Complete', 'cancelled', 'rejected', 'on-hold' and there is no date in cell F2 - then return 'False'. If there is a date in cell F2 - then return 'True'. However,

2. If the status is - 'Approved', 'submitted for approval', 'In dev','Appfor IA' and there is no date in cell F2 - then return no value (a blank cell). If there is a date - return 'True'

Is this possible?? Please tell me it is.

THANK YOU