M
MW
Hi!
I'm trying to solve a quite delicate problem.
I want to distribute a time-limited (locked with standard Excel
protection) spreadsheet that will stop working after a certain date.
After the time period is over, it's important that all previous
calculations and data remains, i.e. all formulas must stop working
after a certain date BUT without a loss of data!!! It must be possible
for users to archive their data in the same sheet/workbook. I.e.
already made calculations must remain at all times.
If users try to override the time-limitations by setting their system
clock to a previous date or if some users "crack" the protection
password it is not very important to me. Still, most users will have
to get a fresh copy of the spreadsheet from me.
*** PROBLEM 1: VBA is out of the question
Since it's easy to shut VBA off it is out of the question. Also, to
keep it as simple as possible, since many users don't even know how to
turn macros ON (in some versions and companies, excel protection
levels turn macros off automatically) the spreadsheet must not contain
any VBA code at all.
*** PROBLEM 2: An IF-formula is out of the question
Since IF-formulas contain a false-part by default they are not part of
the overall solution. The following statement will not work for me:
IF(<date is valid>; <perform calculation to this cell>; 0)
If the sheet is opened after expiration date, all previous
calculations will be overwritten. And I just want them to stop
working. It must be possible for users to review their old work.
If the IF-formula had an optional false-part it would have solved my
problem. Meaning that if the date is expired – do nothing.
*** PROBLEM 3: A Circular reference "should" not be used
I've tried to simulate an IF-formula without a false part using
circular reference.
Using the setting in Excel allowing iterations, no error messages are
shown when a circular reference occurs. It's the possible to write the
following formula:
IF(<date is valid>; <perform calculation to this cell>: <put value
from this cell>)
For example, a content in cell C3:
IF(<date is valid>; <perform calculation to this cell>; C3)
The above statement will generate a circular reference but since the
settings allows iterations, no error message is shown. I.e.
IF(<condition>; <true>; <do nothing>) is simulated. This really solves
my problem and previous calculations remain for all times.
But with the above example is certainly not good programming practice
to distribute something that contains error and relies on that error
messages are not shown. Also I'm not sure how this solution will
behave in all/older versions of Excel.
*** PROBLEM 4: Functions Cannot Change Microsoft Excel Environment
According to Knowledge Base-article 170787
(http://support.microsoft.com/default.aspx?scid=kb;en-us;170787) by
functions you can't change Excels own environment.
I.e. it's not possible to change another cell's value.
Changing another cell values would solve my problem once and for all.
Then I could perform the following solution:
IF(<date is valid>; <move data to another hidden cell>; <not
important>)
All other calculations in the spreadsheet could then use these hidden
cells for their calculation. And these hidden cells would not be
changed after the time-limit period.
*** All this leads to my questions about other possible soultuions:
- Is it possible to write an IF-statement without a false part. I.e.
with a "do nothing" part?
OR…
- Is it possible in any way to simulate problem 3 in another way other
than circular references.
OR…
- Is there another way to simulate put operations (problem 4) in a
secure way?
OR…
- Is there another, different from my previous approach, solution to
my problem?
All help very much appreciated.
/Markus
I'm trying to solve a quite delicate problem.
I want to distribute a time-limited (locked with standard Excel
protection) spreadsheet that will stop working after a certain date.
After the time period is over, it's important that all previous
calculations and data remains, i.e. all formulas must stop working
after a certain date BUT without a loss of data!!! It must be possible
for users to archive their data in the same sheet/workbook. I.e.
already made calculations must remain at all times.
If users try to override the time-limitations by setting their system
clock to a previous date or if some users "crack" the protection
password it is not very important to me. Still, most users will have
to get a fresh copy of the spreadsheet from me.
*** PROBLEM 1: VBA is out of the question
Since it's easy to shut VBA off it is out of the question. Also, to
keep it as simple as possible, since many users don't even know how to
turn macros ON (in some versions and companies, excel protection
levels turn macros off automatically) the spreadsheet must not contain
any VBA code at all.
*** PROBLEM 2: An IF-formula is out of the question
Since IF-formulas contain a false-part by default they are not part of
the overall solution. The following statement will not work for me:
IF(<date is valid>; <perform calculation to this cell>; 0)
If the sheet is opened after expiration date, all previous
calculations will be overwritten. And I just want them to stop
working. It must be possible for users to review their old work.
If the IF-formula had an optional false-part it would have solved my
problem. Meaning that if the date is expired – do nothing.
*** PROBLEM 3: A Circular reference "should" not be used
I've tried to simulate an IF-formula without a false part using
circular reference.
Using the setting in Excel allowing iterations, no error messages are
shown when a circular reference occurs. It's the possible to write the
following formula:
IF(<date is valid>; <perform calculation to this cell>: <put value
from this cell>)
For example, a content in cell C3:
IF(<date is valid>; <perform calculation to this cell>; C3)
The above statement will generate a circular reference but since the
settings allows iterations, no error message is shown. I.e.
IF(<condition>; <true>; <do nothing>) is simulated. This really solves
my problem and previous calculations remain for all times.
But with the above example is certainly not good programming practice
to distribute something that contains error and relies on that error
messages are not shown. Also I'm not sure how this solution will
behave in all/older versions of Excel.
*** PROBLEM 4: Functions Cannot Change Microsoft Excel Environment
According to Knowledge Base-article 170787
(http://support.microsoft.com/default.aspx?scid=kb;en-us;170787) by
functions you can't change Excels own environment.
I.e. it's not possible to change another cell's value.
Changing another cell values would solve my problem once and for all.
Then I could perform the following solution:
IF(<date is valid>; <move data to another hidden cell>; <not
important>)
All other calculations in the spreadsheet could then use these hidden
cells for their calculation. And these hidden cells would not be
changed after the time-limit period.
*** All this leads to my questions about other possible soultuions:
- Is it possible to write an IF-statement without a false part. I.e.
with a "do nothing" part?
OR…
- Is it possible in any way to simulate problem 3 in another way other
than circular references.
OR…
- Is there another way to simulate put operations (problem 4) in a
secure way?
OR…
- Is there another, different from my previous approach, solution to
my problem?
All help very much appreciated.
/Markus