Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-20-2019, 02:16 PM
DIMI DIMI is offline automatically fill in the details Windows 7 32bit automatically fill in the details Office 2007
Advanced Beginner
automatically fill in the details
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 11-20-2019, 04:27 PM
Alansidman's Avatar
Alansidman Alansidman is offline automatically fill in the details Windows 10 automatically fill in the details Office 2019
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 11-20-2019, 10:40 PM
DIMI DIMI is offline automatically fill in the details Windows 7 32bit automatically fill in the details Office 2007
Advanced Beginner
automatically fill in the details
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

I've uploaded a file as an example
Attached Files
File Type: xlsx Βιβλίο2.xlsx (12.2 KB, 6 views)
Reply With Quote
  #4  
Old 11-21-2019, 02:10 AM
ArviLaanemets ArviLaanemets is offline automatically fill in the details Windows 8 automatically fill in the details Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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
File Type: xlsx ProductDeliveryExample.xlsx (33.7 KB, 7 views)
Reply With Quote
  #5  
Old 11-21-2019, 03:08 AM
DIMI DIMI is offline automatically fill in the details Windows 7 32bit automatically fill in the details Office 2007
Advanced Beginner
automatically fill in the details
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

Hello,
There is no easier way
Reply With Quote
  #6  
Old 11-21-2019, 06:27 AM
DIMI DIMI is offline automatically fill in the details Windows 7 32bit automatically fill in the details Office 2007
Advanced Beginner
automatically fill in the details
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

It comes out ERROR , Why
Attached Files
File Type: xlsx EXAMPLE.xlsx (13.2 KB, 9 views)
Reply With Quote
  #7  
Old 11-21-2019, 07:07 AM
ArviLaanemets ArviLaanemets is offline automatically fill in the details Windows 8 automatically fill in the details Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by DIMI View Post
It comes out ERROR , Why

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.
Reply With Quote
  #8  
Old 11-21-2019, 07:55 AM
DIMI DIMI is offline automatically fill in the details Windows 7 32bit automatically fill in the details Office 2007
Advanced Beginner
automatically fill in the details
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 11-21-2019, 10:20 AM
ArviLaanemets ArviLaanemets is offline automatically fill in the details Windows 8 automatically fill in the details Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by DIMI View Post
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
In clients table, edit client name in case it is wrong there);
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.).
Reply With Quote
  #10  
Old 11-21-2019, 11:04 AM
DIMI DIMI is offline automatically fill in the details Windows 7 32bit automatically fill in the details Office 2007
Advanced Beginner
automatically fill in the details
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 11-21-2019, 11:49 PM
ArviLaanemets ArviLaanemets is offline automatically fill in the details Windows 8 automatically fill in the details Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by DIMI View Post
I have a second drop down list and i have problem with the space.
Where you have it? In which table and in which column?. And first of all, in which example file?
Quote:
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.).
In none of currently posted 3 example files (2 yours an 1 mine) exist a table where you can select client in cell A9. And only cell B15 where is something is on Report sheet in my example, and it contains validation list for kind and variety! Do you have some new version of workbook? Then post it! And leave the faulty data validation list or formula in place too - so anyone here can have a look at it.
Quote:
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).
What does this error message to say?
Reply With Quote
  #12  
Old 11-22-2019, 12:59 AM
DIMI DIMI is offline automatically fill in the details Windows 7 32bit automatically fill in the details Office 2007
Advanced Beginner
automatically fill in the details
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

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
Attached Files
File Type: xlsx EXAMPLEEE.xlsx (13.4 KB, 8 views)
Reply With Quote
  #13  
Old 11-22-2019, 04:18 AM
ArviLaanemets ArviLaanemets is offline automatically fill in the details Windows 8 automatically fill in the details Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


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 in the details 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 the details Automatically fill in bcc prroots Outlook 1 06-29-2010 10:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft