A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Setting up and Configuration
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

color coded cells and conditional format in excel 2003



 
 
Thread Tools Display Modes
  #1  
Old December 14th, 2006, 03:40 PM posted to microsoft.public.excel.setup
looksmart5000
external usenet poster
 
Posts: 4
Default 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  
Old December 14th, 2006, 06:21 PM posted to microsoft.public.excel.setup
kassie
external usenet poster
 
Posts: 76
Default 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  
Old December 14th, 2006, 07:00 PM posted to microsoft.public.excel.setup
looksmart5000
external usenet poster
 
Posts: 4
Default 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  
Old December 14th, 2006, 08:27 PM posted to microsoft.public.excel.setup
William Horton
external usenet poster
 
Posts: 76
Default 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  
Old December 14th, 2006, 09:06 PM posted to microsoft.public.excel.setup
looksmart5000
external usenet poster
 
Posts: 4
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.