Nested AND IF problem Syntax and solve errors

[mikecymru]

Hi,

I hope someone can help, I am newbie so my apologies if my thread title is incorrect.
I am creating a simple Gannt chart and am using conditional formatting to display the Gannt bars.
I use the formula

#
=IF(AND(N$9>=$K11, N$9<=$M11),$I11,"")

#
This part works well and populates the cell with the value contained within $I11, allowing me to conditionally format the cell in the colour of my choice. However I would like to add a current date timeline. I can do this seprately by using.

#
=IF(N9=$U$3,"È","")
#

This shows an arrow "wingdings character" when the current week number matches the week header in my gannt chart.

Problem,
What I would like to do is combine the first If statement with the second, so under normal conditions the gannt bar is displayed but when its the current week the arrow/character is displayed.

I am struggling firstly with the syntax and I am uncertain if this is even feasable because i am asking the cell contain to different responses...The arrow/week number is the priority if "true" and then if not the current week number and a task is scheduled for the week display the gannt bar...
I hope this makes sense...?
I can attach my work in progress if it helps..

Thank you to any responses in advance...

Mike

[teylyn]

Hello, or Sut Mae!

attaching a sample workbook would be a good idea.

You can combine several if statements by nesting them, for example

=if(A1=1,"great","not so great")

That's a simple IF() statement. If you want to split the "not so great" part into two, depending on another condition, then you can use

=if(A1=1,"great",if(B1=2,"acceptable","not so great"))

Working with your example, maybe this will do it:

=IF(AND(N$9>=$K11, N$9<=$M11),$I11,IF(N9=$U$3,"È",""))

If that does not do the trick, then please post a sample file, manually fill a few cells with the expected results and explain the logic.

cheers,

[Fotis1991]

Hi Mike and welcome to the Forum.

I think that the joined formula, is logical and maybe works for you. If does not, pls upload a sample workbook.

=IF(AND(N$9>=$K11, N$9<=$M11),$I11,IF(N9=$U$3,"È",""))

Hope to helps you.

[mikecymru]

Hi,

Firstly thankyou very much for your swift responses.
Your suggestions over come the continual syntax errors so that's a good start. but unfotunately it doesnt quite work out as planned.
I have now attached a copy of my work in progress.
I copied the formula to the first few cells of N11 on. It shows the arrow (week indicator) in the cell when the statement is true. But now if the blue gant bar goes through the week then the arrow doesnt show. Also the gannt bar doesnt start in the right place.

To be honest if you can suggest another way of getting a week indicator running vertical down my gannt chart this will also work for me.

Thanks

Mike

[Pete_UK]

If you want the arrow to take priority, then you need to write the IFs the other way round, i.e.:

=IF(N9=$U$3,"È",IF(AND(N$9>=$K11, N$9<=$M11),$I11,""))

The arrow will thus overwrite any bars in your chart for that week, but the bars will be re-instated when the week number moves on.

Hope this helps.

Pete

[mikecymru]

Hi pete,

Thank you very much for your input.

It worked a treat!!

regards

Mike