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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Vasant Nanavati (vasantn)
Date: 04/19/04 

Date: Mon, 19 Apr 2004 10:22:08 -0400

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. 

-- 
Vasant
"MW" <markus.wikman@home.se> wrote in message
news:25001185.0404190316.4fd5d4a@posting.google.com...
> Hi!
>
> I'm trying to solve a quite delicate problem.
>
> I want to distribute a time-limited (locked with standard Excel
> protection) spread*** 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 ***/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 spread*** 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 spread*** 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 *** 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 spread*** 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
Quantcast