T
Takeadoe
Folks,
First, let me say that I'm not sure that the subject necessarily fits
the question. I wasn't sure what to call it.
My question is so convoluted, that I'm not really sure how to start.
Ultimately, what I'm trying to accomplish is this. I have a chart
sheet. I would like to add a box (combo list/edit or dropdown/edit)
to the chart sheet that I could type values into that would update/
change the formula in a cell and ultimately the value of that cell.
That's the bird's eye view. Here are the details.
Cell B106 contains the following formula:
((INDEX(PHFD,$L106))-((INDEX(HrvstFD,$L106))*(INDEX(WNRL,$L106))
Cell L106 contains the following: MATCH($B106,FindPriorYear,0)
PHFD, HrvstFD, WNRL, and FindPriorYear are all named ranges
Note that the formula in Cell B106 is nearly 10x the size of what I've
displayed above. But, for purposes of this discussion, I only need a
sample. In short, the complex formula in B106 yields a population
(deer) estimate. That estimate is based on the following simplified
equation:
initial population - (harvest*WNRL)
With a little imagination you can guess that the initial population is
represented by (INDEX(PHFD,$L106), harvest by (INDEX(HrvstFD,$L106),
and WNRL by (INDEX(WNRL,$L106). The initial population is the
population just prior to the start of the hunting season. "Harvest"
is the reported harvest and WNRL is used to adjust the reported
harvest to accommodate harvested deer that go unreported i.e., are
unaccounted for. Presently, WNRL is a fixed value and the only way to
change it is to go directly to the cell. WNRL is actually a variable/
parameter disguised as a constant and is one of the many values that I
would like to change "on the fly", so to speak. At this point, I'm
thinking that a bit more background might be helpful.
The chart that I've referred to a number of times contains county deer
population estimates from 1980 to the present. The population
estimates are the product of a deer population model which I've built
into a worksheet using some rather complex formulas. Each year's
population estimate builds on the prior year's estimate and changes
made to any year will impact all subsequent estimates. These annual
population estimates derived from the model are then plotted against
known values for each year. The goal, as you would guess, is to get
the population model to generate estimates as close to the "known"
values as possible and that is where the "variables" like WNRL come
into play.
You might think of these as "fine-tuning knobs" that will allow me to
tweak the population estimate. As I indicated above, at present,
these fine-tuning knobs are constants and that is what I'm trying to
change with the EDIT boxes. By now it should be apparent why I want
these edit boxes right on the chart - so that I can instantly see how
the change affected the graph. I may have to change one value -WNRL -
100 times before I get the curve to look like I want it. And on top
of that, there are 6 "variables" that I will be tweaking. I need a
much more efficient system for doing this then going out to each cell
and changing it and coming back to the chart and so on and so forth.
I hope I didn't bore you to tears with too much detail, but I thought
more is better than less. If you would like to help, you can of
course work directly through the group or offline if you would like.
Thanks in advance for any help you may be able to offer.
Best,
Mike
First, let me say that I'm not sure that the subject necessarily fits
the question. I wasn't sure what to call it.
My question is so convoluted, that I'm not really sure how to start.
Ultimately, what I'm trying to accomplish is this. I have a chart
sheet. I would like to add a box (combo list/edit or dropdown/edit)
to the chart sheet that I could type values into that would update/
change the formula in a cell and ultimately the value of that cell.
That's the bird's eye view. Here are the details.
Cell B106 contains the following formula:
((INDEX(PHFD,$L106))-((INDEX(HrvstFD,$L106))*(INDEX(WNRL,$L106))
Cell L106 contains the following: MATCH($B106,FindPriorYear,0)
PHFD, HrvstFD, WNRL, and FindPriorYear are all named ranges
Note that the formula in Cell B106 is nearly 10x the size of what I've
displayed above. But, for purposes of this discussion, I only need a
sample. In short, the complex formula in B106 yields a population
(deer) estimate. That estimate is based on the following simplified
equation:
initial population - (harvest*WNRL)
With a little imagination you can guess that the initial population is
represented by (INDEX(PHFD,$L106), harvest by (INDEX(HrvstFD,$L106),
and WNRL by (INDEX(WNRL,$L106). The initial population is the
population just prior to the start of the hunting season. "Harvest"
is the reported harvest and WNRL is used to adjust the reported
harvest to accommodate harvested deer that go unreported i.e., are
unaccounted for. Presently, WNRL is a fixed value and the only way to
change it is to go directly to the cell. WNRL is actually a variable/
parameter disguised as a constant and is one of the many values that I
would like to change "on the fly", so to speak. At this point, I'm
thinking that a bit more background might be helpful.
The chart that I've referred to a number of times contains county deer
population estimates from 1980 to the present. The population
estimates are the product of a deer population model which I've built
into a worksheet using some rather complex formulas. Each year's
population estimate builds on the prior year's estimate and changes
made to any year will impact all subsequent estimates. These annual
population estimates derived from the model are then plotted against
known values for each year. The goal, as you would guess, is to get
the population model to generate estimates as close to the "known"
values as possible and that is where the "variables" like WNRL come
into play.
You might think of these as "fine-tuning knobs" that will allow me to
tweak the population estimate. As I indicated above, at present,
these fine-tuning knobs are constants and that is what I'm trying to
change with the EDIT boxes. By now it should be apparent why I want
these edit boxes right on the chart - so that I can instantly see how
the change affected the graph. I may have to change one value -WNRL -
100 times before I get the curve to look like I want it. And on top
of that, there are 6 "variables" that I will be tweaking. I need a
much more efficient system for doing this then going out to each cell
and changing it and coming back to the chart and so on and so forth.
I hope I didn't bore you to tears with too much detail, but I thought
more is better than less. If you would like to help, you can of
course work directly through the group or offline if you would like.
Thanks in advance for any help you may be able to offer.
Best,
Mike