Excel2K - How to set cell to be empty with formula?

[thedaver]

I have a formula in cell F29 that says:
=IF(EntryData!A29<>&quot;&quot;,EntryData!A29,&quot;&quot

It works fine, but I didn't realize that on the FALSE segment that it was literally setting the cell to a _value_ of &quot;&quot;. I had intended for the cell to be considered literally empty so that an isempty() on the cell would result in true.

I can't find how to forcably set a cell to be empty in value, yet have a formula determine its own contents.

 

[xlbo]

Can't be done. Think about it a mo..............
Thought about it..ok then ;-)
How can a cell be emtpy if it has a formula in it ????
Answer. It can't. What you are asking for is physically and logically impossible

The only way round this is either to adjust the formulae that work on these cells to react to &quot;&quot; as well as blank OR to run code on the calculate / change event of the worksheet to physically delete the formulae from cells which return &quot;&quot;
You would then, of course, have to re-insert those formulae to do any further calcs so it's a bit of a no-goer for anything that needs to be updated

I suggest you start using =if(cell_ref<>&quot;&quot; rather than ISBLANK Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[thedaver]

I need the &quot;value&quot; property of the cell to be empty so that I can use the selection mode of Shift-End-DownArrow to highlight a variable sized range.

Please don't confuse the presence of a formula with the value of the cell. The cell can be set to &quot;&quot;, let's set its value to (empty) or (nil) or (null) or something that displays nothing as its value.

 

[xlbo]

Erm - I'm sorry but my point is absolutely valid
In cases where a cell has a value only, the formula property of the cell is equal to the value property of a cell

ISBLANK works on the CONTENTS of a cell. therefore, by default, if it has a formula in, it cannot be empty

You CANNOT set a cell to be NULL or EMPTY or whatever you want to call it, if it has a formula in. It just isn't possible. Excel's object model doesn't function like that.

If you are manually selecting your variable range, you are in trouble but it would be relatively easy to do via code - depending on what you do AFTER selecting this variable range

some more information may well secure a workaround.... Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[SkipVought]

Hi,
Geoff is ABSOLUTELY correct!

What it seems that you want to do can't be done with spreadsheet functions.

What you ought to do is NOT have formulas in &quot;blank&quot; rows.

Instead have the Worksheet_Change event insert formulas in a row at the time that there is data for the formula to work with.

Use your macro recorder to see what happens when a formula is entered. Clean it up, modify it and it can become the means for adding formulas as needed. You can get help here to know how to do that.

BTW, you will need to handle the &quot;change as a result of a change&quot; situation with some kind of a lock-out mechanism.

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

cheers for the backup Skip ( - I'll be looking at your file tonight BTW)

theDaver - it doesn't go down too well when you write patronising comments like &quot;Please don't confuse the presence of a formula with the value of the cell&quot;
Before assuming that I don't know what you are talking about, you should take time to either read my member profile or have a good long look at excel's object model

Remember who's asking the questions and whose trying to give you the answer Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[thedaver]

OK, 2 against 1, I must be wrong. Great alternatives, thanks for the help. (Secretly wishing that Excel did what I wanted...)