SpreadEveryone: The Fetchland Excel wire

2 lurkers | 101 watchers

run free Oct 2019 9:38am, 15 Oct 2019 3,571 posts	0 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	0 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	0 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	0 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	0 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	0 GlennR Well quite.
run free Oct 2019 3:17pm, 20 Oct 2019 3,576 posts	0 run free Use relationships / PowerPivot - far faster than the functions
logicscience Oct 2019 3:24pm, 20 Oct 2019 10 posts	0 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	0 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	0 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'.