| View previous topic :: View next topic |
| Author |
Message |
paulsen
General User
Joined: 12 May 2011
Posts: 15
Location: United States
|
 Posted: Fri Jun 15, 2012 5:34 am Post subject: Can I autofill a unique key field? |
 |
|
I have a common (I suspect) situation. Every row in a certain spreadsheet needs to have a unique key field. I would like to fill that cell in automatically with a unique number whenever I add a new row. Probably an incrementing sequence is simplest.
Seems like any formula I used would always recalculate but I want the number to remain static once set. Perhaps this is a matter of programming a macro, or something, but I have not taken up that challenge yet. Is it time for me to learn?
I do have a satisfactory workaround, but I am aiming for perfection! The workaround is a cell that gives me 1 + MAX(KEYS) where KEYS is the data range of the keys. I labeled that cell "Next Key". Very workable but it does not protect me from mistakes (e.g. a typo putting in a duplicate key).
Bob |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jun 15, 2012 6:16 am Post subject: |
|
|
If you were aiming for perfection you would use a database where such auto-keys are a matter of course. You define them and forget about them.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
paulsen
General User
Joined: 12 May 2011
Posts: 15
Location: United States
|
| Posted: Fri Jun 15, 2012 6:47 am Post subject: |
|
|
| Villeroy wrote: | | If you were aiming for perfection you would use a database where such auto-keys are a matter of course. You define them and forget about them. |
I presume you mean a "real" database management system and that is way beyond overkill in my case.
In addition to "perfection" I should have also mentioned "simplicity!"  |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jun 15, 2012 7:40 am Post subject: |
|
|
I use real RDBMs for most simple phone lists and there is never any overkill involved. The lists are so much easier to maintain by anybody on the same network. Such lists are even easier to use in spreadsheets that any "native" spreadsheet list.
If you want auto-IDs then you want to lookup unique row references in a spreadsheet. That is overkill. And there are many reasons why this may fail.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jun 15, 2012 7:44 am Post subject: |
|
|
Insert>Names>Define...
Name: N
Refers to: MAX($A$1:$A$99)+1
[Add], [OK]
Now insert a new row and type:
= N F9 Enter Enter
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
paulsen
General User
Joined: 12 May 2011
Posts: 15
Location: United States
|
| Posted: Fri Jun 15, 2012 8:03 am Post subject: |
|
|
| Villeroy wrote: | Insert>Names>Define...
Name: N
Refers to: MAX($A$1:$A$99)+1
[Add], [OK]
Now insert a new row and type:
= N F9 Enter Enter |
Almost works.
The "=N" gets me Err:522 if I enter the data within the covered range of cells. If I enter "=N" outside the covered range of cells it is then not taken into account when additional rows are added. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jun 15, 2012 8:59 am Post subject: |
|
|
=N [F9 ] [Enter] [Enter]
which puts formula "=N", F9 calculates the formula result without leaving the input mode, the first enter replaces the formula with the result as displayed in a tool tip, the second enter actually enters the constant value.
In the time we wrote this thread I would have created a relational database with a couple of interrelated lists with auto-IDs, foreign keys and everything.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
paulsen
General User
Joined: 12 May 2011
Posts: 15
Location: United States
|
| Posted: Fri Jun 15, 2012 5:15 pm Post subject: |
|
|
| Villeroy wrote: | =N [F9 ] [Enter] [Enter]
which puts formula "=N", F9 calculates the formula result without leaving the input mode, the first enter replaces the formula with the result as displayed in a tool tip, the second enter actually enters the constant value.
In the time we wrote this thread I would have created a relational database with a couple of interrelated lists with auto-IDs, foreign keys and everything. |
Oops! Missed that F9 somehow. |
|
| Back to top |
|
|
|
