Populate multiple cells from a Data Validation list.

Question

I am trying to populate multiple cells using a Data Validation list which looks to a table in the same workbook.

I have a list of about 40 sample sites for collecting water samples and there are 4 different people who look after different sites. I have created a table with unique site numbers in column1 and in column2 the names of the 4 different people who are the samplers for their respective sample sites. I have created a Data Validation list using the names "Sampler A", "Sampler B", "Sampler C" and "Sampler D" in cell A1.

What I would like to make happen is from cell A3 down to say A20 all the cells populated with the unique site numbers from my table that match to the sampler I choose from the Data Validation list. For example, when I choose "Sampler A" in the Data Validation list I want the cells below to populate with "SITE3", "SITE7", "SITE8", "SITE9", "SITE15", "SITE16", "SITE22"....... ect. Then choose "Sampler B" and the populated cells change to all the sites that match to the same row in my table to "Sampler B", for example "SITE1", "SITE2", "SITE23", "SITE24" and "SITE25".

Thank you,

Tim

Answer 1

Hi TimothyVale,

You can use the below mentioned formula to get the desired result.. It is an array formula. After typing the formula in the required cell, you have to press Shift+Ctrl+Enter for getting the result..

=INDEX($A$12:$B$21,MATCH(0,COUNTIF($E$12:$E12,$A$12:$A$21)+($B$12:$B$21<>$E$12),0),1)

(here data is in A12 to B12) See the below pic for more clarity..

Answer 2

Hi Tim,

It is working fine for me.. Can you share me the Excel file, so that I can check the same and get back to you..

Regards,

BKG.

Answer 3

Hello Tim,

Have you tried method by BKG_MS and if so, does it suit your purpose?

I have uploaded a modified workbook with my solution to the following link on OneDrive.

https://1drv.ms/u/s!ArAXPS2RpafChkwvSwhewFObaapA

It is a Macro (VBA) solution. When the Data Validation DropDown value is changed, the relevant data is populated into the cells below. Previous data in the cells below is cleared before populating with new data.

Not sure what your expertise is with VBA code but to see the code, Right click the worksheet tab name and select View code. You can close the VBA editor with the cross very top right of the VBA editor window.

If you copy the code to another workbook then the lookup table must be a table as per your example or the code will fail.

To copy to another workbook,

• Open the VBA editor as per above
• Select and copy the code
• Open the VBA editor in the new workbook as per above
• Paste the code to the new workbook
• Note where I have made comments that you will need to edit your code with the Data Validation DropDown address, worksheet name for the lookup table and the lookup table name.

The workbook must be saved as Macro Enabled and you will need to have Macros enabled in your Excel. (See Help for how to do this and the Option to "Disable all macros with notification" should be satisfactory).

Feel free to get back to me if you have further questions on this and especially if you need to expand because I believe that the example you gave me is only a simple version extracted from your overall project. The code can be expanded to fill on additional data if required.

Answer 4

I am assuming that somewhere in your workbook that you have a table that associates your Sampler A and Sampler B etc to the sites that belong to them.

I am thinking that Vlookup and/or Hlookup can probably be used. However, how that table is set up is critical to how the population of the cells will be done. Can you upload a copy of the workbook with this information and where you want the data populated after selecting the Sampler, indicate where and what data is to be populated.

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Select "Get a Link" from the popup menu.
10. Click in the field displaying the link and it should highlight and then copy and Paste into your reply on this forum. (I suggest that you avoid the "Copy" button because it introduces additional steps that are not required.)