Data Validation List Using Index & Match Formula

validation.xlsx

Hi,

Attached the solution for your question.

Thanks & Warm Regards,

Prasad Avasare

Hello Rajeev,

Did you checked my attachment? The list is already in Sheet1 and DataValidation in A2 and F2 cell in Sheet2. You can copy down A2 and F2 cell as many range you want.

Solve.zip
Here you go!!!!!!!!!!!!!!!!
I will explain later in next post..... Bit in hurry!!!!

Thanks & Warm Regards,

Prasad Avasare

Hi Rajeev,

Here is the brief of solution....I hope you will enjoy following the steps.
To resolve your query I have done a small change to your WO No data in Master sheet (See AK Column). I just replaced "/" to "_". This is because I had to name Each unique WO No through Name Manager. Name Manager can never accept special charaters except "_". So your WO No. Would be for e.g. LE100479_E2 instead of LE100479/E2. Not sure if you are ok with this small change. Below are the steps:
1) In AK column replace ""/"" to ""_"". AK14=IFERROR(REPLACE(C14,FIND(""/"",C14),1,""_""),C14) Drag it down till end data.
2) In AL14 insert array formula in AL14 and hit Ctrl+Shift+Enter =IFERROR(INDEX('WO AMd.Detail'!$M$2:$M$1500,SMALL(IF('WO AMd.Detail'!$A$2:$A$1500=$C14,ROW('WO AMd.Detail'!$A$2:$A$1500)-ROW('WO AMd.Detail'!$A$2)+1),COLUMNS($AL$14:AL$14))),"""")
3) Drag this over to column CZ14 and again drag down till the last row of WO Master data. This will give you result of all the Item codes column wise in same row. Your data in WO.AMd.Details is till 765th row but the range I have taken it till 1500 so that even if you add the additional data in WO.AMd.Detail will auto populate the Item codes in Work Order Master sheet.
4) Select AK14+Shift+AL14 and hit Ctrl+Shift+Down Arrow Key then hit Ctrl+Shift+F3 function Key will populate Create Names from Selection dialog Box, select Left Column check box only and click OK.
5) in DA14 insert formula =AK14&""Range"" and drag it down till Last data row
6)Select AL14+Shift+Right arrow key and hit Ctrl+Shift+Down Arrow Key then hit Ctrl+Shift+F3 function Key will populate Create Names from Selection dialog Box, select Right Column check box only and click OK.
7) Select AK14+ Ctrl+Shift+Down Arrow Key then name this range NewWOList.
8) In B9 of Cummeas sheet add Data Validation List =NewWOList
9) In E9 of Cummeas sheet add Data Validation List =OFFSET(INDIRECT($B9),,,,COUNTA(INDIRECT($B9&""Range""))-COUNTBLANK(INDIRECT($B9&""Range"")))
Follow the steps exactly I have mentioned above and you will get your result.......Let me know if you have any questions."

Thanks & Warm Regards,
Prasad Avasare.

Hi Rajeev,

Can you please mark this as solved if you are satisfied.

Thanks & Warm Regards,
Prasad Avasare.