Large Workbook - trouble opening
 

I have a couple of workbooks (Excel '07) that I'm having trouble with them
being slow to open. One, I sort of understand why it's slow as it has a lot
of calculations, the other I'm not sure why. Here's the senarios an maybe
someone could suggest a way to get them to open quicker......

1. The most recent problem is with a worksheet where I'm taking a list of
our customers and the reoccuring work we do for them (and other important
info) and trying to make rolodex cards for each customer. I have the
complete list on sheet 1. Then on each subsequent sheet I have only one
customer's info per sheet in a format that will fit on the rolodex cards that
I want to use. I get the customer's info by connecting back to the sheet 1
(i.e. "=Sheet1!A1" in the cell) sometimes I have an IF statement if there is
a possiblity that the cell on Sheet 1 could be blank but other than that no
"calculations" per se. Is there any way to keep this one from being a pain
in the you know what everytime I open it or am I just SOL? I'm only on the
letter C.

2. The second worksheet is the one that is a doosey. I used to do this one
in Excel '03 before my computer crashed and I upgraded to '07. I never had
problems opening it until I got '07. I've tried saving it as every option
available but it doesn't help speed things up. I have to do a report for my
boss showing our billable time vs non-billable time and include what each
technician is paid vs what is worked (among other things). There are TONS of
calcs, not just on each worksheet for each week of the year (yes, I said
year) but back to the summary sheet which is what is printed out. Is there
any way I can have it do the calculations ONLY when the workbook is saved,
not when opened? I think that could possibly make it open a lot faster. I
try to not let the data entry part get so bad that I have to enter weeks at a
time so I could wait to save until then end. Then if it took a while to
save, I could just file the time folder away while it's saving.

Sorry this is so long but it's not easy to describe in a short way.

Large Workbook - trouble opening
 

1. I have a mailing list set up where I double click on the name and the
envelope tab is filled out. So, why not have ONE form that is populated
thusly from the ONE database.

2. Set calculation to manual and in the ThisWorkbook module set up a
before_close or before_save event to do the calculation. You might also try
to make your project more efficient. Some use many formulas where one will
do.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rbecker" wrote in message
...
I have a couple of workbooks (Excel '07) that I'm having trouble with them
being slow to open. One, I sort of understand why it's slow as it has a
lot
of calculations, the other I'm not sure why. Here's the senarios an maybe
someone could suggest a way to get them to open quicker......

1. The most recent problem is with a worksheet where I'm taking a list of
our customers and the reoccuring work we do for them (and other important
info) and trying to make rolodex cards for each customer. I have the
complete list on sheet 1. Then on each subsequent sheet I have only one
customer's info per sheet in a format that will fit on the rolodex cards
that
I want to use. I get the customer's info by connecting back to the sheet
1
(i.e. "=Sheet1!A1" in the cell) sometimes I have an IF statement if there
is
a possiblity that the cell on Sheet 1 could be blank but other than that
no
"calculations" per se. Is there any way to keep this one from being a
pain
in the you know what everytime I open it or am I just SOL? I'm only on
the
letter C.

2. The second worksheet is the one that is a doosey. I used to do this
one
in Excel '03 before my computer crashed and I upgraded to '07. I never
had
problems opening it until I got '07. I've tried saving it as every option
available but it doesn't help speed things up. I have to do a report for
my
boss showing our billable time vs non-billable time and include what each
technician is paid vs what is worked (among other things). There are TONS
of
calcs, not just on each worksheet for each week of the year (yes, I said
year) but back to the summary sheet which is what is printed out. Is
there
any way I can have it do the calculations ONLY when the workbook is saved,
not when opened? I think that could possibly make it open a lot faster.
I
try to not let the data entry part get so bad that I have to enter weeks
at a
time so I could wait to save until then end. Then if it took a while to
save, I could just file the time folder away while it's saving.

Sorry this is so long but it's not easy to describe in a short way.

Large Workbook - trouble opening
 

on your 2nd problem, file taking along time to open...maybe setting it to
manual calc will save time on opening? when you save it at the end of the
session, it will automatically recalc the entire s/sheet before it saves?
(may help with the 1st problem too?)

/tools/options/calculations tab - select manual calc
Also, maybe you could consider converting the calcs from old data, to
values? that way it wont keep going through and calc'ing answers to things
that have already been calc'd and that wont change ever again?

"rbecker" wrote:

I have a couple of workbooks (Excel '07) that I'm having trouble with them
being slow to open. One, I sort of understand why it's slow as it has a lot
of calculations, the other I'm not sure why. Here's the senarios an maybe
someone could suggest a way to get them to open quicker......

1. The most recent problem is with a worksheet where I'm taking a list of
our customers and the reoccuring work we do for them (and other important
info) and trying to make rolodex cards for each customer. I have the
complete list on sheet 1. Then on each subsequent sheet I have only one
customer's info per sheet in a format that will fit on the rolodex cards that
I want to use. I get the customer's info by connecting back to the sheet 1
(i.e. "=Sheet1!A1" in the cell) sometimes I have an IF statement if there is
a possiblity that the cell on Sheet 1 could be blank but other than that no
"calculations" per se. Is there any way to keep this one from being a pain
in the you know what everytime I open it or am I just SOL? I'm only on the
letter C.

2. The second worksheet is the one that is a doosey. I used to do this one
in Excel '03 before my computer crashed and I upgraded to '07. I never had
problems opening it until I got '07. I've tried saving it as every option
available but it doesn't help speed things up. I have to do a report for my
boss showing our billable time vs non-billable time and include what each
technician is paid vs what is worked (among other things). There are TONS of
calcs, not just on each worksheet for each week of the year (yes, I said
year) but back to the summary sheet which is what is printed out. Is there
any way I can have it do the calculations ONLY when the workbook is saved,
not when opened? I think that could possibly make it open a lot faster. I
try to not let the data entry part get so bad that I have to enter weeks at a
time so I could wait to save until then end. Then if it took a while to
save, I could just file the time folder away while it's saving.

Sorry this is so long but it's not easy to describe in a short way.

Large Workbook - trouble opening
 

Thanks for your quick response, Don!!

1. How did you do the mailing list? I might actually be able to use that.

2. I will have to look to see if I can scale down on the calculations. I
would like to try your suggestion "Set calculation to manual and in the
ThisWorkbook module set up a before_close or before_save event to do the
calculation." but where do I find it? I'm still learning where everything
is on '07.

"Don Guillett" wrote:


1. I have a mailing list set up where I double click on the name and the
envelope tab is filled out. So, why not have ONE form that is populated
thusly from the ONE database.

2. Set calculation to manual and in the ThisWorkbook module set up a
before_close or before_save event to do the calculation. You might also try
to make your project more efficient. Some use many formulas where one will
do.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rbecker" wrote in message
...
I have a couple of workbooks (Excel '07) that I'm having trouble with them
being slow to open. One, I sort of understand why it's slow as it has a
lot
of calculations, the other I'm not sure why. Here's the senarios an maybe
someone could suggest a way to get them to open quicker......

1. The most recent problem is with a worksheet where I'm taking a list of
our customers and the reoccuring work we do for them (and other important
info) and trying to make rolodex cards for each customer. I have the
complete list on sheet 1. Then on each subsequent sheet I have only one
customer's info per sheet in a format that will fit on the rolodex cards
that
I want to use. I get the customer's info by connecting back to the sheet
1
(i.e. "=Sheet1!A1" in the cell) sometimes I have an IF statement if there
is
a possiblity that the cell on Sheet 1 could be blank but other than that
no
"calculations" per se. Is there any way to keep this one from being a
pain
in the you know what everytime I open it or am I just SOL? I'm only on
the
letter C.

2. The second worksheet is the one that is a doosey. I used to do this
one
in Excel '03 before my computer crashed and I upgraded to '07. I never
had
problems opening it until I got '07. I've tried saving it as every option
available but it doesn't help speed things up. I have to do a report for
my
boss showing our billable time vs non-billable time and include what each
technician is paid vs what is worked (among other things). There are TONS
of
calcs, not just on each worksheet for each week of the year (yes, I said
year) but back to the summary sheet which is what is printed out. Is
there
any way I can have it do the calculations ONLY when the workbook is saved,
not when opened? I think that could possibly make it open a lot faster.
I
try to not let the data entry part get so bad that I have to enter weeks
at a
time so I could wait to save until then end. Then if it took a while to
save, I could just file the time folder away while it's saving.

Sorry this is so long but it's not easy to describe in a short way.

Large Workbook - trouble opening
 

Right click sheet tabview codeinsert thismodify to suit
While in the vbe (visual basic editor) look for the ThisWorkbook module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 1 Then
If ActiveCell.Offset(0, 2) < "" Then
Title = ActiveCell.Offset(0, 2)
Else
Title = ""
End If
If ActiveCell.Offset(0, 1) < "" Then
FirstName = ActiveCell.Offset(0, 1) & " "
Else
FirstName = ""
End If
LastName = ActiveCell
ADDRESSEE = Application.Proper(Title + FirstName + LastName)
[envelope!c6] = ADDRESSEE
[envelope!c7] = ActiveCell.Offset(0, 3)
[envelope!c8] = ActiveCell.Offset(0, 4)
[envelope!c9] = ActiveCell.Offset(0, 5)
Sheets("envelope").Select
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rbecker" wrote in message
...
Thanks for your quick response, Don!!

1. How did you do the mailing list? I might actually be able to use that.

2. I will have to look to see if I can scale down on the calculations. I
would like to try your suggestion "Set calculation to manual and in the
ThisWorkbook module set up a before_close or before_save event to do the
calculation." but where do I find it? I'm still learning where
everything
is on '07.

"Don Guillett" wrote:


1. I have a mailing list set up where I double click on the name and the
envelope tab is filled out. So, why not have ONE form that is populated
thusly from the ONE database.

2. Set calculation to manual and in the ThisWorkbook module set up a
before_close or before_save event to do the calculation. You might also
try
to make your project more efficient. Some use many formulas where one
will
do.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rbecker" wrote in message
...
I have a couple of workbooks (Excel '07) that I'm having trouble with
them
being slow to open. One, I sort of understand why it's slow as it has
a
lot
of calculations, the other I'm not sure why. Here's the senarios an
maybe
someone could suggest a way to get them to open quicker......

1. The most recent problem is with a worksheet where I'm taking a list
of
our customers and the reoccuring work we do for them (and other
important
info) and trying to make rolodex cards for each customer. I have the
complete list on sheet 1. Then on each subsequent sheet I have only
one
customer's info per sheet in a format that will fit on the rolodex
cards
that
I want to use. I get the customer's info by connecting back to the
sheet
1
(i.e. "=Sheet1!A1" in the cell) sometimes I have an IF statement if
there
is
a possiblity that the cell on Sheet 1 could be blank but other than
that
no
"calculations" per se. Is there any way to keep this one from being a
pain
in the you know what everytime I open it or am I just SOL? I'm only on
the
letter C.

2. The second worksheet is the one that is a doosey. I used to do
this
one
in Excel '03 before my computer crashed and I upgraded to '07. I never
had
problems opening it until I got '07. I've tried saving it as every
option
available but it doesn't help speed things up. I have to do a report
for
my
boss showing our billable time vs non-billable time and include what
each
technician is paid vs what is worked (among other things). There are
TONS
of
calcs, not just on each worksheet for each week of the year (yes, I
said
year) but back to the summary sheet which is what is printed out. Is
there
any way I can have it do the calculations ONLY when the workbook is
saved,
not when opened? I think that could possibly make it open a lot
faster.
I
try to not let the data entry part get so bad that I have to enter
weeks
at a
time so I could wait to save until then end. Then if it took a while
to
save, I could just file the time folder away while it's saving.

Sorry this is so long but it's not easy to describe in a short way.

Large Workbook - trouble opening
 

Never thought about converting to values on the 2nd one. You're right, once
the numbers get figured, the chances of them changing are slim (since they
refer to payroll/completed jobs). That would cut down on a LOT of the calcs.
THANKS!!

"fdibbins" wrote:

on your 2nd problem, file taking along time to open...maybe setting it to
manual calc will save time on opening? when you save it at the end of the
session, it will automatically recalc the entire s/sheet before it saves?
(may help with the 1st problem too?)

/tools/options/calculations tab - select manual calc
Also, maybe you could consider converting the calcs from old data, to
values? that way it wont keep going through and calc'ing answers to things
that have already been calc'd and that wont change ever again?

"rbecker" wrote:

I have a couple of workbooks (Excel '07) that I'm having trouble with them
being slow to open. One, I sort of understand why it's slow as it has a lot
of calculations, the other I'm not sure why. Here's the senarios an maybe
someone could suggest a way to get them to open quicker......

1. The most recent problem is with a worksheet where I'm taking a list of
our customers and the reoccuring work we do for them (and other important
info) and trying to make rolodex cards for each customer. I have the
complete list on sheet 1. Then on each subsequent sheet I have only one
customer's info per sheet in a format that will fit on the rolodex cards that
I want to use. I get the customer's info by connecting back to the sheet 1
(i.e. "=Sheet1!A1" in the cell) sometimes I have an IF statement if there is
a possiblity that the cell on Sheet 1 could be blank but other than that no
"calculations" per se. Is there any way to keep this one from being a pain
in the you know what everytime I open it or am I just SOL? I'm only on the
letter C.

2. The second worksheet is the one that is a doosey. I used to do this one
in Excel '03 before my computer crashed and I upgraded to '07. I never had
problems opening it until I got '07. I've tried saving it as every option
available but it doesn't help speed things up. I have to do a report for my
boss showing our billable time vs non-billable time and include what each
technician is paid vs what is worked (among other things). There are TONS of
calcs, not just on each worksheet for each week of the year (yes, I said
year) but back to the summary sheet which is what is printed out. Is there
any way I can have it do the calculations ONLY when the workbook is saved,
not when opened? I think that could possibly make it open a lot faster. I
try to not let the data entry part get so bad that I have to enter weeks at a
time so I could wait to save until then end. Then if it took a while to
save, I could just file the time folder away while it's saving.

Sorry this is so long but it's not easy to describe in a short way.