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,SUM(K18:K20)*INDEX(AA$11:AA$19,J21,0)))

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?
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I don't see any problem in the formula ...i mean this one...

=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...
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
Katie PierceCommented:
Try instead using this formula:

=IF(ISBLANK(K20)=TRUE,"K20 is Empty","K20 is OK")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
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..
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.