Expert Needed to help with complex formula please!

[sam.tidswell-norrish]

I have a worksheet with 30 columns across. In column A I want either a tick or a cross in each box to indicate whether all the columns have been filled in between columns B and AT.

Ideally it would be a tick if there are 4 or less blank columns on each row, a red cross if there are between 4-20 blank columns on each row and another symbol if there are 20+ blank fields.

Currently I am using this formula:

=LOOKUP(COLUMNS(B1:AT1)-COUNTA(B1:AT1),{0,1,25;"a","r","x"})

with webdings to illustrate the crosses/ticks etc.

Thanks

 

[Ruddles]

So... are you saying this formula doesn't do what you want it to?

Which version of Office are you using? 2007 onwards has some nice icon sets which you can display using conditional formatting.

 

[sam.tidswell-norrish]

2003 version. Webdings works fine, it's just the part of the formula selecting the 0-4 blanks as okay, the 4-20 as not okay, and the 20+ as not applicable (because the row probably hasn't been filled in yet)

 

[Ruddles]

I think your only typo is you put 0,1,25 when you should have put 0,4,25.

You might also want to check this: you wrote "a tick if there are 4 or less blank columns on each row, a red cross if there are between 4-20 blank columns". Does 4 need a tick or a red cross? You included it in both conditions.