Spreadsheets on shared drives..
 

Hi, i have a large complex spreadsheet which is currently hosted on a shared
drive and accessed via emailed hyperlinks. The sheet is such that it takes
several minutes to calculate changes, and as such when multiple entries are
being made daily it poses a problem with several users waiting for it to
calculate and closing it or thinking it's encountered a problem - i've had to
shave 250+ complex formulas from considering rows 1-9999 to 1-7000 just to
get it to calculate without crashing at all.
Is there a way of turning off the automatic calculation for a central sheet
such as this, but still allowing casual input of data, so one person (namely
me) can update it last thing or first thing and take the calculation hit,
leaving things streamlined for others?
I've tried it in the tools menu but unfortunately turning it to manual
calculation appears only to apply to my machine..

Spreadsheets on shared drives..
 

An easy solution is to setup the environment for the user and then restore it
prior to closing.

In a Workbook Open macro:
1. "remember" the original calculation mode
2. set the calculation mode to manual
3. msgbox to the user that the calculation is manual

In a BeforeClose macro:

1. msgbox to the user that the calculation mode is being re-set
2. set the calculation mode to the "remembered mode"

--
Gary''s Student - gsnu200802


"GD" wrote:

Hi, i have a large complex spreadsheet which is currently hosted on a shared
drive and accessed via emailed hyperlinks. The sheet is such that it takes
several minutes to calculate changes, and as such when multiple entries are
being made daily it poses a problem with several users waiting for it to
calculate and closing it or thinking it's encountered a problem - i've had to
shave 250+ complex formulas from considering rows 1-9999 to 1-7000 just to
get it to calculate without crashing at all.
Is there a way of turning off the automatic calculation for a central sheet
such as this, but still allowing casual input of data, so one person (namely
me) can update it last thing or first thing and take the calculation hit,
leaving things streamlined for others?
I've tried it in the tools menu but unfortunately turning it to manual
calculation appears only to apply to my machine..

Spreadsheets on shared drives..
 

"Gary''s Student" wrote:

An easy solution is to setup the environment for the user and then restore it
prior to closing.

In a Workbook Open macro:
1. "remember" the original calculation mode
2. set the calculation mode to manual
3. msgbox to the user that the calculation is manual

In a BeforeClose macro:

1. msgbox to the user that the calculation mode is being re-set
2. set the calculation mode to the "remembered mode"

--
Gary''s Student - gsnu200802


"GD" wrote:

Hi, i have a large complex spreadsheet which is currently hosted on a shared
drive and accessed via emailed hyperlinks. The sheet is such that it takes
several minutes to calculate changes, and as such when multiple entries are
being made daily it poses a problem with several users waiting for it to
calculate and closing it or thinking it's encountered a problem - i've had to
shave 250+ complex formulas from considering rows 1-9999 to 1-7000 just to
get it to calculate without crashing at all.
Is there a way of turning off the automatic calculation for a central sheet
such as this, but still allowing casual input of data, so one person (namely
me) can update it last thing or first thing and take the calculation hit,
leaving things streamlined for others?
I've tried it in the tools menu but unfortunately turning it to manual
calculation appears only to apply to my machine..

Ah ok, i'll need to develop my knowledge of macros then (currently zero) but
i think i understand the fundementals, thanks for that

Simple IF formula hopefully..
 

For some reason an error means I can't ask a new question but can reply to
one of my old ones!

My question is regarding a range of data in values, say from 1 to 1000 in
cells A1-A100 (typical value being 47.4 etc)

I am looking to get a formula which will select the values out of these
results which are within specific ranges, I.e 0-49 - and report a count as a
result. For instance if there are 3 results between 50-99 (64, 78, 81) I
would like a formula which can identify these and produce a result of '3' to
signify how many values fall into the range. Any ideas?

Simple IF formula hopefully..
 

=COUNTIF(A1:A100,"<99")-COUNTIF(A1:A100,"<50")


Gord Dibben MS Excel MVP

On Tue, 16 Sep 2008 04:39:01 -0700, GD wrote:

For some reason an error means I can't ask a new question but can reply to
one of my old ones!

My question is regarding a range of data in values, say from 1 to 1000 in
cells A1-A100 (typical value being 47.4 etc)

I am looking to get a formula which will select the values out of these
results which are within specific ranges, I.e 0-49 - and report a count as a
result. For instance if there are 3 results between 50-99 (64, 78, 81) I
would like a formula which can identify these and produce a result of '3' to
signify how many values fall into the range. Any ideas?