How Can I Build A Dynamic List From Multiple Named Ranges

[GEANZ]

Hi Forum,

As per the attached spreadsheet I wish to build one dynamic list of products from multiple named ranges (lists of products in separate columns of various lengths) without blanks or errors.

Perhaps a custom VBA function is needed?

DynamicList.xlsx

Many thanks in advance.

-Ollie

[Pepe Le Mokko]

This formula works with fixed ranges. If you make them dynamic it should do what you want

Please Login or Register  to view this content.

[Marcol]

Do you want dynamic lists for each pump category and one combined list?

If so see this workbook
Add to the sub categories in your sheet "Named Ranges"

The drop-downs in sheet "Data List" will adjust accordingly and, the combined list will automatically grow as you enter any item to any of the other lists.

[kvsrinivasamurthy]

Pl see the attached file.Name ranges are changed as dynamic . you can add or remove the contents.

[abubaniyan]

DEAR kvsrinivasamurthy
I THINK U HAVE NOT READ THE THREAD PROPERLY OR YOU HAVE UPLOADED THE WRONG FILE...

[Marcol]

@ All

The original file posted has null strings, in Sheet "Named_Ranges", Columns A:D, there might be more.

This sheet needs to be cleaned for most "Dynamic Names" formulae to work properly.

[GEANZ]

Hi Marcol, thanks for your reply.

My Pump lists (in the real workbook) are already dynamic. However in my attached workbook they aren't dynamic as they are just an example.

The goal of this thread is to build one combined dynamic list from the named ranges of various lengths.

[GEANZ]

Hi Pepe Le Mokko,

Thanks, with modification this formula does the job.

However I'm sure there must be a tidier way to do this with a custom function in VBA?

I will leave this thread open for a bit longer to see if anyone has a cleaner way.

Thanks -Ollie

[kvsrinivasamurthy]

Pl see the attached file.
Formula used for Four columns of sheet "Named_Ranges".Suitably can be modified.

Please Login or Register  to view this content.

[Pepe Le Mokko]

However I'm sure there must be a tidier way to do this with a custom function in VBA?

There surely is a way to do it with VBA, unfortunately I can't help you there.
I suggest you start a thread in the VBA forum with a reference to this one.

And thanks for the rep