Generating a unique process number

[Sawdeeka01]

Hi,

I am working with a large data set and I have two (2) queries I am hoping you could assist me with please.

1): I need to create a unique process number comprised of various cells. I have created a formula based on one (1) cell but need additional identifiers and I keep getting the formula wrong

My current formula is: =CONCATENATE(LEFT(B3,5),"-",COUNTIF($B$2:B3,B3))

What I am hoping to build is a formula with the first 5 characters of cell: B and D and 8 characters from cell H.

2): All of the above fields have very long drop down lists. I am hoping that I can create a formula so that when someone selects a specific title from column B, the only options available in column C have a direct relationship to column B.

I really hope this makes sense to you!

Thank you in advance!

Sawdeeka01

 

[jasonb75]

For the first part, something like

=LEFT(B3,5)&LEFT(D3,5)&LEFT(H3,8)"-"COUNTIFS($B$2:B3,B3,$D$2:D3,D3,$H$2:H3,H3)

For the second part you will need to use the method detailed at the link below,

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

 

[Sawdeeka01]

For the first part, something like

=LEFT(B3,5)&LEFT(D3,5)&LEFT(H3,8)"-"COUNTIFS($B$2:B3,B3,$D$2:D3,D3,$H$2:H3,H3)

For the second part you will need to use the method detailed at the link below,

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

Thank you I did get there in the end!

 

[Sawdeeka01]

Following on from this I need to make some changes and am challenged all over again!

I built the below formula but now need to change it so that it only generates a unique process number if column Q is a Yes (drop down list of Yes or No), can anyone please help me?

=CONCATENATE(LEFT(B7,5)&LEFT(D7,5)&LEFT(H7,8),"-",COUNTIFS($B$6:B7,B7,$D$6:D7,D7,$H$6:H7,H7))

 

[jasonb75]

Perhaps

=IF(Q7="Yes",LEFT(B7,5)&LEFT(D7,5)&LEFT(H7,8)&"-"&COUNTIFS(B$6:B7,B7,$D$6:D7,D7,$H$6:H7,H7,Q$6:Q7,"Yes"),"")