#1
|
|||
|
|||
automatically fill in the details
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 |
#2
|
||||
|
||||
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. |
#3
|
|||
|
|||
I've uploaded a file as an example
|
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
Hello,
There is no easier way |
#6
|
|||
|
|||
It comes out ERROR , Why
|
#7
|
|||
|
|||
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. Last edited by ArviLaanemets; 11-21-2019 at 10:22 AM. |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
Quote:
After that, in Main sheet, select or enter client name anew (You can't enter or select client which hasn't been registered in clients table, and spelling must be exactly same. But beware - you can copy-paste a wrong client name onto main sheet.). |
#10
|
|||
|
|||
I have a second drop down list and i have problem with the space.
For example if client 1 in Cell(A9) then in Cell(B15) i would like to make a second drop down list with the delivery places (Paris,London,Monaco e.t.c.). But when to i'm going to do data validation then i get an error message. As far as I could check, you owe it to the space of the customer's name in the Cell(A9). Ιs there any way to confuse it; Thanks |
#11
|
|||
|
|||
Where you have it? In which table and in which column?. And first of all, in which example file?
Quote:
Quote:
|
#12
|
|||
|
|||
Good morning,
Please note the file I uploaded now. I'm going to make the drop down list and type in the bug I have in it cell B7 , but I get it wrong. Please help me, I don't know what else to try. Thank you |
#13
|
|||
|
|||
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Automatically fill word tables with excel data | Qontrax | Word | 1 | 06-12-2019 12:39 AM |
Automatically fill in names in a weekschedule | Adile | Excel | 4 | 11-16-2016 08:08 AM |
Automatically Fill Cells in Other Worksheets | MatthewR | Excel | 4 | 07-09-2015 07:13 AM |
Word Template: Fill out user E-Mail automatically | MS_Maverick | Office | 0 | 05-09-2014 12:50 AM |
Automatically fill in bcc | prroots | Outlook | 1 | 06-29-2010 10:56 AM |