I'm looking for a solution to convert a very laborious manual process to an
automated or at least more efficient processs.

I just started a new position where I create metrics reports. The current
process gathers data from other parties using a number of input sheets
(several source worksheets). They input data into these sheest and I then
copy specific cells ifrom their input sheets into a destination workbook
(which has many worksheets) and create charts. This occurs on a monthly basis
and the data is accumulated. I then display the data from the current and
previous months in an excel chart.

I would like to establish links (or run a macro) from the destination
worksheets to the source worksheets so the destination cells are
automatically populated when the input sheets are populated. However,
there are several obstacles to overcome. First, the name of the source
worksheets is variable (the last node is the reporting month and day).
Second, the source data is not always in the same cell in the destination
sheet. Third, I also need to keep the integrity of the previous months data
(via link or macro).

There are many reports to create and thus the reporting takes several days
to manually input the data into the destination sheets. The charts are
automatically updated as they are linked to the destination worksheet cells
so there is no issue there.

I need to improve the automation of efficiecny of gathering the data from
the source sheets and inputting it into the destination sheets and any help
is appreciated. Thanks.


pavlai