programming excel to update master spreadsheet automatically

[drthursday]

i download ~20 files depicting the financial status of ~20 subtasks
each month and consolidate the results manually into one "master"
spreadsheet where i display the information in various ways (for
example by subtask, team, directorate, et al).

is there a way to program the "master" file so that it will update
itself when i download the 20 files containing the most current data
for each task?

any input on this would be greatly appreciated as i am currently at a
loss and constantly worry that i've entered the data incorrectly.
regards,
amy

 

[Fredrik Wahlgren]

i download ~20 files depicting the financial status of ~20 subtasks
each month and consolidate the results manually into one "master"
spreadsheet where i display the information in various ways (for
example by subtask, team, directorate, et al).

is there a way to program the "master" file so that it will update
itself when i download the 20 files containing the most current data
for each task?

any input on this would be greatly appreciated as i am currently at a
loss and constantly worry that i've entered the data incorrectly.
regards,
amy

I think you need to explain how your sheet(s) are organized and how you
load or enter data.

/ fredrik

 

[drthursday]

Oh, ok, yes sure. Thank you for your inquiry.

How my sheets are organized....
I have created a workbook with several worksheets. I may wind up
dividing these worksheets by category to avoid confusion as the tab
area near the bottom will become really cluttered but, for now they
remain worksheets within one workbook and can be grouped as follows:


1 current month detailed break out of all 20 subtasks,
1 current month brief summary of all subtasks within our directorate,
3 current month brief summary worksheets ordered to represent the 3
teams in our directorate, and
20 subtask historical reference worksheets.

How I currently load or enter my data....
I retrieve the 20 files containing financial data for the 20 subtasks
in our directorate. After printing them out, I read through and type
the pertinent data into my workbook, the old fashioned way.

Over the course of today I determined that I could link my workbook to
the various files that I ordinarily download, print out, etc.

The trouble, at least in my mind, is how to get my worksheet to
recognize the current month files (which change names every month...
nov, dec, etc) in order to reference those and move over in the file to
capture the current month's data.

It seems like there might be a way to tell it to do that but I haven't
a clue where to begin.

Again, any input would be greatly appreciated as I remain at a loss.

Thanks,
Amy




I have

 

[Fredrik Wahlgren]

Hi

The most significant sentences in your reply are "I retrieve the 20 files
containing financial data for the 20 subtasks
in our directorate.After printing them out, I read through and type the
pertinent data into my workbook, the old fashioned way."

This is important. The first issue that you need to think about is whether
these files can be read automatically into excel. If you can get this data
into excel without having to print them and then enter datat manually, you
will have made things much easier. Once this is done , it should be
relatively easy to have additional sheets where you calculate consolidated
values.

Is the file you get a text file? If it is, then you may be able to open them
from Excel. If they are not easily read into excel, the question is if you
can get these files formatted in such a way that they can be read into
excel.

Ideally, if your data comes from another Mac application, you may be able to
use AppleScript to load the data into Excel. IMy guess is that you probably
get these files from a PC.

In order to proceed, the following information is needed:
1) From what program do you get the files?
2) What kind of files do you get? Is it a text file, a word document or a
pdf file?
4) If it's a text file, is the data structured? i.e. do you get a comma
separated text file?
5) If you get unstructured data, can you get the dtata in a different
format, like xml?

These are things I need to know before I can say anything about your other
questions like month files. Right now I think that's the least problematic
part. Not having to enter data manually is much more important.

Feel free to email me directly if you feel this is very personal to you. We
can then post solutions to this problem for the benefit of the community.

Regards,
Fredrik

 

[drthursday]

Fredrik,

Thank you so much for your assistance with this.

Fortunately, the files I download from the database are excel files. I
have tinkered with linking them to my workbook to no avail. As I
mentioned before, I wasn't sure how to program things to pull data from
a) the new respective files each month, and b) the new cells containing
the important data for that month.

I am uplifted by your appraisal of the situation relative to
calculating consolidated values on a monthly basis and look forward to
your response.

Thanks again,
Amy

 

[Fredrik Wahlgren]

Hi

Thanks for this info. There are still a lot of things I don't understand
yet. You mention that you have to print them out and then enter the data
manually. I would need some kind of simple sample ( the data can be fake)
which shows how the data is organized so that the manual step can be
skipped. You may want to create such a sheet yourself and then show how you
enter data in your presentation sheet. It is imperative that I understand
how the data is transferred.

/ Fredrik