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: Frank Kabel (frank.kabel_at_freenet.de)
Date: 04/19/04 

Date: Mon, 19 Apr 2004 19:40:52 +0200

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

--
Regards
Frank Kabel
Frankfurt, Germany
MW wrote:
> 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