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

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

Conditional formatting formula that uses VLookup, based on contentof another cell
 

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

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



Fred wrote:

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 formula that uses VLookup, based on content of another cell
 

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

Conditional formatting formula that uses VLookup, based on contentof another cell
 

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.

Fred wrote:

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