Excel and null values - guru advice sought

[Gadget]

Any MSExcel gurus?

I'm trying to write a formula that a filter will use, but in order for the filter to work properly I need the cell to have data or be null: not contain an empty string (ie "")

if(Filter=True,"Use This Text",<null>)

Anyone know what I put into the <null> to actually get a null entry?

[CJ]

<sausages>

I couldn't believe it. But it does actually work.

[Yliander]

Originally Posted by Gadget

Any MSExcel gurus?

I'm trying to write a formula that a filter will use, but in order for the filter to work properly I need the cell to have data or be null: not contain an empty string (ie "")

if(Filter=True,"Use This Text",<null>)

Anyone know what I put into the <null> to actually get a null entry?

Hey gadget - being rather handy with Excel would love to help - but would need the question in english rather than geek.

what is the filter to do if a cell = null?

[straycat]

Originally Posted by CJ

<sausages>

I couldn't believe it. But it does actually work.

British sausages, presumably? The Continental ones tend to have more meat content, and are hence a lot greater than null.

[Gadget]

Originally Posted by Yliander

Hey gadget - being rather handy with Excel would love to help - but would need the question in english rather than geek.

what is the filter to do if a cell = null?

I'll try english, but don't hold out for any better explanation.

Private Sub SubFilterList()
    If FilterMode = True Then ShowAllData
    
    Range("FullSheet").AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=Range("FilterListBy")
    
    Range("B11").Select
    
End Sub

...and it works if the data in the named range "FilterListBy" is typed in. But I want to have it entered via a picklist/formula in another cell somewhere, so I have the formula:

If(UseFilter=True,"Filter Text","")

Which inserts a null string if I don't want to filter it... of course none of the data in the list is a null string, so the filter does what it's told and filters out all the records.

(oh and I tried "*" for a wild card, but that dosn't seem to work either)

[geoff332]

Try using two double quotes:
=IF(filter,"stuff","")

just saw your reply... let me figure this out then...

[geoff332]

OK - if you're doing it in VB, why not use a conditional to set the criteria range to null. I have a funny feeling that if you enter a formula in a cell, the cell cannot be null. Well, it's more than a feeling - microsoft actually say it themselves (and it sort of make sense - null means nothing in the cell, a formula is not nothing).

So you have to validate the filter list to make sure you don't have any blank cells before you use it as a filter.

[Gadget]

Solved: it was to do with boolean values rather than null values as i first thought...well, that and it didn't scroll up to the top of the list once it had filtered it, so it looked like there was nothing there

Thanks anyway Geoff.
~G~