Formula to blank to formula

[fluffynicesheep]

Hi

I have the following formula in a cell, which works great, and turns what is in the cell blank, if a 0) is subsequently put into cell a23.

However there are times when I then need to then change cell a23 back to a different number, so I then want the formula to kick back in ......

Problem is, the formula has now disappeared!!!

What would be the best way to get around this, and for the formula to start working again?

=IF(ISNUMBER(SEARCH("0)",A23)),"",VLOOKUP(RR23,Table17[#All],5,FALSE))

 

[mole999]

you might look at a work sheet change event monitorin the cell so that if it is made null, code outs the formula back in

 

[fluffynicesheep]

Thanks, would you know how to go about doing this, and how this change event would then happen to all cells in the same column (with the formula bring dragged down .... E.g cell b24 then has the formula referencing row 24 etc) Sorry I'm not an expert with VBA!

 

[mole999]

so what happens if some cells have values

 

[fluffynicesheep]

Currently I have a word in cell rr1 for example.. if cell a1 contains either a 1,2,3 or 4 I then need b1 to contain the results of a vlookup. If cell A1 gets changed to 0) I then need the cell to remain blank but the formula to stay in place. This is due to 2 reasons.

1) we may change the value in cell a1 again in the future, so I need the formula to kick in again at that stage and
2) I have another macro that when I click a button it inserts a full line of data in the next available row in this workbook. The way it does this is by looking at the cell directly above in this sheet and if it contains a value it copies in the new data from the new line, but if it contains a formula it drags it down automatically into the new line.... Hence the need for the macro to know there is a formula in cell b1 (regardless if what is in cell a1)

I have posted info re that other macro in a 2nd post, as it maybe that which needs to be changed?

Anyway hope this gives you a bit of ban understanding of what I need!