Looping through data validation list to incrementally update model

[eblaser_salessync]

Q: How can I loop through 3 cells that contain data validation lists, in order to update my model?
In order for the model I am working with to update, there are three cells (C5, C6, C7 - that contain dynamic data validation lists linked to 3 different ranges in the sheet) that need to be cycled through in order for new source data to be incrementally added into our reporting. Currently, we are doing this by hand. For example:

C3: Select option 1 in list, wait for the model to update; Select option 2 in list, wait for the model to update; Select option X in list, wait for model to update

C4: (Repeat)

C5: (Repeat)

I am looking to automate this process and do not know where to start. I have looked at dozens of other similar requests (ie loop through list and copy/paste values in another sheet, loop through list and save as pdf, etc) however I am not competent enough to reverse engineer those into a solution for this request.
Thank you!

E

 

[oldbrewer]

are you trying to avoid thousands of cells updating when you select option 1 in list thus stopping you immediately selecting option 2 in list ?

 

[eblaser_salessync]

Sorry, I did not see that you replied but the problem still exists. Let me provide some more context and explain the larger situation

In our full file we capture performance data for clients and represent that data in reports that are comprised of several pages of charts and graphs (the reports live in the same file). Those charts are not updated automatically and require us to click through the various drop down lists to update our reports incrementally.

Example:
We've been tracking Jerry's performance over the last 4 weeks. At the end of week 5 we enter his raw week 5 performance data into Table A. However, our charts only show 4 weeks of available data because their source is Table B. Table A only feeds Jerry's week 5 data to Table B when we select "Jerry" in the data validation list in cell C5. Once Jerry's data transfers from Table A to Table B, it is there permanently, so we can move onto Elaine, Kramer, and George, then eventually the Location's dropdown (C6).

The Goal:
The following dummy file (https://drive.google.com/file/d/0B0zlbgkw1BSlUlpfN1pYOFBGams/view?usp=sharing) is a very very small chip off our full file but includes the basics of our situation and my a basic recorded macro title Refresh that achieves the basics of what I've been trying to find. The "Refresh" recorded macro imitates what we would do by hand. However, it is very inefficient because it does not skip blanks. Primarily are looking for help building out a macro that loops through either a) the names listed in the dropdown lists - or - b) the names in B51:B63 & B66:B71 while ignoring the blank cells. Ideally, the solution would be dynamic enough to house in our Personal macro workbook because we have use for this in about ~10 different models that are identical in structure (same worksheet names, same named ranges, etc) but contain different data sets (ie one may have 9 agents, and 3 locations, another may have 3 agents and 5 locations, etc).


Again, thanks so much! A solution for this problem would be a huge timesaver for us.

 

[oldbrewer]

[TABLE="width: 768"]
<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]week1[/TD]
[TD="width: 64"]week2[/TD]
[TD="width: 64"]week3[/TD]
[TD="width: 64"]week4[/TD]
[TD="width: 64"]week5[/TD]
[TD="width: 64"]week6[/TD]
[TD="width: 64"]week7[/TD]
[TD="width: 64"]week8[/TD]
[TD="width: 64"]week9[/TD]
[TD="width: 64"]week10[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]jerry[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/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][/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][/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][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]here is data being recorded for jerry and fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]let us pretend this is all the data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]do you want to transfer this data to separate sheets?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]is somewhere else copying this data ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]