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?

Please Login or Register  to view this content.

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

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

Try this amendment to my original formula:

Please Login or Register  to view this content.

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

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

Apologies NBVC, I had the status order incorrect - it does work

You are very helpful and I really appreciate how quickly you have come back to me.

Could you please help with my second If statement - it won't work now!!

Many thanks.

**NBVC** · 06-08-2007, 07:46 AM

Originally Posted by Mrsm

Apologies NBVC, I had the status order incorrect - it does work

You are very helpful and I really appreciate how quickly you have come back to me.

You're welcome.

Could you please help with my second If statement - it won't work now!!

Many thanks.

Not sure which one you are talking about?

You mean this one?:
=IF(G2=TRUE,$A$38,IF(G2=FALSE,$A$39,""))

This one should work with True, False or blank.

What's not working about it?