Conditional formatting formula that uses VLookup, based on content of another cell

[Fred]

I have a software order sheet that has, in column B, a validation list,
comprising 2 entries, a blank entry and a specific software product
(Product X). That way the users can enter their own software product or
select Product X.

Column E (the monthly rate cell) has been left blank for the user to
enter their own rate, but I am trying to get the Conditional Formatting
to populate the cell with a value if Product X has been selected in
column B.

I have entered the following formula in the Conditional formatting but
get nothing in the monthly rate cell when Product X is selected.

=IF(B8="Product X",VLOOKUP(B8,Software_Rate,2,False),0)

I have tried out the formula "stand-alone" and it works perfectly

I am using the VLookup as the software selection list is certain to
grow.

Can anyone point me in the right direction please.

Regards
Fred

 

[Dave Peterson]

Conditional formatting can't return a value to the cell.

It's used to make the cell look pretty--colors/fonts/borders/fills.

 

[Fred]

Dave/Miguel,

Thanks for pointing that one out. Shame really, it would have been an
easy solution to my problem.

Any ideas how I can get the rate to appear in the cell(s) ? I want to
avoid putting a formula in because, as soon as a rate is typed in (for
non-Product X software), the formula will be wiped out. If the user
subsequently changes his mind, there will be no formula present if
Product X is then selected.

Thanks again
Fred

 

[Dave Peterson]

I use 3 cells (per row).

One is for the tabled value (=vlookup(), one is for the user override, and one
is the one that is really used:

Say D:F,
In D2 (from the table):
=if(a2="","",vlookup(a2,sheet2!a:e,2,false))

In E2 (from the user):
(just leave it empty

In F2 (decide which should be used):
=if(e2<>"",e2,d2)
(if the user fills in column E, use it. Else use what's in column D.

And make sure all subsequent formulas point at F2.