Hey everyone, i am a new poster and was looking for any ideas about the problem i am facing. I am using excel for monthly accounting reports but cannot seem to devise a way to make them more and more automated. The problem is that when all the sheets are linked they move folders every month and new reporting locations are added on a random basis so formulas and sheets constantly need to be updated.

Does anyone have any suggestions on how to help automate these processes? Any help would be appreciated.

Thanks, Troy

acctuser wrote:
Hey everyone, i am a new poster and was looking for any ideas about the
problem i am facing. I am using excel for monthly accounting reports
but cannot seem to devise a way to make them more and more automated.
The problem is that when all the sheets are linked they move folders
every month and new reporting locations are added on a random basis so
formulas and sheets constantly need to be updated.

Does anyone have any suggestions on how to help automate these
processes? Any help would be appreciated.

Hi Troy and Welcome,

For this problem the best suggestion I can think of (and probably the
most obvious one) is to standardize the location and naming of
worksheets and other data sources. Your organization may have certain
needs that explain why files are moved around after (or before) they are
finalized, but you have a case for not moving these files. Perhaps you
need to make copies of the files yourself, and store them in a stable
structure. This may or may not work for you though--especially if the
content of the source files is subject to change.

In my workgroup we create new folders every quarter, and subfolders for
each month. Each month, data sources are copied forward from the prior
month and the current month reports are built on the copies. Nothing is
moved after a month is closed.

Among the advantages of this: we have a predictable and stable
convention for linked files, and we have snapshots of data sources.

On the downside, a lot of data is duplicated in the monthly copies. We
get razzed by the network admins for our disk consumption on a regular
basis, but c'est la guerre.

Hope this helps!

Thanks smartin. We follow a similar procedure as it sounds you do. So when you copy over all your reports from say June that are linked, do you then go in and manually update all the equations to now read from the July folders?

Thanks smartin. We follow a similar procedure as it sounds you do. So when you copy over all your reports from say June that are linked into the new July folders, do you then go in and manually update all the equations to now read from the July folders?