Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

How to make auto populated cells change accordingly when I select from a drop-down menu?

Copper Contributor

Hi, 

 

I have a template I am working on with many sheets. I am working on tweaking a dashboard sheet "Contacts Tracker Dashboard" based off data in another sheet "Contacts Tracker Database".  I have set up the dashboard so that when I select a specific "contact" the data I want to highlight appears accordingly. The data is only editable on the original sheet "Contacts Tracker Database" and the "Contacts Tracker Dashboard" only displays the data when selecting a specific "contact" from the drop down list.

However, I am trying to add a table ("Communications Log" ) on the same sheet as the "Contacts Tracker Dashboard" that will ideally be interactive. There are 3 columns on this "Communications Log" table that I want to directly be able to add/edit data onto; however this data should only show when the correct "contact" is selected from the previously mentioned drop-down list. Is this possible?? I have tried many different functions and formulas and I can't seem to make it run the way I envision. Please let me know if I need to further explain anything. Any help would be greatly appreciated! 

 

Thanks so much y'all!! 

5 Replies
Hi ,

If you don't mind could you please share sample data .

@Vinod2406 

 

Hi thanks so much for taking a look at this. 

Yes. For some reason it wouldn't let me attach the excel so I tried to take some pictures.

 

Please let me know if this makes sense or needs further explaining. 

For example, when I choose John Doe from the drop-down list. I get the test data auto populated accordingly. 

e3.png

 

However, next to this Ideally I would like to have an interactive table. So when John Doe is selected from the above drop-down list, he appears here like so. The email may auto populate as well as shown here. However, I would like to be able to then edit/add dates (such as the test ones here) that will only show when "john doe" is selected. So if "jane doe" was selected next, then this table would only reflect the related data. For example the test dates I have in here now stay the same no matter who is selected. Additionally, I would like to do this for "employee" so it can easily be chosen (from that related drop-down menu) and then stay there for that one "contact" such as john doe and then change when I select a new contact. and same for "message", if i were to add texts there too. 

e 2.png

 

Hope this helps! :) thanks again! 

@JaneeCam 

Hi,

Thanks for the data.

The dropdown can be placed using data --> data validation --> allow list

Other details can be mapped using Lookup function.

Sample solution attaching FYR

@Vinod2406

 

So sorry for the delayed response! Thanks so much for the advice! So, it seems that there has to be a source data table that can be edited and the solution dashboard has to be linked and won't be editable. The XLOOKUP formula also seems to produce similarly to the QUERY formulas I was using before for that kind of outputs. Do you recommend one over the over? 

In all it was super helpful! Thanks so so much!

@JaneeCam Yes, You can get same result from xlookup too. 

 

You're welcome. To help people who Search you can click on Mark as solution at the bottom of the post (the one with the solution you adopted)