• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Stacking named ranges

Keith2021

New Member
Good day everyone.

My question is how can I importrange from one sheet to another while expanding and stacking the result (imported range) according to items belonging in each named range.

I have a workbook in which I record my client's orders for chocolate hampers. Each hamper has specific items and for each hamper, there is a named range of the items it contains; these names ranges are in the worksheet titled (Hamper contents list).

In a separate sheet, titled (Client purchases), I record my client's name and his/her hamper of choice and quantity.

The focus of my question is the last worksheet I'll mention, the (Item master list). In this sheet ill like to bring the records from the Client purchases sheet but have the list automatically expanded as the client purchases list is updated.

The result should be the same with the client purchases list except this time instead of just writing the hamper option against the client name, all the contents of the client's hampers are listed so that we can tell which items each client is purchasing.


Below are 2 pictures showing Point A - where I am starting from

76246

and Point B - an example of what I want to achieve.

76245

So for the picture - Point B above, I simply used an indirect function... I don't know how to perpetuate the function along the column to automatically add the next purchased hamper and its list of contents.

Here is an editable link to the Google sheet version of the file:

https://docs.google.com/spreadsheets/d/1waUXTozSpPfoGjv6vvqWRDgjxBeP7I89QMZALWYGt0Q/edit?usp=sharing

I have also attached the spreadsheet file as well.

Many thanks for your help in advance.
 

Attachments

  • Hamper list template.xlsx
    18.1 KB · Views: 2
This can be accomplished with Power Query. Load the Client Table into the PQ Editor. Load the Db into the PQ editor. Do a left outer join.
 

Attachments

  • Hamper list template.xlsx
    18.1 KB · Views: 2
Keith2021
You seems to skipped few basic rules,
Please reread Forum Rules
... and You'll remember soon those sentences about Cross-posting.
Hint: Same kind of rules are everywhere.
 
This can be accomplished with Power Query. Load the Client Table into the PQ Editor. Load the Db into the PQ editor. Do a left outer join.

Thank you so much.

Im however not familiar with the PQ editor but ill surely start learning.
I have somewhere to start from now.

if there are any spreadsheets in which something similar has been done, kindly share so I can see a practical example

Many thanks.
 
Back
Top