dimanche 18 octobre 2020

Excel: Nesting two Nested IF statements inside one IF statement depending on Dropdown Box

I have 2 Nested IF Statements..

=IF($B3=$K$3,DATE(YEAR(D3)+$L$3,MONTH(D3)+$M$3,DAY(D3)),IF($B3=$K$4,DATE(YEAR(D3)+$L$4,MONTH(D3)+$M$4,DAY(D3)),IF($B3=$K$5,DATE(YEAR(D3)+$L$5,MONTH(D3)+$M$5,DAY(D3)),IF($B3=$K$6,DATE(YEAR(D3)+$L$6,MONTH(D3)+$M$6,DAY(D3)),""))))

and

=IF($C3=$O$3,DATE(YEAR(D3)+$P$3,MONTH(D3)+$Q$3,DAY(D3)),IF($C3=$O$4,DATE(YEAR(D3)+$P$4,MONTH(D3)+$Q$4,DAY(D3)),IF($C3=$O$5,DATE(YEAR(D3)+$P$5,MONTH(D3)+$Q$5,DAY(D3)),"")))

which work as required in two separate columns. What I would like is to Nest them again so that depending on the choice made from a Data Validation dropbox (Choice 1 or Choice 2, in the following) it either displays the date calculated from the first Choice, or from the second. This is what I have, but I can't get it to work. I think I need an OR in there, but I don't know where, as there are so many IFs already.

=IF($I$2=”Choice1”,IF($B3=$K$3,DATE(YEAR(D3)+$L$3,MONTH(D3)+$M$3,DAY(D3)),IF($B3=$K$4,DATE(YEAR(D3)+$L$4,MONTH(D3)+$M$4,DAY(D3)),IF($B3=$K$5,DATE(YEAR(D3)+$L$5,MONTH(D3)+$M$5,DAY(D3)),IF($B3=$K$6,DATE(YEAR(D3)+$L$6,MONTH(D3)+$M$6,DAY(D3)))),IF($I$2=”Choice2”,IF($C3=$O$3,DATE(YEAR(D3)+$P$3,MONTH(D3)+$Q$3,DAY(D3)),IF($C3=$O$4,DATE(YEAR(D3)+$P$4,MONTH(D3)+$Q$4,DAY(D3)),IF($C3=$O$5,DATE(YEAR(D3)+$P$5,MONTH(D3)+$Q$5,DAY(D3))),""))))))

If it was just one column of dates I can use this..

=IF($I$2="Choice 1",$E4, IF($I$2="Choice 2",$F4,""))

with a couple of helper columns, but I could have as many as 10, which would require 20 helper columns on a spreadsheet that's already 70 odd columns wide, which I'd rather avoid if possible. I've done this on a test worksheet, rather than mess about with the original for now.

Thanks

Liam

Aucun commentaire:

Enregistrer un commentaire