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.