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~
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.