Nested Ifs

**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

**NBVC** · 06-07-2007, 12:18 PM

Try this amendment to my original formula:

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

Note: Double-Check the key words to make sure they match your key words.

**Mrsm** · 06-08-2007, 04:16 AM

HI,

Thanks for this......However, it seems to be working back to front!!

If the status is "Complete", "Cancelled" etc and there is no date it is returning a blank rather than False and If there is a Date it is returning a false rather than a True. AND If the status is "Approved","Submitted for approval" etc and there is no date - it is returning a False rather than a blank and if there is a date it is returning a True - (which is correct). I have tried to rework it but I still can't get it to work out

Any ideas??