I have a vlookup formula in cell G39, which looks up a value on another worksheet, =IF(ISNA(VLOOKUP($G$29,B620_CPS,6,)),"",VLOOKUP($G$29,B620_CPS,6,)). Then in cell G27 I have a word which I want to turn red if this condition is met, =G39>"1999". But it does not work. Any ideas how to make this work.
Try =G39>1999
this works OK if G39 is a number. Have to check what happens if it's text.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
What's in G39?
Specifically is it a text or number. Test with =ISNUMBER(G39)
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
If it's text, it doesn't work - so you'd need to ensure that it returns the number as a number... add zero to the output of the lookup..
Thanks Glenn and Richard for the replies! The cell is formatted as "general" and returns a year. I tried it without quotes and that seems to be the answer. Thanks!
Well, I thought it worked!Tried it again and it didn't, but I found this formula in another thread, tried it and seems to work. =(VALUE(G39)>1999)
That's was I was getting at in Post 4. If it's text that looks like a number, you need to make excel think its a number. You can do that, for example< by adding 0 to the output of the VLOOKUP (changing it in G39 from text to number). The VALUE function does the same, but in the conditional formatting equation. Different route to the same result.
Thanks Glenn. Odd though as the format of cell G39 is general and so is the source data. I am not clear how to add this zero to =IF(ISNA(VLOOKUP($G$29,B620_CPS,6,)),"",VLOOKUP($G$29,B620_CPS,6,)) or to what? Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks