Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a macro format a hidden sheet? | Excel Discussion (Misc queries) | |||
Conditional format sheet data | Excel Worksheet Functions | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) | |||
make a conditional format the default | Excel Discussion (Misc queries) | |||
Copying a conditional format | Excel Worksheet Functions |