Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting
Hi
I have a spreadsheet which i use to keep a record of stock in a shop and it has two tabs one for stock in store and another for obsolete stock. on sheet one i have a list of product codes and a description and on sheet two there is the same. how can i make the colums on sheet one ie. the product code and description show up red if the product appears on sheet 2? many thanks phill |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting
Hi Phill,
First insert a name, say Obsolete, and define its reference like Sheet2!A:A (say product code is in column A) Select Sheet1 Column A, Conditional formatting/Formula: =NOT(ISERROR(VLOOKUP(A1,obsolete,1,FALSE))) Choose red color, OK Regards, Stefi €˛phill€¯ ezt Ć*rta: Hi I have a spreadsheet which i use to keep a record of stock in a shop and it has two tabs one for stock in store and another for obsolete stock. on sheet one i have a list of product codes and a description and on sheet two there is the same. how can i make the colums on sheet one ie. the product code and description show up red if the product appears on sheet 2? many thanks phill |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting
You use conditional formating, but unfortunately it can not directly access a range on the other sheet, so firstly you have to name this range, it may be better to make it larger than your data currently to allow for expansion you do this by insert name define if you called this range test and on the other sheet were looking at the value in cell a1. Goto conditional formating choose formula is and type =VLOOKUP(A1,test,1,FALSE)0 and format the cell as red This format can be pasted as a format to the other cells where you need to apply the logic Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=563625 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting
"phill" skrev i en meddelelse
... Hi I have a spreadsheet which i use to keep a record of stock in a shop and it has two tabs one for stock in store and another for obsolete stock. on sheet one i have a list of product codes and a description and on sheet two there is the same. how can i make the colums on sheet one ie. the product code and description show up red if the product appears on sheet 2? many thanks phill Hi Phill One way: Assumptions: Product codes in columns A and descriptions in columns B. 1. On sheet 2 select column A (click on the letter "A" on top of the column) 2. Click in the name box (extreme left of the formula bar) and enter a name for the selected range, e.g. "Data" without quotes. 3. On sheet 1 select columns A and B as described. 4. Choose Formats Conditional formatting 5. In "Formula is" enter the formula =COUNTIF(Data,$A1) (Notice that only the column is absolute ($). 6. Choose the wanted format with the button "Formats" and OK. OK. Points 1 and 2 are necessary because a formula in conditional formatting must point to a global name. =COUNTIF(Sheet2!A:A,$A1) won't work because Sheet2!A:A is a local address, not a global one. The same holds, if you have validation and the list is on another sheet. -- Best regards Leo Heuser Followup to newsgroup only please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting a range with conditional formatting | Excel Worksheet Functions | |||
conditional formatting glitches | Excel Discussion (Misc queries) | |||
Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
conditional formatting | Excel Discussion (Misc queries) | |||
cannot use ISEVEN or ISODD functions in Conditional Formatting | Excel Worksheet Functions |