Conditional Format using value from another sheet
 

Is there a way (formula) to format (highlight) a cell on one sheet with a
value from another sheet? I tried Conditional Formating but it says you
cannot use a value from another sheet. If I have:
Sheet 1 value to use Sheet 2 cell to color
A A B C D on sheet 2
1 3 7 2 1 3 D1
2 4 9 11 4 8 C2
3 2 2 1 6 5 A3
4 8 7 8 3 9 B4

TIA
Joe

Joe,

One option would be to set up an equivilent range on your first sheet that
refers to the data on your second sheet via formulas. Then, you can use
conditional formatting because all of your data would be on one sheet.

----
Regards,
John Mansfield


"Joe Gieder" wrote:

Is there a way (formula) to format (highlight) a cell on one sheet with a
value from another sheet? I tried Conditional Formating but it says you
cannot use a value from another sheet. If I have:
Sheet 1 value to use Sheet 2 cell to color
A A B C D on sheet 2
1 3 7 2 1 3 D1
2 4 9 11 4 8 C2
3 2 2 1 6 5 A3
4 8 7 8 3 9 B4

TIA
Joe

Joe,

You have to use a workbook name on the other sheet, or use INDIRECT to get
the data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joe Gieder" wrote in message
...
Is there a way (formula) to format (highlight) a cell on one sheet with a
value from another sheet? I tried Conditional Formating but it says you
cannot use a value from another sheet. If I have:
Sheet 1 value to use Sheet 2 cell to color
A A B C D on sheet 2
1 3 7 2 1 3 D1
2 4 9 11 4 8 C2
3 2 2 1 6 5 A3
4 8 7 8 3 9 B4

TIA
Joe

How would I go about that? Are the sheets the same as workbooks? How would I
use INDIRECT withthe example below? If possible could you please help.

Thank you
Joe

"Bob Phillips" wrote:

Joe,

You have to use a workbook name on the other sheet, or use INDIRECT to get
the data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joe Gieder" wrote in message
...
Is there a way (formula) to format (highlight) a cell on one sheet with a
value from another sheet? I tried Conditional Formating but it says you
cannot use a value from another sheet. If I have:
Sheet 1 value to use Sheet 2 cell to color
A A B C D on sheet 2
1 3 7 2 1 3 D1
2 4 9 11 4 8 C2
3 2 2 1 6 5 A3
4 8 7 8 3 9 B4

TIA
Joe

Select A1:D4
Menu FormatConditional Formatting
Change Condition 1 to Formula Is
add a formula of =ISNUMBER(MATCH(A1,INDIRECT("'Sheet1'!$A"&ROW()),0 ))
click format
select the pattern tab
choose a colour
exit

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joe Gieder" wrote in message
...
How would I go about that? Are the sheets the same as workbooks? How would
I
use INDIRECT withthe example below? If possible could you please help.

Thank you
Joe

"Bob Phillips" wrote:

Joe,

You have to use a workbook name on the other sheet, or use INDIRECT to
get
the data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joe Gieder" wrote in message
...
Is there a way (formula) to format (highlight) a cell on one sheet
with a
value from another sheet? I tried Conditional Formating but it says
you
cannot use a value from another sheet. If I have:
Sheet 1 value to use Sheet 2 cell to color
A A B C D on sheet 2
1 3 7 2 1 3 D1
2 4 9 11 4 8 C2
3 2 2 1 6 5 A3
4 8 7 8 3 9 B4

TIA
Joe

If you don't wish to represent the figures of Sheet1 on Sheet2, the following
is a means to do the colouring based on conditional formatting, but it
requires some programming (not sure whether that helps, but if your numbers
come from a different place you could automate):

On Sheet 2 change your cell contents by the following:
For Cell A1 for example, replace the value 7 by:
=IF(Sheet1!$A1=7;7;TEXT(7;"#"))
and proceed similarly with the other numbers.

The you can use the conditional formatting using a formula as follows:
=CELL("type";A1)="v"
to change the background colour or other if you wish.

Note that if you wish to use the cell contents as a value for other
formulas, you need to remultiply by 1 to retransform into a value.

Hope it helps.
Erny


"Joe Gieder" wrote:

Is there a way (formula) to format (highlight) a cell on one sheet with a
value from another sheet? I tried Conditional Formating but it says you
cannot use a value from another sheet. If I have:
Sheet 1 value to use Sheet 2 cell to color
A A B C D on sheet 2
1 3 7 2 1 3 D1
2 4 9 11 4 8 C2
3 2 2 1 6 5 A3
4 8 7 8 3 9 B4

TIA
Joe