If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
color coded cells and conditional format in excel 2003
Hi, I work with invoices from my suppliers to make the payments and I put in
excel sheet with invoice number and supplier like A2: supplier 1 - inv. number A3: supplier 1 - inv. number A4: supplier 2 - inv. number A5: supplier 3 - inv. number What I'm trying to made is shade the rows from supplier 1 as white, supplier 2 as gray 25%, supplier 3 as white or any other color. I have many suppliers and vary from month to month. The conditionnal format doesn't find a way to solution. I have tried to put a condition IF(A2A1) gray else white, but in the next row the A3=A2 it will paint as white instead gray. My objective is distinguish the suppliers at a glance. Thanks -- With best regards, Looksmart5000 |
#2
|
|||
|
|||
color coded cells and conditional format in excel 2003
I have always used seperate files for each supplier? In that way, you can do
a proper reconcilliation of each supplier's account, and have as an end result a proper payment advice, which you can mail to the supplier, together with your proof of payment. Putting them all in one file is really complicating the issue, isn't it? -- Hth Kassie Kasselman "looksmart5000" wrote: Hi, I work with invoices from my suppliers to make the payments and I put in excel sheet with invoice number and supplier like A2: supplier 1 - inv. number A3: supplier 1 - inv. number A4: supplier 2 - inv. number A5: supplier 3 - inv. number What I'm trying to made is shade the rows from supplier 1 as white, supplier 2 as gray 25%, supplier 3 as white or any other color. I have many suppliers and vary from month to month. The conditionnal format doesn't find a way to solution. I have tried to put a condition IF(A2A1) gray else white, but in the next row the A3=A2 it will paint as white instead gray. My objective is distinguish the suppliers at a glance. Thanks -- With best regards, Looksmart5000 |
#3
|
|||
|
|||
color coded cells and conditional format in excel 2003
yes, but I want to sort by month because he have a 30 or 60 days contract and
to see better what is the invoices paid or not. And by other side improving my excel skills. -- With best regards, Looksmart5000 "kassie" wrote: I have always used seperate files for each supplier? In that way, you can do a proper reconcilliation of each supplier's account, and have as an end result a proper payment advice, which you can mail to the supplier, together with your proof of payment. Putting them all in one file is really complicating the issue, isn't it? -- Hth Kassie Kasselman "looksmart5000" wrote: Hi, I work with invoices from my suppliers to make the payments and I put in excel sheet with invoice number and supplier like A2: supplier 1 - inv. number A3: supplier 1 - inv. number A4: supplier 2 - inv. number A5: supplier 3 - inv. number What I'm trying to made is shade the rows from supplier 1 as white, supplier 2 as gray 25%, supplier 3 as white or any other color. I have many suppliers and vary from month to month. The conditionnal format doesn't find a way to solution. I have tried to put a condition IF(A2A1) gray else white, but in the next row the A3=A2 it will paint as white instead gray. My objective is distinguish the suppliers at a glance. Thanks -- With best regards, Looksmart5000 |
#4
|
|||
|
|||
color coded cells and conditional format in excel 2003
In Excel you are limited to 3 conditional formats per cell. Therefore, if
you had more than 3 suppliers this would not work for you anyway. However, there are a few other alternatives. 1) Just sort the spreadsheet by supplier so its easier to see the supplier groupings. 2) Use Excels autofilter functionality (menu path Data / Filter / Autofilter) and use the drop down arrows to just view the supplier you want. 3) Another option would be to create another cell that would contain the supplier that you want to view (lets say cell E1). You could put in the conditional formats for all the cells in column A that contain supplier that if the cell in column A equals the cell in E1 to hightlight green. If not highlight grey. Hopefully one of these options will work for you. Thanks, Bill Horton "looksmart5000" wrote: Hi, I work with invoices from my suppliers to make the payments and I put in excel sheet with invoice number and supplier like A2: supplier 1 - inv. number A3: supplier 1 - inv. number A4: supplier 2 - inv. number A5: supplier 3 - inv. number What I'm trying to made is shade the rows from supplier 1 as white, supplier 2 as gray 25%, supplier 3 as white or any other color. I have many suppliers and vary from month to month. The conditionnal format doesn't find a way to solution. I have tried to put a condition IF(A2A1) gray else white, but in the next row the A3=A2 it will paint as white instead gray. My objective is distinguish the suppliers at a glance. Thanks -- With best regards, Looksmart5000 |
#5
|
|||
|
|||
color coded cells and conditional format in excel 2003
It is not yet what I want but I like the third solution. Using VBA may be any
code that makes this. I'll search for it. What I make is manually select rows with the same supplier and shade it. But I think that with third solution and conditional format and the offset function use I find a solution. -- With best regards, Looksmart5000 "William Horton" wrote: In Excel you are limited to 3 conditional formats per cell. Therefore, if you had more than 3 suppliers this would not work for you anyway. However, there are a few other alternatives. 1) Just sort the spreadsheet by supplier so its easier to see the supplier groupings. 2) Use Excels autofilter functionality (menu path Data / Filter / Autofilter) and use the drop down arrows to just view the supplier you want. 3) Another option would be to create another cell that would contain the supplier that you want to view (lets say cell E1). You could put in the conditional formats for all the cells in column A that contain supplier that if the cell in column A equals the cell in E1 to hightlight green. If not highlight grey. Hopefully one of these options will work for you. Thanks, Bill Horton "looksmart5000" wrote: Hi, I work with invoices from my suppliers to make the payments and I put in excel sheet with invoice number and supplier like A2: supplier 1 - inv. number A3: supplier 1 - inv. number A4: supplier 2 - inv. number A5: supplier 3 - inv. number What I'm trying to made is shade the rows from supplier 1 as white, supplier 2 as gray 25%, supplier 3 as white or any other color. I have many suppliers and vary from month to month. The conditionnal format doesn't find a way to solution. I have tried to put a condition IF(A2A1) gray else white, but in the next row the A3=A2 it will paint as white instead gray. My objective is distinguish the suppliers at a glance. Thanks -- With best regards, Looksmart5000 |
Thread Tools | |
Display Modes | |
|
|