Please see attached example.
What I want is that if A1 is removed, cell F1 will then automatically switch to the value red.
Currently I am copy and pasting F1 as a value before I remove A1 but if there is a way to avoid this that would be great to know.
Not looking for anything Macro related.
Thanks
Last edited by PaddyP; 08-04-2021 at 08:30 AM.
What do you mean by "removed"? Deleting the content of the cell, which (with your current formula) would give you #N/A! error? Or deleting the cell, and maybe the cell next to it, which I was expecting would give a #REF! error but (at least in Excel 365) also gives a #N/A! error.
To be honest, I don't think there is any option. Even with VBA, deleting the entry from the lookup table is likely to be problematic. A worksheet Change Event handler might be able to UNDO the deletion, determine what was previously in the cell with the VLOOKUP formula, convert it to a value, and restore the deletion.
But, if you don't want VBA ...
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Yes I want to delete the content in A1 but instead of it showing #N/A I want to it show RED, and the formula is essentially removed from that cell.
I didn't think it was possible but I thought I would ask. You guys have proved me wrong before!
If it is too complicated I will simply copy and paste F1 before I remove the content in cell A1.
Thanks for the reply.
You're welcome. Thanks for the rep.
You can't convert a formula to a value without VBA (or doing it manually). Sorry
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks