OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Can I autofill a unique key field?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
paulsen
General User
General User


Joined: 12 May 2011
Posts: 15
Location: United States

PostPosted: Fri Jun 15, 2012 5:34 am    Post subject: Can I autofill a unique key field? Reply with quote

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
View user's profile Send private message AIM Address
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jun 15, 2012 6:16 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
paulsen
General User
General User


Joined: 12 May 2011
Posts: 15
Location: United States

PostPosted: Fri Jun 15, 2012 6:47 am    Post subject: Reply with quote

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!" Very Happy
Back to top
View user's profile Send private message AIM Address
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jun 15, 2012 7:40 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jun 15, 2012 7:44 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
paulsen
General User
General User


Joined: 12 May 2011
Posts: 15
Location: United States

PostPosted: Fri Jun 15, 2012 8:03 am    Post subject: Reply with quote

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
View user's profile Send private message AIM Address
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jun 15, 2012 8:59 am    Post subject: Reply with quote

=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 https://forum.openoffice.org
Back to top
View user's profile Send private message
paulsen
General User
General User


Joined: 12 May 2011
Posts: 15
Location: United States

PostPosted: Fri Jun 15, 2012 5:15 pm    Post subject: Reply with quote

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
View user's profile Send private message AIM Address
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group