Code to Description

[quinwyo]

I'm looking for a simple solution to a form that my company is using.
I have a form that I have my foremen fill out. I ask that he input a code for the work that was done and needs to be charged out to our customer. These codes correspond to a description of the work. For example, if his team trenches in x number of 6" pipe, I want him to input the corresponding code for that work.

I want the spreadsheet to automatically produce a corresponding description in one cell when the foremen put in the code in another cell.

For example, when he inputs the code A001 in cell C10 and hits enter, cell B10 automatically populates with the description 6in Trenched / Plow. I have a different tab with all of the codes and descriptions.

I started building a formula in B10 with =IF(C10="A001","6in Trench / Plow",IF(C10="A002","4in Trench / Plow",IF(C10="A003","2in Trench / Plow",IF(C10=...

It was working fine, but I soon realized that it would take a long, long time to do this as there are close to 300 codes. I also am not sure that an individual cell can support that many arguments anyway.

Is there a way that this can be done easily? Or a way that the foreman can just select the code and it auto populates?

Thanks

[Logit]

Look at VLOOKUP. My preference is to use a macro in the Sheet Change event. But others are more inclined to utilize a formula. I find formulas are sometimes confusing for me and difficult to put together. My $.02 cents.

[TMS]

In B10, copied down:

Please Login or Register  to view this content.

[quinwyo]

In B10, copied down:

Please Login or Register  to view this content.

This is working. thank you much Trevor.

The only pain point is that there are some duplicate values in the codes (Row 28, 29, 33, etc). To get around this, the foreman specifies which code with the Letter of the section.
For example; If he needs to use the code for New Services - PE 2" at 0-50ft, he'll put B024 (Section B - Rows 37-53; Row 46). if he needs to use the code for Replacement Main - Bore Rock Adder 6", he'll put D024 (Section D - Rows 84-111; Row 109). When I input either code on the Foreman Work Sheet, it produces the description for B024 regardless.

[TMS]

It's hard to anticipate such a requirement, or to deduce that it might exist. All your codes are numeric in the lookup table, hence the reason for extracting the numeric part.

Please provide another sample file with all the typical variations.

Given that all your previous examples had a letter and three digits, it might make everyone's life simpler if you used codes that reflected the input requirement. That would immediately remove the issue of duplicates and make the formula simpler.

[quinwyo]

It's hard to anticipate such a requirement, or to deduce that it might exist. All your codes are numeric in the lookup table, hence the reason for extracting the numeric part.

Please provide another sample file with all the typical variations.

Given that all your previous examples had a letter and three digits, it might make everyone's life simpler if you used codes that reflected the input requirement. That would immediately remove the issue of duplicates and make the formula simpler.

Actually, I think I solved it. I added the letter to each of the three digit codes and changed the formula to look at five digits (we have AAxxx - QQxxx). That seems to have worked.

Thanks.

[TMS]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[quinwyo]

One (hopefully) last question.
I have created another tab in the workbook for our admin side of things - we don't want our foreman to see the costs, so I'll hide that tab. I used the same formula with a reference to a different column to auto populate the costs and on the Admin Worksheet and also, to save keystrokes, had cells reference cells from the Foreman Worksheet. Unfortunately, it's throwing some undesired [I]#NAs[I] in cells that I'd like to have blank. It's impacting a sum cell that I need. I've attached the updated workbook to show what I'm seeing (I think).

Thank you for all of your help. I will absolutely go in and star the posts.

[TMS]

One way:

Please Login or Register  to view this content.

Thanks for the rep

[quinwyo]

Worked like a charm. Thank you again.

[AliGW]

Where is CheyWy in the world?

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[quinwyo]

Cheyenne Wyoming

[AliGW]

OK - most people won't know that, so please add USA to your location.

It could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

[TMS]

One (hopefully) last question.
I have created another tab in the workbook for our admin side of things - we don't want our foreman to see the costs, so I'll hide that tab. I used the same formula with a reference to a different column to auto populate the costs and on the Admin Worksheet and also, to save keystrokes, had cells reference cells from the Foreman Worksheet. Unfortunately, it's throwing some undesired [I]#NAs[I] in cells that I'd like to have blank. It's impacting a sum cell that I need. I've attached the updated workbook to show what I'm seeing (I think).

Thank you for all of your help. I will absolutely go in and star the posts.

Please be aware that, just because a sheet is hidden, it doesn't mean that anyone (like the Foreman) won't be able to access it and/or be able to access the content. I can't really go into much more detail here, but just be warned.

[quinwyo]

OK - most people won't know that, so please add USA to your location.

It could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

Smart. I will do that.

[quinwyo]

I am aware of that, but thanks for the heads up.

[TMS]

Ok. . . . . . . .