Using Drop Down List Option as Criteria w/o Selection from Drop Down

[blackened66]

Hi everyone,

I've lurked here for a while and have found this forum very helpful but I haven't been able to find a solution to the question I have. I have used the search in both the forum and the advanced Google search on the main page. Into the problem...

I have a file right now that contains a table that is populated based on the region selected from the drop down menu. What I am trying to do is use a function to extract the data I need from each region in this table regardless of what is selected from the drop down menu. The issue is that my formula will only populate the data from the specific region that is selected. So when I filter on Ontario, every single region will display the numbers from Ontario. My current method of obtaining the data is filtering on the region and then copying and pasting as values (which is not optimal since my goal is to automate this process).

Red indicates the drop down.[TABLE="class: outer_border, width: 991"]
<tbody>[TR]
[TD]British Columbia
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]January
[/TD]
[TD]February
[/TD]
[TD]March
[/TD]
[TD]April
[/TD]
[TD]May
[/TD]
[TD]June
[/TD]
[TD]July
[/TD]
[TD]August
[/TD]
[TD]September
[/TD]
[TD]October
[/TD]
[TD]November
[/TD]
[TD]December
[/TD]
[/TR]
[TR]
[TD]Found
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

This is a sample of the table I would like to populate.

[TABLE="class: outer_border, width: 991"]
<tbody>[TR]
[TD][/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[/TR]
[TR]
[TD]British Columbia[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alberta[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Saskatchewan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Manitoba[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ontario[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="12"></colgroup>[/TABLE]



My current function is just a INDEX(MATCH()) formula. I apologize for the poor tables... I am not certain how to put in better ones. I hope this is enough to go on though.

 

[Smitty]

Welcome to the Board!

Why not base a Pivot Table off of the underlying data?

 

[blackened66]

Unfortunately I cannot edit or alter the information in any way as it's a file that belongs to someone else and it is locked for editing.

 

[Smitty]

If you're copying and pasting from another workbook, then instead of applying the filter and doing it one region at a time, just copy/paste the entire data set, then use a Pivot Table.

 

[blackened66]

The issue with copy and pasting the data set is two fold. The first is that the table with the drop down menu is riddled with functions that caluclate new values not present in the data set. The second issue is that this is a report that is updated every month which is needed for my monthly report. This is why I'm looking for a way to automate this. I appreciate your help so far though Smitty!