I'm creating a drawing register for a project i'm working on. An
example of the existing number system is BMI8813A where BM is the Site
code, I is a drawing type , 8 is a drawing identifier, 81 is an
equipment identifier and the 3A i'm still trying to figure out.

There are 63 different drawing types and approx. 30 different drawing
identifiers.

I have a list of drawing types with the 2 letter code and the drawing
description. I would like the register to have a pull down box that
displays the drawing type description but when selected adds the 2
letter code in the spreadsheet.

I'd prefer to stay away from VBA and combo boxes to make it easier for
the users filling in the register.

I'm not sure that the data validation is the correct way to go.

Thanks for any help

B

you can have your tables filed in easy enough,but what do you want the drop
downs to do??Do you want the register to eventually "cull" the drawings as
the drop downs are used,but no drop downs used during the input???

Your numbers dont make sense. How you can have a single letter identifying
63 types or a single digit identyfying 30 different types?

sounds interesting!I have comboboxes from the forms toolbars that repopulate
other comboboxes or option buttons that do the same thing.Could the last two
numbers be actual sheet numbers within the series??
--
paul
remove nospam for email addy!



"Boab1965" wrote:

I'm creating a drawing register for a project i'm working on. An
example of the existing number system is BMI8813A where BM is the Site
code, I is a drawing type , 8 is a drawing identifier, 81 is an
equipment identifier and the 3A i'm still trying to figure out.

There are 63 different drawing types and approx. 30 different drawing
identifiers.

I have a list of drawing types with the 2 letter code and the drawing
description. I would like the register to have a pull down box that
displays the drawing type description but when selected adds the 2
letter code in the spreadsheet.

I'd prefer to stay away from VBA and combo boxes to make it easier for
the users filling in the register.

I'm not sure that the data validation is the correct way to go.

Thanks for any help

B

The idea was to allow the designers to populate the drawing register as
and when they needed a new drawing.

I've used the data validation list to esure that the user selects the
correct option but I don't want the users to have to try and remember
the drawing code i.e E, ED etc etc. I would prefer to have the users
select a description and the cell display the code letter.

"Your numbers dont make sense. How you can have a single letter
identifying
63 types or a single digit identyfying 30 different types?" There are
63 different drawing types e.g. E - Electrical, ED - Electrical
Demolition etc etc. and we have 30 identifiers. So and electrical block
diagram would be BME02 and the rest of the drawing number.

I had AutoCAD VBA form populate database fields and the combo-box would
display the text and when it was selected I would add the ID field
code. I thought something like that but simpler.

The spreadsheet would expand as the drawings increased and would have
to be handed over to the client at the end of the project. Again I
would like to be as simple as possible when handed over.

Thanks

B

PS how to you change your email addy to stop it getting spammed. New
tot his newsgroup stuff.

It sounds like a few data|Validation cells would work ok.

Say A1 contains a cell that's using a validation list on sheet2 (A1:A63). Put
the short code in B1:B63 of sheet2.

Then you could use a formula to get the short code back based on the long
description in A1:

=vlookup(a1,sheet2!a1:b63,2,false)
or
=if(a1="","__",vlookup(a1,sheet2!a1:b63,2,false)

If you've never used Data|Validation, take a look at Debra Dalgleish's site:
http://www.contextures.com/xlDataVal02.html

If you've never used =vlookup(), visit this page on Debra's site:
http://www.contextures.com/xlFunctions02.html

You could set up similar cells (and ranges) and build a giant formula that
concatenates the returned values into one cell:

=if(a1="","__",vlookup(a1,sheet2!a1:b63,2,false)
&if(b1="","_",vlookup(b1,sheet2!c1:d??,2,false)
&if(c1="","___",vlookup(c1,sheet2!e1:f??,2,fals e)
....and so forth...

Or just use a cell for each =vlookup() and build the string with those:

=a2&b2&c2&...





Boab1965 wrote:

I'm creating a drawing register for a project i'm working on. An
example of the existing number system is BMI8813A where BM is the Site
code, I is a drawing type , 8 is a drawing identifier, 81 is an
equipment identifier and the 3A i'm still trying to figure out.

There are 63 different drawing types and approx. 30 different drawing
identifiers.

I have a list of drawing types with the 2 letter code and the drawing
description. I would like the register to have a pull down box that
displays the drawing type description but when selected adds the 2
letter code in the spreadsheet.

I'd prefer to stay away from VBA and combo boxes to make it easier for
the users filling in the register.

I'm not sure that the data validation is the correct way to go.

Thanks for any help

B

--

Dave Peterson