I want to create an excel file that provides a "profile" of a given geographic region. I want to provide both charts and tables based on source data in hidden worksheets.
I've developed a version of this with 9 hidden data worksheets and two visible worksheets. One visible worksheet has 4 charts, the other has 4 tables. I have them on separate worksheets because they're easier to view and because formatting columns and cells to accomodate both charts and tables without a lot of scrolling or navigation aids seemed too difficult. However, having the two sheets instead of one creates my problem, and thus my question.
I use the data validation tool to create a cell with a drop box to select the geographic region from a list. Then the selected value is referenced with a vlookup function to generate the appropriate source data for the charts and tables from the hidden data worksheets.
However, I have two of these selection cells, one for each visible worksheet. I want to have the viewer be able to select from either worksheet and have both charts AND tables reflect the new values. I haven't found a way to let the viewer do both: 1) select the region from either worksheet AND 2) have both worksheets updated to reflect the newly selected region.
Right now, they are un-synchronized: Worksheet A can have a chart based on newly selected region X while worksheet B will have tables based on region Y.
If I set one cell to simply reflect the value of the select box on the other sheet, the viewer would have to constantly switch back and forth between worksheets to select a region. That doesn't work well.
I tried synchronizing the two by having the selection cell on a separate worksheet and then arranged multiple windows so the selection button was shown separately. This was kludgy and looked bad. Furthermore, a user could screw up the window arrangement.
I guess a floating toolbar/window with the selection drop box would be ideal, but I've never done anything like that in Excel. How would I build it? How do I reference the list of regions (same as with validation tool?)? And more importantly, how would I propagate the value to both worksheets after the choice is made?
Any help/suggestions or even sample VB code to do this would be appreciated. FYI, I'm using Excel 2003.
Thanks in advance,
MD Hokie
I've developed a version of this with 9 hidden data worksheets and two visible worksheets. One visible worksheet has 4 charts, the other has 4 tables. I have them on separate worksheets because they're easier to view and because formatting columns and cells to accomodate both charts and tables without a lot of scrolling or navigation aids seemed too difficult. However, having the two sheets instead of one creates my problem, and thus my question.
I use the data validation tool to create a cell with a drop box to select the geographic region from a list. Then the selected value is referenced with a vlookup function to generate the appropriate source data for the charts and tables from the hidden data worksheets.
However, I have two of these selection cells, one for each visible worksheet. I want to have the viewer be able to select from either worksheet and have both charts AND tables reflect the new values. I haven't found a way to let the viewer do both: 1) select the region from either worksheet AND 2) have both worksheets updated to reflect the newly selected region.
Right now, they are un-synchronized: Worksheet A can have a chart based on newly selected region X while worksheet B will have tables based on region Y.
If I set one cell to simply reflect the value of the select box on the other sheet, the viewer would have to constantly switch back and forth between worksheets to select a region. That doesn't work well.
I tried synchronizing the two by having the selection cell on a separate worksheet and then arranged multiple windows so the selection button was shown separately. This was kludgy and looked bad. Furthermore, a user could screw up the window arrangement.
I guess a floating toolbar/window with the selection drop box would be ideal, but I've never done anything like that in Excel. How would I build it? How do I reference the list of regions (same as with validation tool?)? And more importantly, how would I propagate the value to both worksheets after the choice is made?
Any help/suggestions or even sample VB code to do this would be appreciated. FYI, I'm using Excel 2003.
Thanks in advance,
MD Hokie
Last edited: