Excel Macros?

[shiggles]

Hi. I am not a very skilled Excel user, and I am trying to figure out if it is even possible to do what I want to do. I have a spread sheet of Vehicle Inventory for about 500 vehicles. One of my columns lists color codes (ie. 42U, 21C).

Some color codes are the same number but have 2 different names depending on the model in question....

What I want to be able to do is run some kind of macro or formula or find and replace that will replace the color codes with the actual color name, based on the information in another column... For example, if Cell A1=x and A2 = 42U, change it to Green. If A1=y and A2=42U change it to Red, etc. There are about 50 different codes, and I know I would obviously have to spend some time matching the codes with the actual names, but I dont want to have to do it manually every single time I update this (about every 2 days)

Does anyne have a clue what the heck i am talking about, and is there any way I can do this?

Thanks!

[thelamberts]

Hi there and welcome!


I may have bitten off more than I can chew here. This type of thing is alot easier to "show" than explain if you get my drift!

Lookup tables is exactly what you need.

Essentially you build a list of all potential colour codes and their equivalent colour names (your look up table) off screen somewhere.

You then have an an alpha / numeric input area and a "colour name" results
area which has the lookup formula imbeded.

When you input an alpha/num code; the spreadsheet looks at the formulae and goes to the look up table to grab the "colour name"

Essentially the formula in "english" says "take whatever number is in the cell; look it up and place the answer here"

Look for Excel help (on the web or wherever) using the terms "vlookup" or "hlookup"

Good luck and let us know how you make out.


Hope this helps

Regards

Marty

[shiggles]

thanks, it sounds like that will help a lot except for one thing...

there are certain colors that use the same code for different colors... in those cases it would be identified by 2 cells, the code and the model.....

im gonna look into it though, thats the most progress ive made with this in ages...

thanks!

[thelamberts]

Hi there...yer welcome!

To solve your "two cell" issue you can use the "offset" column values in your lookup tables or you can "combine" your two codes into one value (this will keep things simple)

Give it a shot. Believe it or not it is easier than it sounds.....


Good luck


Marty