If-formula without false part / put in cell operations / time limited spreadsheet

[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

 

[BrianB]

<<: VBA is out of the question>> ???

I think this will be the only way to go.
You could hide your worksheets so that nothing will be seen unles
macros are enabled.

You would need to make sure with a Before_Save macro that sheets ar
hidden again.
Doesn't seem a big problem

 

[Vasant Nanavati]

Don't waste your time. There is no way to do what you want without VBA, and
even then it would not be foolproof. If you want security, don't use Excel.

 

[Frank Kabel]

Hi
not possible without VBA. So if this is out of the question no chance
to achieve your goal

 

[MW]

Hi!

Thanks for all the help! It seems there is no need for me to keep on
looking for another solution.

As I mentioned before, VBA is out of the question, it would be very
nice to perform this easy put-value-operation without using VBA at
all. The problem for me is, since the subscribers of the sheet varies
very much in technical competence, I just cant be sure users would
even bother using my sheet because their lack of competence. They
don't "know" how to set their security level to (less to or) equal to
normal level and the result might be that the sheet might become
unused since it's to "tricky".

So, in the future I might require a formal certificate and then sign
the file (macros). Then VBA is a GO.

Since yesterday, I've tested on several different systems and it seems
like my solution will include a Circular reference to simulate an
IF-formula without a false part, combined with the setting allowing
iterations in Excel (current document). If problem occurs I'll swap
for the solution of pasting formulas in cells from VBA when document
opens.

Thanks
Markus