Can I increase the amount of nested IFs I'm using, or use an alternate way?

[Jediantman]

Hi everyone,


So I'm facing a dilemma. I use a spreadsheet to work out publication dates for reports and it's all nice and easy because it flows very nicely from one cell to the next and works without a problem. That is until holiday periods need to be calculated. Previously I have gotten around the problem by naming ranges, using a VLOOKUP to then have a nested IF argument work out the relevant dates. The problem I now face is I have 15 different date ranges (although there are common dates) and so my nested IF runs out of arguments. I've included the first part of of my formula below, and as I said its been working fine, but now I don't really know which way to turn. I can't really provide a workbook either because of data security so I was just wondering if anyone could suggest another way of attacking the problem?


=IF(AN23="Group1Hols",IF(OR(ISBLANK(L23),L23=""),"",WORKDAY(AL23,1,Group1Hols)),

IF(AN23="Group2Hols",IF(OR(ISBLANK(L23),L23=""),"",WORKDAY(AL23,1,Group2Hols)),IF(etc.etc.

 

[NARAYANK991]

Hi ,


I don't understand the necessity for :


OR(ISBLANK(L23),L23="")


The formula L23="" will return TRUE whether L23 contains "" as a result of a formula , or it is blank without any data. The ISBLANK function will return TRUE only if L23 is truly blank , without any data. Thus , having ISBLANK alone will not work for both situations , but having L23="" alone , will.


However , that is just BTW.


The entire construct can be shortened to :


=IF(L23="","",WORKDAY(AL23,1,INDIRECT(AN23)))


where AN23 will contain a text string such as "Group1Hols" , and where Group1Hols , Group2Hols ,... are named ranges.


Narayan

 

[Jediantman]

Thanks Narayan, the OR(ISBLANK) is just left over from the original person who created the spreadsheet and so I've never fully understood it's use, I just have left it there to save messing around further. I appreciate your comment but the main issue I have is around the need for me to check the truth of my different groups against the arguments, which as I said now stands at 15 variants.