vlookup & conditional formatting

[Guest]

Hi experts,

I have a vlookup formula that retrieves data from another worksheet, but the
source worksheet has conditional formatting. I'd like to have the lookup
data retain the conditional formatting. Is there any way to do this?

For example, I type in an account number and my lookup formula provides the
full account name. In the source data, though, some of those account names
are in bold italics if the account is in certain status. My formula is
returning the account name correctly but applies the formatting of the
current worksheet.

 

[Biff]

Hi!

You would have to use the same cf on the cell with the
lookup formula. Since it's already being used in the
lookup table you can just copy it to the formula cell.

Biff

 

[Biff]

Hi!

After reading your post again I see that this applies to
different worksheets. To use cf in one sheet based on
conditions in another sheet you would have to use defined
names. So, it probably won't be as easy as just copying
the cf (unless you're already using defined names)!

Biff

 

[Guest]

I'm not using defined names but could. I'm not sure that would solve the
problem, though. Sorry if I just don't understand. The conditional
formatting on the source worksheet is looking at another cell on that
worksheet and applying the bold italics if there is an outstanding matter to
be resolved. The vlookup on the worksheet that pulls from the source data is
simply looking for the account name that applies to the account number when
input.

 

[Biff]

Hi!

OK ...

Assume your lookup formula is in cell A1 of Sheet1.
The lookup table is in Sheet2 and the table values are
conditionally formatted based on cell A1 of sheet2.

To conditionally format Sheet1 A1 based on cell A1 of
Sheet2 you need to NAME cell A1 on Sheet2.

Name Sheet2 A1 =Sh2A1

Select cell Sheet1 A1
Conditional formatting
Formula is: =Sh2A1="outstanding_matter"

OR ....

You can use INDIRECT

Conditional formatting
Formula is: =INDIRECT("Sheet2!A1")="outstanding_matter"

Biff

---Original Message-----

 

[Guest]

Sorry - I don't think I clearly stated the problem. Have tried your
suggestions but this is not working for me. Specifically, my lookup formula
is in column B of sheet 1. I type an account number in cell A8 and the
account name is displayed in cell B8. The account information is in a named
range ("Accounts"=A1:J1000) on sheet 2, and the account names are
conditionally formatted on sheet 2 to appear in bold italics if the cell in
column C reads "issue". I need the account name when displayed on sheet 1 to
be in bold italics if there is an outstanding issue relating to that account