Testing for the presence of either a dollar value or a formula in Excel
I have a column in which each cell has the following formula (adjusted of course for line number, etc.)....
=IF(J21="","",IF(K19<>0,SU
The cell has a calculated or entered value. so, the operator will, more often than not, enter a dollar amount in the cell. The problem is when they change their mind and remove their entry, the resident formula is gone.
I wrote the following formula in a non-printing area to test for the contents of the cell: =IF(K20="","K20 is Empty","K20 is OK") but for some reason it only works if there is a dollar value present. The formula is not able to detect the presence of the resident formula. What am I doing wrong?
=IF(J21="","",IF(K19<>0,SU
The cell has a calculated or entered value. so, the operator will, more often than not, enter a dollar amount in the cell. The problem is when they change their mind and remove their entry, the resident formula is gone.
I wrote the following formula in a non-printing area to test for the contents of the cell: =IF(K20="","K20 is Empty","K20 is OK") but for some reason it only works if there is a dollar value present. The formula is not able to detect the presence of the resident formula. What am I doing wrong?
If I have understood your requirement correctly, this can only achieved by the VBA code.
Please confirm if I have understood your requirement correctly.
Lets assume you have a formula in A2 and a user is interested to overwrite the formula cell with manually input a value in A2, as a result the formula would be replaced with the manual input in that cell and as a result the formula from that cell will be disappeared. Now you want that if user changes his mind and want the formula back, user in this case has no option to place the formula back into that cell again. Right?
If this is the requirement, please find the attached workbook, where I have placed the formula in the range A2:A50 and then entered manual inputs in some of the formula cells and as a result, the formula is gone from those cells. If you want the formulas in those cells again, just delete the cell content from those cells, and your original formula will be back in those cells.
This is an example to show that how this can be achieved, your formula cells and the formula may differ from your actual formula cells and actual formula. This is just to demonstrate you the process.
This is a macro workbook, so after downloading it, don't forget to enable the contents and editing if prompted after opening the file first time to test it.
Original-Cell-Formula.xlsm
Please confirm if I have understood your requirement correctly.
Lets assume you have a formula in A2 and a user is interested to overwrite the formula cell with manually input a value in A2, as a result the formula would be replaced with the manual input in that cell and as a result the formula from that cell will be disappeared. Now you want that if user changes his mind and want the formula back, user in this case has no option to place the formula back into that cell again. Right?
If this is the requirement, please find the attached workbook, where I have placed the formula in the range A2:A50 and then entered manual inputs in some of the formula cells and as a result, the formula is gone from those cells. If you want the formulas in those cells again, just delete the cell content from those cells, and your original formula will be back in those cells.
This is an example to show that how this can be achieved, your formula cells and the formula may differ from your actual formula cells and actual formula. This is just to demonstrate you the process.
This is a macro workbook, so after downloading it, don't forget to enable the contents and editing if prompted after opening the file first time to test it.
Original-Cell-Formula.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Saurabh, I checked, it was set to automatic so that was not the problem.
sktneer, I think you were trying to write a VBA rountine that would automatically restore the formula. Not what I was looking for because VBA continues to be elusive to me. I could not even figure out how test your solution.
Katie, your solution worked perfectly. Thanks..
sktneer, I think you were trying to write a VBA rountine that would automatically restore the formula. Not what I was looking for because VBA continues to be elusive to me. I could not even figure out how test your solution.
Katie, your solution worked perfectly. Thanks..
No problem Bill. Glad your issue is resolved.
Since you have accepted the Katie's solution, now its clear to me that what you were trying to achieve.
Probably you were interested in knowing whether a cell is completely empty i.e. without any content in it or cell is blank returned by the formula.
I thought a step ahead so if a user deletes the manually entered value which overwrote the formula cell, I tried to place the original formula in its place.
Cheers!
Since you have accepted the Katie's solution, now its clear to me that what you were trying to achieve.
Probably you were interested in knowing whether a cell is completely empty i.e. without any content in it or cell is blank returned by the formula.
I thought a step ahead so if a user deletes the manually entered value which overwrote the formula cell, I tried to place the original formula in its place.
Cheers!
=IF(K20="","K20 is Empty","K20 is OK")
Can you go to formulas-->Calculation option-->Automatic
If it's manual then your formula won't get updated so change it to automatic.
Saurabh...