Hi All. I would like to import uniqe values from a list in workbook (MFL) into another workbook (FieldValues), in a sheet that shall be used for validations. I've been looking at different load scripts but haven't really managed to sort it out.
A button that triggers this macro would do the trick. It shall be done before I hand the workbook to others to fill in the LOV sheet. For next iteration, the sheet will be updated with new values.
These are my requirements:
1. Fetch all unqies values in Entity col from MFL.xlsx, and create dynamic Range ("EntityPick") under Header Entity in DropDownValidations sheet in FieldValues.xlsx. Like shown below
Entity
Address
Account
Contact
Order
... (there are actually around 30 or so entities IRL, so manual work is not an option). I think a filter is the solution, but i don't know.
2. Create Headers on second row from column C, based on the values under Entity (=EntityPick range). Like shown below.
Entity Address Account Contact Order
Address ...
...
(again, IRL it's like 30 entities).
3. Populate these new columns with Field Names corresponding to each Entity from MFL.xlsx. The problem is, we only want to fetch Field Names where Type is LOV and where Actions is Add, Keep, OK or Change. In addition to that, Field Names are unique to Entities. Like shown below.
Entity Address Account Contact Order
Address Address Type Account Type
... Apartment Number ...
... Apartment Type ...
...
IRL, there might be up to 30 Field Names per Entity.
All this imported data, shall then be used for dependant validations in the LOV sheet in FieldValues.xlsx, but that part is covered.
Please find the attached sample workbooks.
I'm thankful for any help i can get.
A button that triggers this macro would do the trick. It shall be done before I hand the workbook to others to fill in the LOV sheet. For next iteration, the sheet will be updated with new values.
These are my requirements:
1. Fetch all unqies values in Entity col from MFL.xlsx, and create dynamic Range ("EntityPick") under Header Entity in DropDownValidations sheet in FieldValues.xlsx. Like shown below
Entity
Address
Account
Contact
Order
... (there are actually around 30 or so entities IRL, so manual work is not an option). I think a filter is the solution, but i don't know.
2. Create Headers on second row from column C, based on the values under Entity (=EntityPick range). Like shown below.
Entity Address Account Contact Order
Address ...
...
(again, IRL it's like 30 entities).
3. Populate these new columns with Field Names corresponding to each Entity from MFL.xlsx. The problem is, we only want to fetch Field Names where Type is LOV and where Actions is Add, Keep, OK or Change. In addition to that, Field Names are unique to Entities. Like shown below.
Entity Address Account Contact Order
Address Address Type Account Type
... Apartment Number ...
... Apartment Type ...
...
IRL, there might be up to 30 Field Names per Entity.
All this imported data, shall then be used for dependant validations in the LOV sheet in FieldValues.xlsx, but that part is covered.
Please find the attached sample workbooks.
I'm thankful for any help i can get.
