How to accomplish auto generate.

[Sithtechnology]

What I am trying to do is to type a site ID on a field, and in next filed auto populate market id.

Example Site ID - IL12345, Market IL

So the first two letters would auto populate market ID.

Any help is appreciated.

Thanks

 

[Youngdand]

Hi,

Do you already have a list of states, and their abbreviations?

if so, put them in to 2 columns with the abbreviation in the first column, and the state in the second. Highlight the 2 columns and name the range by typing states in to the small box next to the formula bar.

then in your main sheet, type the following formula in to the field you want the result in. =vlookup(left(a1,2),states,2,false) where a1 is the cell with your site id in.

Regards,

Dan.

 

[Sithtechnology]

So one column is the Market ID the other is Site ID, will it know what market id to choose by just typing the first two letters of the site ID?
Market ID Examples, AR, CH, ML. Site ID examples AR16789, CH67253, ML93907A

 

[Youngdand]

do you have a list of Market ID's? if so, create 1 column with the market id, and one with the abbreviation eg
[TABLE="width: 500"]
<tbody>[TR]
[TD]IL[/TD]
[TD]Market ID1[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]Market ID2[/TD]
[/TR]
[TR]
[TD]BK[/TD]
[TD]Market ID3[/TD]
[/TR]
[TR]
[TD]DG[/TD]
[TD]Market ID 4[/TD]
[/TR]
</tbody>[/TABLE]

Name that range as per last email, with any name you like eg(tolookup)

Then in the sheet you have the sites ID's in:

[TABLE="width: 500"]
<tbody>[TR]
[TD]IL12345[/TD]
[TD]some info[/TD]
[TD]some info[/TD]
[TD]=vlookup(left(a1,2),tolookup,2,false)[/TD]
[/TR]
[TR]
[TD]AC4567[/TD]
[TD]some info[/TD]
[TD]some info[/TD]
[TD]=vlookup(left(a2,2),tolookup,2,false)[/TD]
[/TR]
[TR]
[TD]BK9876[/TD]
[TD]some info[/TD]
[TD]some info[/TD]
[TD]=vlookup(left(a3,2),tolookup,2,false)[/TD]
[/TR]
[TR]
[TD]DG7548[/TD]
[TD]some info[/TD]
[TD]some info[/TD]
[TD]=vlookup(left(a4,2),tolookup,2,false)[/TD]
[/TR]
</tbody>[/TABLE]

you could prepopulate the formula down, but in its current form would display N/a it there is no value in the site id cell. you can fix this by changing the formula to =ifna(vlookup(left(a1,2),tolookup,2,false),"")