PDA

View Full Version : Excel and null values - guru advice sought



Gadget
19th-December-2007, 05:54 PM
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
19th-December-2007, 05:57 PM
<sausages>

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

Yliander
19th-December-2007, 06:06 PM
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
19th-December-2007, 06:16 PM
<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
19th-December-2007, 06:43 PM
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
19th-December-2007, 07:04 PM
Try using two double quotes:
=IF(filter,"stuff","")

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

geoff332
19th-December-2007, 07:24 PM
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
19th-December-2007, 07:42 PM
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 :blush:

Thanks anyway Geoff.
~G~