Note: per m.a.riosv’s comment below here’s a sample file in Dropbox:
link. There’s a download button at the top of the right-hand sidebar. (I tried the forum file uploader but it didn’t work for me.)
I have checkboxes which are linked to cells named option1, option2, option3.
Reference value (on) for all of them is set to
If I tick the checkboxes, I can do arithmetic with the contents of the named cells, e.g. :
=option1+10 gives result
But if I use the linked cells in a VLOOKUP formula -
=VLOOKUP(option1,lookup,2) - I get #N/A.
If I change the formula to
=VLOOKUP(VALUE(option1),lookup,2), it works perfectly.
This gave me fits till I found the solution, but I still don’t understand it. If the linked cell stores values as text, why can I do arithmetic with them?
Can anyone explain? Thanks!