Method to assign an identifying number if entry. Help please!

M

mkmed

New Member
Joined
May 14, 2007
Messages
31
Hello all,I am planning out a spreadsheet that will involve multiple sheets of entries. The first worksheet will be a filtered regurgitation of all the sheets. I want each item in the entire workbook to have a unique identification number. What would be the best way to automatically assign an ID number (column A would be fine) everytime a row of details about an item is created. This information will be on many sheets. I am stumped on this issue and any help is greatly appreciated. As an added challenge I don’t understand VBA. Maybe an add-in? Thanks in advance!

Mark
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Can you give a few examples of what your data would look like, and how you picture the ID looking like?
Im thinking that something like a COUNTIF might work here
 
Upvote 0
FDibbins said:
Can you give a few examples of what your data would look like, and how you picture the ID looking like?
Im thinking that something like a COUNTIF might work here
Click to expand...



I am on my tablet at the moment and no way to make an example. Let me attempt a better explanation. the spreadsheet will be for an inventory of collectibles. Each sheet will House a specific kind of collectibles. Example: sheet 2 is toys. Sheet 3 is cards. Sheet 4 is models. Sheet 5 is music instruments. Etc...

Each sheet will have the same format. Row 1 is a header explaining what items the sheet housed. Row 2-how ever many are possessed, are details about that item. (Column A is where I want the identification #) column b would be description. Column c would be condition. Column d would be Value. Column e would be notes. Each page will be formatted exactly alike.

Using the sheet. Assume there are currently 5,000 entries (indentification #’s used to this point 1-5,000). Now more inventory needs to be added. For instance an item on sheet 2 and an item on sheet 6. When the item begins being input on sheet 2, the corresponding cell in column A inserts the number to 5001. Then I begin entering an item on sheet 6. Once I begin entering the item, column A of the corresponding row populates as 5002.

The first sheet in the workbook will just be a full and complete regurgitation of all the sheets.

Hopefully that will give a better understanding of the goal. Thank you for any and all help!

Mark
 
Upvote 0
.
Here is one suggestion. This numbering layout makes it easy to create any number of additional ID's simply by highlighting the last ID NUMBER then dragging down Col A :


[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[TH]
E​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1​
[/TD]
[TD]ID[/TD]
[TD]DESCRIPTION[/TD]
[TD]CONDITION[/TD]
[TD]VALUE[/TD]
[TD]NOTES[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2​
[/TD]
[TD]TOY-0001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3​
[/TD]
[TD]TOY-0002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4​
[/TD]
[TD]TOY-0003[/TD]
[TD="bgcolor: #FFFF00"]This is on the TOYS sheet[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5​
[/TD]
[TD]TOY-0004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7​
[/TD]
[TD]ID[/TD]
[TD]DESCRIPTION[/TD]
[TD]CONDITION[/TD]
[TD]VALUE[/TD]
[TD]NOTES[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
8​
[/TD]
[TD]CARD-0001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
9​
[/TD]
[TD]CARD-0002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
10​
[/TD]
[TD]CARD-0003[/TD]
[TD="bgcolor: #FFFF00"]This is on the CARDS sheet[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
11​
[/TD]
[TD]CARD-0004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
13​
[/TD]
[TD]ID[/TD]
[TD]DESCRIPTION[/TD]
[TD]CONDITION[/TD]
[TD]VALUE[/TD]
[TD]NOTES[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
14​
[/TD]
[TD]MUS-0001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
15​
[/TD]
[TD]MUS-0002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
16​
[/TD]
[TD]MUS-0003[/TD]
[TD="bgcolor: #FFFF00"]This is on the MUSICAL INSTRUM sheet[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
17​
[/TD]
[TD]MUS-0004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
18​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
​
 
Last edited:
Upvote 0
Logit said:
.
Here is one suggestion. This numbering layout makes it easy to create any number of additional ID's simply by highlighting the last ID NUMBER then dragging down Col A :


[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[TH]
E​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1​
[/TD]
[TD]ID[/TD]
[TD]DESCRIPTION[/TD]
[TD]CONDITION[/TD]
[TD]VALUE[/TD]
[TD]NOTES[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2​
[/TD]
[TD]TOY-0001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3​
[/TD]
[TD]TOY-0002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4​
[/TD]
[TD]TOY-0003[/TD]
[TD="bgcolor: #FFFF00"]This is on the TOYS sheet[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5​
[/TD]
[TD]TOY-0004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7​
[/TD]
[TD]ID[/TD]
[TD]DESCRIPTION[/TD]
[TD]CONDITION[/TD]
[TD]VALUE[/TD]
[TD]NOTES[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
8​
[/TD]
[TD]CARD-0001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
9​
[/TD]
[TD]CARD-0002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
10​
[/TD]
[TD]CARD-0003[/TD]
[TD="bgcolor: #FFFF00"]This is on the CARDS sheet[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
11​
[/TD]
[TD]CARD-0004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
13​
[/TD]
[TD]ID[/TD]
[TD]DESCRIPTION[/TD]
[TD]CONDITION[/TD]
[TD]VALUE[/TD]
[TD]NOTES[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
14​
[/TD]
[TD]MUS-0001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
15​
[/TD]
[TD]MUS-0002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
16​
[/TD]
[TD]MUS-0003[/TD]
[TD="bgcolor: #FFFF00"]This is on the MUSICAL INSTRUM sheet[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
17​
[/TD]
[TD]MUS-0004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
18​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
​
Click to expand...


Thank you for the suggestion and that is my plan if I can’t accomplish the goal. I really want each item to be 100% numeric. Thank you for your suggestion though.

Mark
 
Upvote 0
.
Establish a system of numbers to represent TOY, MUSIC, CARD, etc.

Ex:

TOY = 444 The id's would be: 44400001, 44400002, etc.

MUSIC = 555 55500001, 55500002, etc.

CARD = 333 33300001, 33300002, etc.

You will still be able to highlight the last used cell in Col A and drag down to create additional ID numbers.
 
Upvote 0
I agree with Logit that the best way would be to assign each department its own reference then it is useful for quick referencing but if you insist on it being completely numerical without department identification then...

If your sheets are always the same i.e. a new sheet is not created regularly you could use the following:

I am assuming the references are in column A and the name of the item in column B

On sheet2 (as sheet1 is your entire inventory) in A1 put the first item as 1

Underneath in A2 use the simple yet effective =IF(B2="","",A1+1)
Drag this down as far as you like, it will only number it when B is populated.

Copy sheet2 column A and paste to sheet3 column A

Change cell A1 to =MAX('sheet2'!A:A)+1

Repeat for every sheet and make sure you remember to change the sheet reference.

It's a fairly slow process but if you only have 20 sheets it should only take a few minutes.

The major downside is that the item code is subject to change if an item is added on a previous sheet. As an accountant who understands the importance of a completely unique reference I would not use this method.
 
Last edited:
Upvote 0
Thank you both for your input. And yes no eyes, that would be an issue as this will be a living document and there is no telling when, or where, items will be added.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
210
Lux Aeterna
L
B
  • Question Question
Macro to Assign "UniqueID's" to Judges tables for a competition
Replies
1
Views
340
BeerMan23
B
B
  • Question Question
Sum child cells and allow reference to higher cell within Table
Replies
7
Views
156
babyjwhale
B
D
  • Question Question
VBA Basic Userform - add -update -delete
Replies
3
Views
685
Drexi
D
R
  • Question Question
Word to Excel - Not formatting correctly - Can someone help?
Replies
6
Views
333
rgillis32
R

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top