HELP - I NEED A PLAN

[Takeadoe]

Folks,

I've spent a fair bit of time perusing this and other groups. Two
things were very apparent to me. First, I don't know much! Second,
I'm so far out I don't even know what to ask. The questions that
folks have been asking are very detailed and targeted to a very
specific task. My situation is different. I need a plan, some general
guidance on how to proceed. I need a roadmap because I'm not even
sure where I'm going. So, asking for detailed directions at this
point is senseless! Here's the deal.

I have 4 workbooks (SOURCE.XLS, RECONSTRUCT.XLS, MODELS.XLS,
RESULTS.XLS) and each contains 88 worksheets, one for each management
unit (county). The exception is SOURCE.XLS.

SOURCE contains a single worksheet with 88 rows (counties) and 4
columns (age classes). SOURCE contains buck (male deer) harvest
figures for the current year's hunting season (2005 in this case).

RECONSTRUCT uses the buck harvest data in SOURCE to
reconstruct/estimate the size of the buck population just prior to the
hunting season. Currently, I have to paste the buck harvest data into
each worksheet (88 cut and paste operations) of RECONSTRUCT. There are
a number of formulas that use the harvest data within RECONSTRUCT to
generate prehunt population estimates for each age class (n=4).
RECONSTRUCT consists of 2 4*30 matrices (age class*year), a harvest
data matrix and a population estimates matrix. Much of the historical
data (both harvest and population estimates) is used in the
calculations, so I must keep it. Presently, the following steps are
repeated, year in and year out: 1) Select the harvest data from SOURCE
and paste it into the harvest matrix in RECONSTRUCT and label it 2005
2) Go to population matrix in RECONSTRUCT and copy most recent
population estimates, in this case 2004, into the 2005 column. The
population values are updated using the 2005 data from the harvest
matrix. Once the buck population is reconstructed for all 88 counties,
the current year's population estimates for the buck population,
along with the harvest data, must be cut and pasted individually into
MODEL.XLS. In addition to these 2 pieces of data, doe (female) harvest
data from SOURCE, must also be pasted into MODEL.XLS. These harvest
data (buck and doe) and buck population estimates are then used in a
series of calculations in MODEL to generate the prehunt doe population
and ultimately project the size of the total herd to the following year
(in this case, 2006). Like the RECONSTRUCT worksheet, MODEL.XLS
contains roughly 30 columns, 1 for each year. However, it contains
about the same number of rows representing an array of various
descriptive variables for the population. The final phase of this
annual undertaking is what I call extraction. Many of the 30 variables
calculated in each of 88 sheets in MODEL are needed for further
analyses and evaluation of trends. Thus, what I would like to do is
extract values for the most recent year for select variables and add
them to RESULTS.XLS, which would have 88 sheets (1 for each county) and
say 10 rows and 1 column for each year from 1977 to present.

So as I see it, I've got the following tasks to automate:
1) I need to get the buck harvest data out of SOURCE and update the
harvest matrix in RECONSTRUCT.
2) Within the population matrix of RECONSTRUCT I need to copy and paste
the most recent range into the current year's column to generate buck
population estimates.
3) I need to extract the buck harvest and population estimates from
each of 88 sheets and paste them into MODEL, but only after I copy the
most recent column and paste into the current year's column within
MODEL.
4) I need to extract certain cells from all 88 worksheets within MODEL
and paste them into the RESULTS.XLS spreadsheet for trend analyses.

I need to do this once a year. Presently, I'm cutting and pasting 88
times for each of these operations and it is killing me. I know there
is a better way.

I apologize for the lengthy post, but I figured you would need the
detail sooner or later. Any help that you are willing to offer will
truly be appreciated. I also apologize for any cross-postings, but I'm
desperate!

Regards,

Mike

 

[L. Howard Kittle]

Hi Mike,

I may not have a snowball chance in hell to get you there, but lets try
this:

So as I see it, I've got the following tasks to automate:
1) I need to get the buck harvest data out of SOURCE and update the
harvest matrix in RECONSTRUCT.

Lets shoot for step 1)

Send me a sample workbook of SOURCE and RECONSTRUCT and some simple,
detailed info of what you want to happen in this first step, "...buck
harvest data from Source" "...to harvest matrix in Reconstruct".

Lets see if we can get that done and go from there.

Now on a broader view, can you send me a sample of ALL four workbooks to use
as an overall reference? Don't need any detail on these workbooks until it
becomes relevant to task at hand, but will use them as a library resource so
to speak.

HTH
Regards,
Howard

(e-mail address removed)

 

[Takeadoe]

Howard,
First, thanks very much for taking the time to reply. I really appreciate
your willingness to help me get this ball rolling. If nothing else, round
the corners off these stone wheels!
As for the workbooks, should I send these to you "offline"? If so, please
drop me an email. I'm not sure how, even if it is possible, to attach files
to posts. Perhaps like the Yahoo and Google groups there is a place to
upload files to. Anyhow, looking forward to hearing from you.

Mike

 

[L. Howard Kittle]

Yes, send to me off line. I can't e-mail you I don't have your e-mail
address.

You can send attachment on regular e-mail, under Insert > File attachment >
then select the workbooks from your excel file.

Include some detail on what you are trying to do.

(e-mail address removed)

Regards,
Howard

 

[Takeadoe]

Howard - I'll send it tomorrow. Thanks for your willingness to help.

Mike

 

[L. Howard Kittle]

Here we go!!!!

 

[L. Howard Kittle]

Hey there, waiting for some sample workbooks, if you want.

(e-mail address removed)

Regards,
Howard