Good evening,


I would like to select client 1 or 2 from a list, then fill in each person's address from the client's sheet, postal code, VAT number, discount and payment method.
Then a list in the name of the place of delivery, where I will only take out the delivery places that the particular customer has and automatically fill in the details .
And finally a list in kind according to the products that each customer buys based on their card and then for example for customer 1 we put potatoes, i would like to open another list for us to select the variety according to what is listed on his card and finally after taking into account the type and variety to make a list for me to select the description I need.
Finally, depending on the description, the weight will be returned to me.
Thank you

Attach a sample workbook
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

I've uploaded a file as an example

Attached Files

Βιβλίο2.xlsx (12.2 KB, 8 views)

In added example file where what you want is done without any coding.


I left your original sheets as they were. You can delete them.


Sheets with names starting with "H_" are meant to be hidden.


On other sheets, columns with dark brown headers are meant to be hidden.

On hidden sheets, you must have in Tables enough rows to have full info for any client you have registered. Currently both tables allow maximally 20 entries. To add rows, select any cell in bottom row and drag it down.

Instead of cascading validation lists to select products and varieties, I advice to combine them and have a single validation list - as I did in my example.

Attached Files

ProductDeliveryExample.xlsx (33.7 KB, 9 views)

Hello,
There is no easier way

It comes out ERROR , Why

Attached Files

EXAMPLE.xlsx (13.2 KB, 11 views)

After what you get an error? When I select another client, address and city are updated.


But with your current design, whenever you add a new client, you have to edit on MAIN sheet the validation list for client name, and formulas for client address and city. With my solution all those are dynamic, i.e. you add new client information to clients table, and it's all what is needed to get the info about new client into MAIN sheet.


This dynamic automation goes even further with next steps. In my solution, on hidden sheets lists of addresses and products for current client on report/main sheet are generated automatically. When you change the client in main sheet, those hidden sheets have information only for this client. And validation lists and VLOOKUP formulas for address and product info are referring to those hidden sheets to get info. So whenever you add new clients into clients sheet, new persons into delivery sheet, or new (combinations of) products and/or varieties on sheet with whatever name you use, there is no need to edit any validation lists or formulas. You simply select the value of group key, and you get correct data for all group fields.


Btw., when you select e.g. another client, the person in address group remains old one until you change it (and for almost sure will be a wrong one!). In cell right to address name I added the formula, which displays red "!" whenever there doesn't exist such name for this client - indicating that another name must be selected. Using this formula as model, you can do same for product group too.

Good evening,
I found the mistake, was responsible for the error I had in the client's name (CLIENT 1). How can I fix it, because I have to have the space.
Thank you

What you are trying to do?
VLOOKUP function returns a single value from range. In your case it is string "BHGGG";
Then you add to this string another and you get "BHGGGList"
And then you use INDIRECT to read some value from cell BHGGGList !???


Are you trying to create dynamic Data Validation List for cell B7? There are 3 possible ways to create a Data Validation List:
1. By values - you enter delimited value list into Source field (like "red","blue","green"). (I don't know what you use as list delimiter, for me it is semicolon.);
2. By reference to range of cells. Referred cells MUST BE ON SAME SHEET as cell with Data Validation List defined. The source will be like '= $X$2:$X$12';
3. By reference to Named Range (fixed or dynamic). The range of cells defined as Named Range can be on any sheet of workbook. The source will be like '=YourNamedRange'


As follows, you can create a dynamic Data Validation List only using dynamic Named Ranges.