Create dropdown list in a cell using data from two different columns

[syp1677]

Dear Gurus,

I have two worksheets in which there is data in Column A. I want to create dropdown list which have consolidated lists of Column A of both the worksheets, excluding blank cells.

I am open to both VBA or data validation.
Is there any way to do the same? Please comment

 

[p45cal]

This should be possible.
Can you attach a workbook? If it's not the actual workbook, make sure it's as close to the real one as possible, making sure the source data columns A, if they are part of a real Excel table, they're also part of a real Excel table. This way we'll not be guessing (wrongly) and you should get a solution that works for you.
FYI, I'd perhaps be looking to use FILTER and VSTACK maybe in a Name which could then be used in Data Validation.

 

[syp1677]

This should be possible.
Can you attach a workbook? If it's not the actual workbook, make sure it's as close to the real one as possible, making sure the source data columns A, if they are part of a real Excel table, they're also part of a real Excel table. This way we'll not be guessing (wrongly) and you should get a solution that works for you.
FYI, I'd perhaps be looking to use FILTER and VSTACK maybe in a Name which could then be used in Data Validation.

Dearp45cal,
Thanks for the response.
PFA... file which is the closest I can make.

Best Regards,
S

 

[p45cal]

In the attached:
Data validation (DV)at cell A3 of the Report sheet
The DV has the formula: =Control!$A$1#
A hidden Control sheet with a formula at cell A1:

=SORT(UNIQUE(VSTACK(FILTER(Database!A2:A1048576,Database!A2:A1048576<>""),FILTER('Database-2'!A2:A1048576,'Database-2'!A2:A1048576<>""))))

Completely separately and nothing to do with the above (it won't work directly in DV) is a lambda formula myDV which you can use on a sheet eg. thus:
=myDV('Database-2'!A2:A100,Database!A2:A100)

 

[syp1677]

In the attached:
Data validation (DV)at cell A3 of the Report sheet
The DV has the formula: =Control!$A$1#
A hidden Control sheet with a formula at cell A1:

=SORT(UNIQUE(VSTACK(FILTER(Database!A2:A1048576,Database!A2:A1048576<>""),FILTER('Database-2'!A2:A1048576,'Database-2'!A2:A1048576<>""))))

Completely separately and nothing to do with the above (it won't work directly in DV) is a lambda formula myDV which you can use on a sheet eg. thus:
=myDV('Database-2'!A2:A100,Database!A2:A100)

View attachment 83636

Thank you p45cal.
I am using Office 2016 version, It does not have unique or vstack function. Is there any other option.

Best Regards,
S

 

[p45cal]

I am using Office 2016 version, It does not have unique or vstack function.

Having stated my intention to use those functions you could have pointed that out to me earlier.

This was more difficult that I thought without the newer functions.
Try the attached, data validation in the green cells. If it's not working properly I'll tweak, if it is I'll explain if you need it.
Allow macros and 'External connections' (which aren't external!)

 

[syp1677]

Having stated my intention to use those functions you could have pointed that out to me earlier.

This was more difficult that I thought without the newer functions.
Try the attached, data validation in the green cells. If it's not working properly I'll tweak, if it is I'll explain if you need it.
Allow macros and 'External connections' (which aren't external!)

My apologies for my ignorance.
You have suggested powerquery, it works for now. I would be thankfull if you can suggest way we can do it by VBA.
Best Regards,
S

 

[p45cal]

As before, see attached. (Some vba was already being used)