Change a value and color based on time of implementation

[lkalis]

I'm in need of some assistance. I work for a school district, and have been asked to help with computer inventory. I have listed the inventory in an excel spreadsheet (excel 2003). Based on the make, model, and speed of the machines, I have assigned them a letter grade of A, B, C, D, and F. I did this to make it easier for each dept. to better understand when they need to start replacing their machines. I would like the letter grades of A, B, C, & D to automically progress to the next letter after two years of them being added to the excel sheet. Once it reaches the F status, it may remain there until it is replaced. To better help each dept. run down the list, I would also like each letter grade to automatically change to a unique color. (i.e. A = green, B = Blue, C = Orange, D = Black < Bold >, and F = Red < Bold >).

Can something like this be done?


Thank you for your time.


Lou Kalis
School District of Onalaska

 

[RalphA]

It all sounds very doable.

The first part can be done by formulas (in which I could help you) or by VBA code.

The second part can be done by VBA code.

So, it seems to me that VBA code would be the best way to solve the problem. And, since I do not work with VBA code, it will have to be someone else that helps you.

 

[lenze]

Welcome to the board!!

Yes, this can be done, but might I suggest you change your rating system to numbers(1,2,3,4,5). It would simplify things.
Also, is there a date column? This would also make things easier. If these changes are not possible, I believe we can still get you what you want. I, as will others. will think on this.

lenze

 

[lkalis]

The rating system certainly can be changed.

I chose the A, B, C, D, F rating to help identify the machines with certain dept. heads that are teachers. It seems the more simple I make things for them, the better they respond. However, the color scheme will also help them identify what condition the machines are in. I believe that will be sufficient.

Thank you both for responding so quickly. It's nice to see other use their time to help others. I do appreciate your assistance.

Where shall be proceed from here?

Thank you,


Lou Kalis
School District of Onalaska

 

[joelnichols]

Something like this might be close to what you want. The color would have to be a VBA solution, and I am terrible at code, so someone else can pick that up i'm sure.

Book6
	A	B	C	D	E
2			12/14/2005		Rank
3	Brand	Speed (Ghz)	Date added	Years since added	By age
4
5	IBM	1.2	8/20/2001	4	C
6	Dell	1.4	1/26/2002	3	B
7	Compaq	2	9/15/2004	1	A
8	Sony	2.2	2/18/2005	0	A
9	Dell	2.4	3/5/2005	0	A
10	Ibm	1.6	5/12/2003	2	B
11	Compaq	1	9/12/1999	6	D
12	Atari	Turtle	7/5/1986	19	F
Sheet1

 

[lkalis]

Question on the Years Since Added formula

I like your approach to this. Thank you. When I attempt to use the formula in the Years Since Added column (D5,D6, D7), I get a different response that you have. When I enter it exactly at you have, I end up with the following calculations in the first 3 Cells: 1/4/00, 1/3/04, 1/1/00. I have tried it on a PC running Office 2003, and a Mac running Office 2004. ( I did change the Mac to use the 1900 date system, rather than the 1904). It appears that the years are showing up in the month field. I do like how you have it only showing the years since added, rather than days/months. What step do you think I need to add? Thank you so much for your assistance.

Lou Kalis
School District of Onalaska

 

[joelnichols]

Cells D5:D12 must be formated as general or number instead of date. I should have mentioned that.

 

[lkalis]

Thank you !

Joel,

Thank you for the solution. That did the trick. I see what I can figure out on the color, as it helps sort machines, when they get into the hundreds.

You've helped tremendously. Thank you to everyone else who read and contributed to the task.


Sincerely,


Lou Kalis
School District of Onalaska