Change a value and color based on time of implementation

lkalis

New Member
Joined
Dec 14, 2005
Messages
4
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Dec 14, 2005
Messages
4
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

Active Member
Joined
Apr 13, 2004
Messages
384

ADVERTISEMENT

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
ABCDE
212/14/2005Rank
3BrandSpeed (Ghz)Date addedYears since addedBy age
4
5IBM1.28/20/20014C
6Dell1.41/26/20023B
7Compaq29/15/20041A
8Sony2.22/18/20050A
9Dell2.43/5/20050A
10Ibm1.65/12/20032B
11Compaq19/12/19996D
12AtariTurtle7/5/198619F
Sheet1
 

lkalis

New Member
Joined
Dec 14, 2005
Messages
4
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

Active Member
Joined
Apr 13, 2004
Messages
384
Cells D5:D12 must be formated as general or number instead of date. I should have mentioned that.
 

lkalis

New Member
Joined
Dec 14, 2005
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,068
Messages
5,570,003
Members
412,304
Latest member
citrus
Top