Workbook Links

[pavlai]

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

[cparaske]

pavlai,

Short of actually seeing your report, I can only suggest you create links into the source workbook. You can use the Edit>Links function to point the workbook to the new source rather easily. Edit>Replace could take care of the reference problem (assuming, for example, that the new data is in Column D as opposed to Column C in the new month).

Could you post a small sample of the differences you are seeing between reports? There might be a better solution.

HTH,
cparaske