T
Takeadoe
Folks, this is a follow-up to my earlier post. I've done some
experimenting and I can quickly accomplish what I'm after if I could
figure out how to change a cell value from a worksheet directly from a
chartsheet.
Here's my plan.
Worksheet1 = PopulationModel
Worksheet2 = PrehuntEstimates
Worksheet3 = PivotTable
Chartsheet4 = Chart
A closeup look at each component:
Worksheet1
Year PHFD PHYD PHAD
WS2 WS2 WS2 (CELLS ARE ALL LINKED TO CELLS IN WORKSHEET2
and updated of course when they change)
Worksheet2 (88R*7C)
PHFD PHYD PHAD ADJUST UPHFD UPHYD UPHAD
PHFD=ADJUST*UPHFD (adjust is used to adjust all linked values)
Thus, when I change "adjust", it changes the values (PHFD PHYD PHAD) in
worksheet 2, which in turn change the values in worksheet 1. Once this
is done, I have to refresh the Pivot table and then go to chart to see
how the change affected the curve.
I added a command button to the chart which I use to refresh the pivot
table. My question for the group - is there anyway that I can gain
access to the "adjust" value in Worksheet2 directly from the chart so
as to avoid switching between the chart and the Worksheet2?
Does that make any sense? In very simplistic terms, it would be akin to
having a blank cell on the chart that would be linked to the "adjust"
cell in worksheet 2. The only thing that I can see that would
complicate things, is when I change counties (n=88). I have a dropdown
list on the chart from which I select the county for charting (data is
coming from Worksheet3 - the Pivot Table). Somehow it would have to
know to go to the correct row in Worksheet2 when I changed counties.
Any help at all would really be appreciated.
Regards,
Mike
experimenting and I can quickly accomplish what I'm after if I could
figure out how to change a cell value from a worksheet directly from a
chartsheet.
Here's my plan.
Worksheet1 = PopulationModel
Worksheet2 = PrehuntEstimates
Worksheet3 = PivotTable
Chartsheet4 = Chart
A closeup look at each component:
Worksheet1
Year PHFD PHYD PHAD
WS2 WS2 WS2 (CELLS ARE ALL LINKED TO CELLS IN WORKSHEET2
and updated of course when they change)
Worksheet2 (88R*7C)
PHFD PHYD PHAD ADJUST UPHFD UPHYD UPHAD
PHFD=ADJUST*UPHFD (adjust is used to adjust all linked values)
Thus, when I change "adjust", it changes the values (PHFD PHYD PHAD) in
worksheet 2, which in turn change the values in worksheet 1. Once this
is done, I have to refresh the Pivot table and then go to chart to see
how the change affected the curve.
I added a command button to the chart which I use to refresh the pivot
table. My question for the group - is there anyway that I can gain
access to the "adjust" value in Worksheet2 directly from the chart so
as to avoid switching between the chart and the Worksheet2?
Does that make any sense? In very simplistic terms, it would be akin to
having a blank cell on the chart that would be linked to the "adjust"
cell in worksheet 2. The only thing that I can see that would
complicate things, is when I change counties (n=88). I have a dropdown
list on the chart from which I select the county for charting (data is
coming from Worksheet3 - the Pivot Table). Somehow it would have to
know to go to the correct row in Worksheet2 when I changed counties.
Any help at all would really be appreciated.
Regards,
Mike