N
Nornny
Hi, so this is kind of complicated to explain, but I hope someone get
my drift. I have an annual report that I have just designed. It is on
excel file that's getting close to 3MB. It takes forever for it t
recalculate cells and such, I've been forced to turn calculation t
manual.
Why it is so big, is because of the amount of worksheets inside. I hav
12 worksheets, one for each month, and in those worksheets is
database of problems for a respective month. Those worksheets generall
have 200-400 rows (each row being a problem), and about a dozen column
of fields (which are the properties of the problem, like when th
problem arised and when it was solved). Multiply that by 12.
Then, I have a summary worksheet that takes those 12 sheets of data an
manipulate them in order to figure out various things, such as how man
problems there are monthly, or how many problems occured at 8:00AM
There is a bunch of manipulation involved, from really easy stuff lik
counting to really complex formulas.
Followed by that is a variety of graphs showing the data from th
Summary worksheet.
The reason my file started running slow was that my client wanted
rotating annual report. What this means is that he should be able t
create a new worksheet and paste in data for the newest month, delet
the oldest month, and the changes should be reflected in the Summar
sheet. I figured out a way to do that by just changing a row of cell
in the Summary sheet. All the other cells are INDIRECT referenced t
that row. So as long as the worksheets of monthly problems is the sam
as that one row in my Summary, everything works like a charm. The onl
problem is all the INDIRECT references slows down my file like crazy.
What I'm trying to do is instead of putting those 12 monthly proble
worksheets into my file, to take them out and leave them as separat
Excel files. I'm sure this can be done with linking or INDIRECT o
macros or something, but I don't know 1) how to 2) if this will spee
up my original file. This would be easier for me because the eac
month, I get the data as an individual file anyways. Right now, i'
just copying and pasting into a worksheet. I'd rather just leave it a
its own file and pull the data from there. Also, I don't want to ope
all 12 files just to open my Summary and Graphs file. Any help would b
greatly appreciated.
Please let me know if you need more clarification. Thanks
my drift. I have an annual report that I have just designed. It is on
excel file that's getting close to 3MB. It takes forever for it t
recalculate cells and such, I've been forced to turn calculation t
manual.
Why it is so big, is because of the amount of worksheets inside. I hav
12 worksheets, one for each month, and in those worksheets is
database of problems for a respective month. Those worksheets generall
have 200-400 rows (each row being a problem), and about a dozen column
of fields (which are the properties of the problem, like when th
problem arised and when it was solved). Multiply that by 12.
Then, I have a summary worksheet that takes those 12 sheets of data an
manipulate them in order to figure out various things, such as how man
problems there are monthly, or how many problems occured at 8:00AM
There is a bunch of manipulation involved, from really easy stuff lik
counting to really complex formulas.
Followed by that is a variety of graphs showing the data from th
Summary worksheet.
The reason my file started running slow was that my client wanted
rotating annual report. What this means is that he should be able t
create a new worksheet and paste in data for the newest month, delet
the oldest month, and the changes should be reflected in the Summar
sheet. I figured out a way to do that by just changing a row of cell
in the Summary sheet. All the other cells are INDIRECT referenced t
that row. So as long as the worksheets of monthly problems is the sam
as that one row in my Summary, everything works like a charm. The onl
problem is all the INDIRECT references slows down my file like crazy.
What I'm trying to do is instead of putting those 12 monthly proble
worksheets into my file, to take them out and leave them as separat
Excel files. I'm sure this can be done with linking or INDIRECT o
macros or something, but I don't know 1) how to 2) if this will spee
up my original file. This would be easier for me because the eac
month, I get the data as an individual file anyways. Right now, i'
just copying and pasting into a worksheet. I'd rather just leave it a
its own file and pull the data from there. Also, I don't want to ope
all 12 files just to open my Summary and Graphs file. Any help would b
greatly appreciated.
Please let me know if you need more clarification. Thanks