SOLVED

Dropdown mirrored in alternate sheet

Copper Contributor

Hello! Is it possible to create a sheet in Excel where once you've selected a category from a dropdown list, it then appears in the similarly labelled sheet? For example, if in the dropdown I selected 'Apples' that data row would then appear in the sheet also labelled 'Apples'?

7 Replies
Can you attach an Excel file with some sample data please?
To attach a file, click "Reply" and then click "Open full text editor".

@Jan Karel Pieterse 

 

File attached, in this case if someone filled out a form and selected their birthday as 'Summer' could that data row then appear in the Data Sheet titled 'Summer' automatically?

 

Thanks for your help!

best response confirmed by AlyshaK (Copper Contributor)
Solution

@AlyshaK Attached an example.

I converted your data to a Table (Home tab, Format as Table button)

Then I clicked Data, From Table to create a query that connects to that same table. Then I right-clicked on the generated query and chose "Reference" and filtered for a season and let the result be placed on the appropriate tab. Repeated that for each season.

After entering a new name, click "Refresh All" on your data tab.

Hi @Jan Karel Pieterse,

 

Thank you so much, this is exactly what I need, I've just tried to do this on my other spreadsheet and can't figure out what you meant by 'let the result be placed on the appropriate tab.' I've tried filtering for the new data but I'm not sure how to select the tab? Document attached. 

Quick question: Wouldn't it suffice to just keep that one sheet and use the filter for column G? To make it easy to filter, you could add a slicer for that column.

@AlyshaK Once in the Query editor window and you've set the filter the way you like, you click on the Home tab of its ribbon and use the "Close & Load" drop-down and click "Close & load to":

2021-07-28_17-27-57.png

 

 

 

 

 

 

 

 

 

Now you are asked where you want to place the data. Choose "Table" and click on the cell where you want that table to appear.

Hi, yes the filter would be a much easier way to do it, which we have used in the past but now for the data that I will need to add in eventually a separate tab seemed like a easier view for everyone sharing the sheet.

Thank you for your help though, I managed to get it all sorted with your instructions!
1 best response

Accepted Solutions
best response confirmed by AlyshaK (Copper Contributor)
Solution

@AlyshaK Attached an example.

I converted your data to a Table (Home tab, Format as Table button)

Then I clicked Data, From Table to create a query that connects to that same table. Then I right-clicked on the generated query and chose "Reference" and filtered for a season and let the result be placed on the appropriate tab. Repeated that for each season.

After entering a new name, click "Refresh All" on your data tab.

View solution in original post