Hi ,
It looks like you're using an ad blocker.



The revenue generated from the adverts on the site is a critical part of our funding - and it's because of these ads that I can offer the site for free. But using the site for free AND blocking the ads doesn't feel like a great thing to do, which is why this box is so large and inconvenient. Some sites will completely block your access, but I'm not doing that - I'm appealing to your good nature instead. Did you know that you can allow ads for specific sites, whilst still blocking them on others?

Thanks,
Ian Williams aka Fetch
or for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers
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.
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.
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!
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... ;)
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
Oct 2019
12:34pm, 20 Oct 2019
45,734 posts
  • Quote
  • Pin
GlennR
Well quite.
Oct 2019
3:17pm, 20 Oct 2019
3,576 posts
  • Quote
  • Pin
run free
Use relationships / PowerPivot - far faster than the functions
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?
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)
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'.

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...
  • Show full description...

Related Threads

  • excel
  • support
  • tech
  • work

Report This Content

You can report any content you believe to be unsafe. Please let me know why you believe this content is unsafe by choosing a category below.



Thank you for your report. The content will be assessed as soon as possible.










Back To Top

Tag A User

To tag a user, start typing their name here:
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 114,483 Fetchies!
Already a Fetchie? Sign in here