I have two worksheets, one with charts and one with tables. Both of them
report data on separate worksheets. I use separate worksheets because the
format of the charts and tables interfere with each other if I put them all
on the same worksheet and trying to combine them also makes it difficult for
users to scroll and find what they want.

I used the "Data Validation: List" method to create a drop down menu of
potential geographic areas to be profiled in the charts and tables. Each
chart or table uses Vlookup with the value from the drop down menu list cell
to direct the appropriate values from the other worksheets.

I want users to be able to choose a geographic area from a drop down menu on
EITHER the chart or the table worksheet that will then populate ALL charts
and tables. I don't want to set one as "master" and set the other using an =
cell value because then the user has to jump back and forth, e.g., if the
drop down list is on the chart worksheet, then the user can't change the area
when on the table worksheet without switching to the chart sheet first then
switching back to view the table. I also don't want to have two drop down
choices that are independent because a viewer might think that the chart data
would switch when they changed the area on the table sheet and vice versa.

My only kludgy solution so far is to create a new window and put a dropdown
menu cell on a new worksheet visible in that new window (with scroll bars,
tabs and other features turned off) and then have all the vlookup functions
refer to this value. But playing with two windows and leaving this lookup
worksheet tab visible gets awkward and invites the user to play around with
worksheets that can lead to problems.

I really want a floating window that gives the user a drop down menu that
serves as a source for values in the Vlookup functions that drive the charts
and table values. Either that, or a way to have a source value that can be
set by the drop down menu on whichever worksheet is active, but is propagated
to both the chart and table worksheet simultaneously.

Any ideas on an elegant work around to my current kludgy solution?

I'm not sure how to create a floating box but, what you could do is add a
worksheet to the left of the other two and have it serve as a dashboard.
Your drop down to control the graphs and charts would be there and two
buttons; one to "go to graph" and the other to "go to chart". On the graph
and chart pages, you could then add a "back" button to take the user back to
the "dashboard".

"mdhokie" wrote:

I have two worksheets, one with charts and one with tables. Both of them
report data on separate worksheets. I use separate worksheets because the
format of the charts and tables interfere with each other if I put them all
on the same worksheet and trying to combine them also makes it difficult for
users to scroll and find what they want.

I used the "Data Validation: List" method to create a drop down menu of
potential geographic areas to be profiled in the charts and tables. Each
chart or table uses Vlookup with the value from the drop down menu list cell
to direct the appropriate values from the other worksheets.

I want users to be able to choose a geographic area from a drop down menu on
EITHER the chart or the table worksheet that will then populate ALL charts
and tables. I don't want to set one as "master" and set the other using an =
cell value because then the user has to jump back and forth, e.g., if the
drop down list is on the chart worksheet, then the user can't change the area
when on the table worksheet without switching to the chart sheet first then
switching back to view the table. I also don't want to have two drop down
choices that are independent because a viewer might think that the chart data
would switch when they changed the area on the table sheet and vice versa.

My only kludgy solution so far is to create a new window and put a dropdown
menu cell on a new worksheet visible in that new window (with scroll bars,
tabs and other features turned off) and then have all the vlookup functions
refer to this value. But playing with two windows and leaving this lookup
worksheet tab visible gets awkward and invites the user to play around with
worksheets that can lead to problems.

I really want a floating window that gives the user a drop down menu that
serves as a source for values in the Vlookup functions that drive the charts
and table values. Either that, or a way to have a source value that can be
set by the drop down menu on whichever worksheet is active, but is propagated
to both the chart and table worksheet simultaneously.

Any ideas on an elegant work around to my current kludgy solution?