chicken and egg Tables

[LisaGreen]

Hi,

I have a table that with a date column.
When entering a new line at the bottom I'd like todays date to be automatically put in the new date column cell and not alter.
I understand I probably need to use circular references enabled and so on.
No matter how I try I end up with 0.. Jan 1st 1900.

If anyone can help I'd really apprciate it.

TIA
Lisa

[HansV]

Would VBA be OK? If so, you can use the Worksheet_Change event procedure to enter the current date as a fixed value in the appropriate column,

[LisaGreen]

Hello Hans,

Unfortunately it must be a plain xlsx without VBA. Apparently it's a security issue wether it actually is or not. Because of thati'M not allowed to send data and so on. bugrit!

Lisa

[HansV]

In that case, you can indeed use a circular reference, but you'll have to instruct all users to do the following:

- Select File > Options.
- Select Formulas in the navigation pane on the left.
- Tick the check box 'Enable iterative calculation'.
- Click OK.

Since macros aren't allowed, you cannot turn this on for them, so they have to do it themselves.

See the attached sample workbook for a demonstration of the formula needed. You may notice that the workbook is slow to react to changes. That is a result of the circular references.

DateStamp.xlsx

[LisaGreen]

Thank you Hans!!

Lisa

[ChrisGreaves]

See the attached sample workbook for a demonstration of the formula needed. You may notice that the workbook is slow to react to changes. That is a result of the circular references.

Hans, I arrive late to the party ...

I downloaded the DateStamp.xslx and opened it in Excel2003.
I could not get the two rows to either make a date or show a date other than 1990.
I set the Tools, Options for MaxIteration=1,000 and MaxChange=100. and confess to being unsure of exctly what they do, not having used circular references before.

Untitled.png

I then examined the formulas, and decided that the switches in B2:D2 were a bit of a red-herring; they merely controlled whether the circular reference was to be instituted.
Hence my own formula in cell E5. (cell E4 is just =NOW() to confirm that there is indeed a value for that function)
cell E5 says "if I am not null, then institute NOW(), otherwise leave the cell as it is", which is, of course, NOW() - in theory.

Frankly I am surprised that Excel2003 allows and encourages circular references, as evidenced by the Help screens "Make a circular reference work by changing the number of times Microsoft Excel iterates formulas"

I am happy that the so-called dates show as the zeroth of January 1990, for that is a facsimile of a zero-date.

Question: Did the datestamp.XLSX actually return today's date for you, and did it retain that date when you opened the workbook the next day?
Thanks, Chris

[HansV]

The .xlsx workbook uses a feature introduced in Excel 2007: a table (aka listobject). If a column of a table contains the "same" formula in each row, that formula will automatically be propagated to each row.
The .xlsx workbook works correctly for me (after closing and opening too).

You have to tick the Iteration check box before entering data.
Try setting Maximum iterations to 1 and Maximum change to 0.0001.

Here is your .xls workbook with the table removed, and the data cleared (but not the formulas in E2:E31).

DateStamp.xls

[ChrisGreaves]

The .xlsx workbook uses a feature introduced in Excel 2007:

[Thank you, Hans. I shall sign this reply "More confused then before" of Bonavista.
As I understand it. you have used a version of Excel2007 or later to open my Excxel2003 workbook, worked your magic, and then saved your enhanced workbook in Excel2003 format.

Untitled.png

I open your 2007 version of my workbook in 2003, tick the Iteration check box, and enter data.
The date of today (66 years since we landed in Western Australia; easy to remember) appears in the cells E2, E3 and E4.
So far so good.

I learn from the 2003 help files that Excel2003 has tables (I think that these use array formulas like the classic example of using Excel to predict the selling price of your house), so 2007 can set up a table and pass it to me, and the circular references work in 2003 BUT I cannot build that functionality in my copy of 2003.

Untitled2.png

I don't have to wait for tomorrow; I can set the cell formats to Time, enter data and tap the <F9> key and see that the formula works.

So the functionality for circular references in tables exists in 2003, but the mechanism (user interface?) for setting it up is not present in 2003.
Is that correct?
(signed) "More confused then before" of Bonavista.

[HansV]

The .xls workbook that I attached does not contain a table/list. I simply entered the formula in E2:E31. It will return a blank until you enter something in columns A to D. The formula will then return the date (or time) as it was then, and it should remain unchanged.

[LisaGreen]

Good stuff you guys!!

Lisa

[ChrisGreaves]

The .xls workbook that I attached does not contain a table/list. I simply entered the formula in E2:E31. It will return a blank until you enter something in columns A to D. The formula will then return the date (or time) as it was then, and it should remain unchanged.

Thank you, Hans.

Untitled.png

I have this morning managed to craft, from scratch, an Excel2003 example that works (attached)
I remain flummoxed that Excel permits this which is, I concede, a valid example of an ostensibly illegal construction from the mathematical point of view, nothing at all to do with computing systems.
So I remain "on guard".
A programming solution in VBA would see us using an IF/THEN/ELSE statement; but we are (here) not allowed to use VBA.
So the If-logic has been embedded within the spreadsheet calculation in the form of two nested If-functions.

Untitled2.png

Back to my early days when I was faced with a DOS Lotus2.1 circular reference. I avoided that with ten rows of a function which was a bit of a Newton-Raphson method with a test between the ninth and tenth rows to see if the two values were within a tolerance ("fuzz") level. If they were the result was used, if not, an error was signaled.

I think that in Excel terms my number of rows (10) corresponds to Excel's Maximum Iterations, and my tolerance corresponds to Excel's Maximum Change.

I see that this (attached) solution works; it works by compressing my ten-row efforts into a single cell. But it is still recognised by Excel as a circular reference, and for me the danger is that a user on seeing "Circular Reference" will dismiss the status bar saying "Oh, thats OK; I know what causes that" and will miss the other circular reference(s) which are illegal.

Lisa: Are you able to construct a solution like this on your system from scratch, without Edit-Copying cell contents from any attached worksheet?

ALL: Is anyone in a position to duplicate this method in Lotus, QuattroPro, or any other Windows-based spreadsheet processor?

Grudgingly yours, Chris

[HansV]

VBA allows a statement such as

x = x +1

which is mathematically incorrect too...

[ChrisGreaves]

VBA allows a statement such as
x = x +1
which is mathematically incorrect too...

Hans; I thought that you were better at VBA than am I

Code: Select all

Sub test()
    Dim x As Long
    x = 0
    x = x + 1
    MsgBox x
End Sub

In VBA, the statement "x=x+1" in context is an assignment statement.

In most of his "guide to" books Daniel Delbert McCracken had to go to lengths to explain to his readers that in BASIC, FORTRAN, COBOL and similar languages, the equals-sign was used as an assignment operator!
Yours historically,
Chris
P.S. Mathematically, x=x+1 is correct when the value of x is supposed to be zero. C

[HansV]

So 0 = 1 ?

[LisaGreen]

Chris,

>Lisa: Are you able to construct a solution like this on your system from scratch, without Edit-Copying cell contents from any attached worksheet?

No I wasn't.
I was going to take the cowards way out and code it somewhere else.. perhaps in python.. and put the date in that way. That or try and persuade the client to allow VBA by wowing her with what vba can do!!

It's strange in a way that a lot of people will allow python and other languages.. vbscript comes to mind.. to update things and not vba.

I'm going to have a look at this solution though. Maybe I can avoid being a coward.. for now!

And thank you for looking at it Chris and Hans!!

Hugs
Lisa

[LisaGreen]

Chris,

I cut my fortran teeth with newton-raphson!!

Lisa

[ChrisGreaves]

I cut my fortran teeth with newton-raphson!!

Lordy Lord! I dIdn't realize that you were that old!
Cheers, Chris

[ChrisGreaves]

I'm going to have a look at this solution though. Maybe I can avoid being a coward.. for now!

Lisa, I think that you should still be scared, at least a little bit.
The "Iteration", "Maximum Iterations" and "Maximum change" settings are application-wide, and, as Hans has pointed out, will have to be set manually (and correctly) by each user on each of their machines (office and home) for your specific application in terms of recording a date/time.

If you get carried away at thumbing your nose (grin!) at mathematics, and decide to use this technique for a second circular reference - say a Newton Raphson calculation of a root of a quadratic equation, even in a different workbook - then those three global settings can cause a conflict.

That is, it seems to me that the Excel solution is a fudge implemented to allow users limited access to circular references for one specific solution within a spreadsheet on one specific machine. I shudder at what might have happened in 1988 had we insisted that multiple computers in 750+ offices across North America had been required to change their Tools Options "Maximum change" settings to four decimal places.

If your application demands time-stamps for different events - a series of worksheets to log the transactions against a specific work order (NOW() when first reported, when the object arrives, when dis-assembled, when resolved, when fixed, when re-assembled and tested, when packed and sent out, when confirmation received from the client) - then the global settings would work for those functionally-related eight events.

Your scenario is part of a larger field, that looks at fixing values produced by calculations.
Spreadsheets work by (1) accepting constant values of data and (2) forming dynamic results based on those fixed data.
Mostly the dynamic results arrive by spreadsheet formulas of the form "tax=taxrate*income", but in some cases the results are time-dependent as in "FirstReported=NOW()".

I am still pondering whether it makes any sense at all to allow conversion of a cell on a one-way passage from dynamic to fixed.
Spreadsheets do allow the user to effect this, with Edit, Copy and then Edit, paste Special.

Back to your case: You are able to shift the responsibility of the user keying in a fixed date, by automating the date, but requiring the user to key in, in a separate cell, a data-switch which triggers the setting of the date. An alternative solution would be to require the user to pasteSpecial the automated date value. By using a data switch of some form, the user's workload is decreased, but some form of user action will always be required to produce a fixed value for the date.
Cheers, Chris

[LisaGreen]

Chris,

I have a meeting with the client tomorrow.. aaaand .. I'm building a pretty user form to show her. Nothing complex just data input and validation. I'm hoping to persuade her to go in that direction. I'm taking a hammer with me and a couple of rubber ducks.

Having said that I'm taking what you said onboard. All good considerations!! Isn't it amazing that such a small thing could cause so much thought!!!

Thank you for looking at it.. appreciate it a **lot**.

Hugs
Lisa

[ChrisGreaves]

Isn't it amazing that such a small thing could cause so much thought!!!

Lisa, best of good wishes for tomorrow; you don't need luck.
As far as a small thing goes, you know me by now. Give me an inch and I'll take 1.57828E-05 of a mile.
Cheers, Chris