for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

run free Oct 2019 9:38am, 15 Oct 2019 3,571 posts	Quote Pin run free Then suggest you create a reference cell e.g say A1 = 2020. And all formulas refer to $A$1.
mattglen_ Oct 2019 12:53am, 18 Oct 2019 74 posts	Quote Pin mattglen_ Is it possible to use conditional formatting on a cell that has the result of a VLOOKUP in it? For eg, I can happily apply conditional formatting to change the cell colour depending on the value, when the value is manually typed in. However when I pull the same value from another workbook using a VLOOKUP, the conditional formatting disappears. I guess this is because the contents of the cell is a formula, not directly a value? Anyone got any ideas?! Hope this makes sense.
mattglen_ Oct 2019 12:55am, 18 Oct 2019 75 posts	Quote Pin mattglen_ Ah! Got it! The conditional formatting rules already applied to the cells were being removed when I pasted the VLOOKUP formula in. Simply rewriting the conditional formatting rules seems to have worked!
GlennR Oct 2019 8:55am, 18 Oct 2019 45,714 posts	Quote Pin GlennR Makes sense. I was about to comment that I couldn’t see what the problem was. On the other hand, the problems with VLOOKUP are manifold...
icenutter Oct 2019 6:56am, 20 Oct 2019 280 posts	Quote Pin icenutter Mattglen: try the index and match combo instead of vlookup, much more Flexible
GlennR Oct 2019 12:34pm, 20 Oct 2019 45,734 posts	Quote Pin GlennR Well quite.
run free Oct 2019 3:17pm, 20 Oct 2019 3,576 posts	Quote Pin run free Use relationships / PowerPivot - far faster than the functions
logicscience Oct 2019 3:24pm, 20 Oct 2019 10 posts	Quote Pin logicscience 318 pages of excel on a running forum, this is exciting what about powerBI?
run free Oct 2019 3:32pm, 20 Oct 2019 3,577 posts	Quote Pin run free ....It's as good as Tableau especially as you can now have it on premises with SQL 2017 (PowerBI report server)
GregP Oct 2019 12:22pm, 22 Oct 2019 115,474 posts	Quote Pin GregP Life didn't ought to be this difficult: =TRIM(CLEAN(SUBSTITUTE(E8,CHAR(160)," "))) Or in other words, TRIM() should do what it says on the tin rather than one needing to 'help'.