Auto-Increment if Column Already Contains a Certain Value
up vote
0
down vote
favorite
I have a question on how to best accomplish auto incrementation in Excel.
In my sheet, I have two Columns - Column A, which is my ID, and Column E, which is my Workstream (Columns B-D were hidden for ease of read). The Workstream column is a drop-down and contains multiple values.
Currently, my ID column (Column A) is based on the following formula:
(My data starts in Row 17)
=IF(E17="","",E17 & "-000-"& TEXT(ROW()-16,"000"))
Here's where it gets tricky for me. I will explain it as best as I can.
What I'm looking to do is alter my formula (or maybe use VBA?) to auto increment, based upon if a value exists in Column A.
If Column A already contains WM-000-001 in Row 17, and a user selects the WM option from the drop-down in Column E, in say Row 19, Column A will automatically populate WM-000-002 in Row 19.
I've also been trying to accomplish the same thing in VBA using Worksheet_Change(ByVal Target As Range), but am still running into a roadblock, as I'm not sure how to correctly code the formula.
If there is an easier way to accomplish this, or if anyone could nudge me in the right direction, it would be much appreciated!
excel vba
edited Nov 9 at 18:00
Mathieu Guindon
39.7k762138
asked Nov 9 at 17:30
user7735684
104
add a comment |
up vote
0
down vote
favorite
I have a question on how to best accomplish auto incrementation in Excel.
In my sheet, I have two Columns - Column A, which is my ID, and Column E, which is my Workstream (Columns B-D were hidden for ease of read). The Workstream column is a drop-down and contains multiple values.
Currently, my ID column (Column A) is based on the following formula:
(My data starts in Row 17)
=IF(E17="","",E17 & "-000-"& TEXT(ROW()-16,"000"))
Here's where it gets tricky for me. I will explain it as best as I can.
What I'm looking to do is alter my formula (or maybe use VBA?) to auto increment, based upon if a value exists in Column A.
If Column A already contains WM-000-001 in Row 17, and a user selects the WM option from the drop-down in Column E, in say Row 19, Column A will automatically populate WM-000-002 in Row 19.
I've also been trying to accomplish the same thing in VBA using Worksheet_Change(ByVal Target As Range), but am still running into a roadblock, as I'm not sure how to correctly code the formula.
If there is an easier way to accomplish this, or if anyone could nudge me in the right direction, it would be much appreciated!
excel vba
edited Nov 9 at 18:00
Mathieu Guindon
39.7k762138
asked Nov 9 at 17:30
user7735684
104
3
Sounds like a job for a relational database.
– Comintern
Nov 9 at 17:47
Is there any sort of workaround?
– user7735684
Nov 9 at 18:02
4
If you're trying to create a relational database in something that isn't a relational database, everything will be a workaround.
– Comintern
Nov 9 at 18:03
1
sure there is
– Mathieu Guindon
Nov 9 at 18:09
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a question on how to best accomplish auto incrementation in Excel.
In my sheet, I have two Columns - Column A, which is my ID, and Column E, which is my Workstream (Columns B-D were hidden for ease of read). The Workstream column is a drop-down and contains multiple values.
Currently, my ID column (Column A) is based on the following formula:
(My data starts in Row 17)
=IF(E17="","",E17 & "-000-"& TEXT(ROW()-16,"000"))
Here's where it gets tricky for me. I will explain it as best as I can.
What I'm looking to do is alter my formula (or maybe use VBA?) to auto increment, based upon if a value exists in Column A.
If Column A already contains WM-000-001 in Row 17, and a user selects the WM option from the drop-down in Column E, in say Row 19, Column A will automatically populate WM-000-002 in Row 19.
I've also been trying to accomplish the same thing in VBA using Worksheet_Change(ByVal Target As Range), but am still running into a roadblock, as I'm not sure how to correctly code the formula.
If there is an easier way to accomplish this, or if anyone could nudge me in the right direction, it would be much appreciated!
excel vba
edited Nov 9 at 18:00
Mathieu Guindon
39.7k762138
asked Nov 9 at 17:30
user7735684
104
I have a question on how to best accomplish auto incrementation in Excel.
In my sheet, I have two Columns - Column A, which is my ID, and Column E, which is my Workstream (Columns B-D were hidden for ease of read). The Workstream column is a drop-down and contains multiple values.
Currently, my ID column (Column A) is based on the following formula:
(My data starts in Row 17)
=IF(E17="","",E17 & "-000-"& TEXT(ROW()-16,"000"))
Here's where it gets tricky for me. I will explain it as best as I can.
What I'm looking to do is alter my formula (or maybe use VBA?) to auto increment, based upon if a value exists in Column A.
If Column A already contains WM-000-001 in Row 17, and a user selects the WM option from the drop-down in Column E, in say Row 19, Column A will automatically populate WM-000-002 in Row 19.
I've also been trying to accomplish the same thing in VBA using Worksheet_Change(ByVal Target As Range), but am still running into a roadblock, as I'm not sure how to correctly code the formula.
If there is an easier way to accomplish this, or if anyone could nudge me in the right direction, it would be much appreciated!
excel vba
excel vba
edited Nov 9 at 18:00
Mathieu Guindon
39.7k762138
asked Nov 9 at 17:30
user7735684
104
edited Nov 9 at 18:00
Mathieu Guindon
39.7k762138
asked Nov 9 at 17:30
user7735684
104
edited Nov 9 at 18:00
Mathieu Guindon
39.7k762138
edited Nov 9 at 18:00
Mathieu Guindon
39.7k762138
edited Nov 9 at 18:00
Mathieu Guindon
39.7k762138
39.7k762138
asked Nov 9 at 17:30
user7735684
104
asked Nov 9 at 17:30
user7735684
104
asked Nov 9 at 17:30
user7735684
104
104
3
Sounds like a job for a relational database.
– Comintern
Nov 9 at 17:47
Is there any sort of workaround?
– user7735684
Nov 9 at 18:02
4
If you're trying to create a relational database in something that isn't a relational database, everything will be a workaround.
– Comintern
Nov 9 at 18:03
1
sure there is
– Mathieu Guindon
Nov 9 at 18:09
add a comment |
3
Sounds like a job for a relational database.
– Comintern
Nov 9 at 17:47
Is there any sort of workaround?
– user7735684
Nov 9 at 18:02
4
If you're trying to create a relational database in something that isn't a relational database, everything will be a workaround.
– Comintern
Nov 9 at 18:03
1
sure there is
– Mathieu Guindon
Nov 9 at 18:09
3
3
Sounds like a job for a relational database.
– Comintern
Nov 9 at 17:47
Sounds like a job for a relational database.
– Comintern
Nov 9 at 17:47
Is there any sort of workaround?
– user7735684
Nov 9 at 18:02
Is there any sort of workaround?
– user7735684
Nov 9 at 18:02
4
4
If you're trying to create a relational database in something that isn't a relational database, everything will be a workaround.
– Comintern
Nov 9 at 18:03
If you're trying to create a relational database in something that isn't a relational database, everything will be a workaround.
– Comintern
Nov 9 at 18:03
1
1
sure there is
– Mathieu Guindon
Nov 9 at 18:09
sure there is
– Mathieu Guindon
Nov 9 at 18:09
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
Use COUNTIF() to do the numbering:
=IF(E17="","",E17 & "-000-"& TEXT(COUNTIF($A$1:A16,E17&"-*")+1,"000"))
answered Nov 9 at 17:34
Scott Craner
86.8k82449
1
Kindly remember that use of such formula can lead to future errors if used for IDs, instead create one static id column for id as value and one for this formula, when you get a new id, paste it as value in the static id column, this will help you in future, because one wrong entry can update all the ids of all your rows and any linked formulae with ids will be messed up
– usmanhaq
Nov 9 at 17:51
2
@user7735684 Excel isn't made for this. Using Excel as a relational database is a doomed endeavor.
– Mathieu Guindon
Nov 9 at 17:58
1
@user7735684 see edit, but take to heart what the others are saying, moving to a relational database would be better. While Excel can do a lot of what a database can it is too easy for the user to mess it up.
– Scott Craner
Nov 9 at 18:07
1
@ScottCraner, thank you so much, and I will have that conversation to begin moving data over to a relational database. Now I know where Excel is limited, I can work to better educate others!
– user7735684
Nov 9 at 18:11
4
@user7735684 the biggest problem with a computed ID is that it's mutable, so if you delete a row, IDs can change for existing records, and then anything that was using them for lookups will now be pulling the wrong record - and then you add/insert a new record and the IDs change again, and now everything is mangled beyond repair. Or a user comes in and sorts the data and mangles everything all over again. If you get IT's cooperation, have SQL Server set up. If you don't, have your data live in an Access database (which will easily handle immutable IDs) - not in Excel.
– Mathieu Guindon
Nov 9 at 18:17
|
show 3 more comments
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
Use COUNTIF() to do the numbering:
=IF(E17="","",E17 & "-000-"& TEXT(COUNTIF($A$1:A16,E17&"-*")+1,"000"))
answered Nov 9 at 17:34
Scott Craner
86.8k82449
1
Kindly remember that use of such formula can lead to future errors if used for IDs, instead create one static id column for id as value and one for this formula, when you get a new id, paste it as value in the static id column, this will help you in future, because one wrong entry can update all the ids of all your rows and any linked formulae with ids will be messed up
– usmanhaq
Nov 9 at 17:51
2
@user7735684 Excel isn't made for this. Using Excel as a relational database is a doomed endeavor.
– Mathieu Guindon
Nov 9 at 17:58
1
@user7735684 see edit, but take to heart what the others are saying, moving to a relational database would be better. While Excel can do a lot of what a database can it is too easy for the user to mess it up.
– Scott Craner
Nov 9 at 18:07
1
@ScottCraner, thank you so much, and I will have that conversation to begin moving data over to a relational database. Now I know where Excel is limited, I can work to better educate others!
– user7735684
Nov 9 at 18:11
4
@user7735684 the biggest problem with a computed ID is that it's mutable, so if you delete a row, IDs can change for existing records, and then anything that was using them for lookups will now be pulling the wrong record - and then you add/insert a new record and the IDs change again, and now everything is mangled beyond repair. Or a user comes in and sorts the data and mangles everything all over again. If you get IT's cooperation, have SQL Server set up. If you don't, have your data live in an Access database (which will easily handle immutable IDs) - not in Excel.
– Mathieu Guindon
Nov 9 at 18:17
|
show 3 more comments
up vote
1
down vote
accepted
Use COUNTIF() to do the numbering:
=IF(E17="","",E17 & "-000-"& TEXT(COUNTIF($A$1:A16,E17&"-*")+1,"000"))
answered Nov 9 at 17:34
Scott Craner
86.8k82449
1
Kindly remember that use of such formula can lead to future errors if used for IDs, instead create one static id column for id as value and one for this formula, when you get a new id, paste it as value in the static id column, this will help you in future, because one wrong entry can update all the ids of all your rows and any linked formulae with ids will be messed up
– usmanhaq
Nov 9 at 17:51
2
@user7735684 Excel isn't made for this. Using Excel as a relational database is a doomed endeavor.
– Mathieu Guindon
Nov 9 at 17:58
1
@user7735684 see edit, but take to heart what the others are saying, moving to a relational database would be better. While Excel can do a lot of what a database can it is too easy for the user to mess it up.
– Scott Craner
Nov 9 at 18:07
1
@ScottCraner, thank you so much, and I will have that conversation to begin moving data over to a relational database. Now I know where Excel is limited, I can work to better educate others!
– user7735684
Nov 9 at 18:11
4
@user7735684 the biggest problem with a computed ID is that it's mutable, so if you delete a row, IDs can change for existing records, and then anything that was using them for lookups will now be pulling the wrong record - and then you add/insert a new record and the IDs change again, and now everything is mangled beyond repair. Or a user comes in and sorts the data and mangles everything all over again. If you get IT's cooperation, have SQL Server set up. If you don't, have your data live in an Access database (which will easily handle immutable IDs) - not in Excel.
– Mathieu Guindon
Nov 9 at 18:17
|
show 3 more comments
up vote
1
down vote
accepted
up vote
1
down vote
accepted
Use COUNTIF() to do the numbering:
=IF(E17="","",E17 & "-000-"& TEXT(COUNTIF($A$1:A16,E17&"-*")+1,"000"))
answered Nov 9 at 17:34
Scott Craner
86.8k82449
Use COUNTIF() to do the numbering:
=IF(E17="","",E17 & "-000-"& TEXT(COUNTIF($A$1:A16,E17&"-*")+1,"000"))
answered Nov 9 at 17:34
Scott Craner
86.8k82449
edited Nov 9 at 18:05
answered Nov 9 at 17:34
Scott Craner
86.8k82449
answered Nov 9 at 17:34
Scott Craner
86.8k82449
answered Nov 9 at 17:34
Scott Craner
86.8k82449
86.8k82449
1
Kindly remember that use of such formula can lead to future errors if used for IDs, instead create one static id column for id as value and one for this formula, when you get a new id, paste it as value in the static id column, this will help you in future, because one wrong entry can update all the ids of all your rows and any linked formulae with ids will be messed up
– usmanhaq
Nov 9 at 17:51
2
@user7735684 Excel isn't made for this. Using Excel as a relational database is a doomed endeavor.
– Mathieu Guindon
Nov 9 at 17:58
1
@user7735684 see edit, but take to heart what the others are saying, moving to a relational database would be better. While Excel can do a lot of what a database can it is too easy for the user to mess it up.
– Scott Craner
Nov 9 at 18:07
1
@ScottCraner, thank you so much, and I will have that conversation to begin moving data over to a relational database. Now I know where Excel is limited, I can work to better educate others!
– user7735684
Nov 9 at 18:11
4
@user7735684 the biggest problem with a computed ID is that it's mutable, so if you delete a row, IDs can change for existing records, and then anything that was using them for lookups will now be pulling the wrong record - and then you add/insert a new record and the IDs change again, and now everything is mangled beyond repair. Or a user comes in and sorts the data and mangles everything all over again. If you get IT's cooperation, have SQL Server set up. If you don't, have your data live in an Access database (which will easily handle immutable IDs) - not in Excel.
– Mathieu Guindon
Nov 9 at 18:17
|
show 3 more comments
1
Kindly remember that use of such formula can lead to future errors if used for IDs, instead create one static id column for id as value and one for this formula, when you get a new id, paste it as value in the static id column, this will help you in future, because one wrong entry can update all the ids of all your rows and any linked formulae with ids will be messed up
– usmanhaq
Nov 9 at 17:51
2
@user7735684 Excel isn't made for this. Using Excel as a relational database is a doomed endeavor.
– Mathieu Guindon
Nov 9 at 17:58
1
@user7735684 see edit, but take to heart what the others are saying, moving to a relational database would be better. While Excel can do a lot of what a database can it is too easy for the user to mess it up.
– Scott Craner
Nov 9 at 18:07
1
@ScottCraner, thank you so much, and I will have that conversation to begin moving data over to a relational database. Now I know where Excel is limited, I can work to better educate others!
– user7735684
Nov 9 at 18:11
4
@user7735684 the biggest problem with a computed ID is that it's mutable, so if you delete a row, IDs can change for existing records, and then anything that was using them for lookups will now be pulling the wrong record - and then you add/insert a new record and the IDs change again, and now everything is mangled beyond repair. Or a user comes in and sorts the data and mangles everything all over again. If you get IT's cooperation, have SQL Server set up. If you don't, have your data live in an Access database (which will easily handle immutable IDs) - not in Excel.
– Mathieu Guindon
Nov 9 at 18:17
1
1
Kindly remember that use of such formula can lead to future errors if used for IDs, instead create one static id column for id as value and one for this formula, when you get a new id, paste it as value in the static id column, this will help you in future, because one wrong entry can update all the ids of all your rows and any linked formulae with ids will be messed up
– usmanhaq
Nov 9 at 17:51
Kindly remember that use of such formula can lead to future errors if used for IDs, instead create one static id column for id as value and one for this formula, when you get a new id, paste it as value in the static id column, this will help you in future, because one wrong entry can update all the ids of all your rows and any linked formulae with ids will be messed up
– usmanhaq
Nov 9 at 17:51
2
2
@user7735684 Excel isn't made for this. Using Excel as a relational database is a doomed endeavor.
– Mathieu Guindon
Nov 9 at 17:58
@user7735684 Excel isn't made for this. Using Excel as a relational database is a doomed endeavor.
– Mathieu Guindon
Nov 9 at 17:58
1
1
@user7735684 see edit, but take to heart what the others are saying, moving to a relational database would be better. While Excel can do a lot of what a database can it is too easy for the user to mess it up.
– Scott Craner
Nov 9 at 18:07
@user7735684 see edit, but take to heart what the others are saying, moving to a relational database would be better. While Excel can do a lot of what a database can it is too easy for the user to mess it up.
– Scott Craner
Nov 9 at 18:07
1
1
@ScottCraner, thank you so much, and I will have that conversation to begin moving data over to a relational database. Now I know where Excel is limited, I can work to better educate others!
– user7735684
Nov 9 at 18:11
@ScottCraner, thank you so much, and I will have that conversation to begin moving data over to a relational database. Now I know where Excel is limited, I can work to better educate others!
– user7735684
Nov 9 at 18:11
4
4
@user7735684 the biggest problem with a computed ID is that it's mutable, so if you delete a row, IDs can change for existing records, and then anything that was using them for lookups will now be pulling the wrong record - and then you add/insert a new record and the IDs change again, and now everything is mangled beyond repair. Or a user comes in and sorts the data and mangles everything all over again. If you get IT's cooperation, have SQL Server set up. If you don't, have your data live in an Access database (which will easily handle immutable IDs) - not in Excel.
– Mathieu Guindon
Nov 9 at 18:17
@user7735684 the biggest problem with a computed ID is that it's mutable, so if you delete a row, IDs can change for existing records, and then anything that was using them for lookups will now be pulling the wrong record - and then you add/insert a new record and the IDs change again, and now everything is mangled beyond repair. Or a user comes in and sorts the data and mangles everything all over again. If you get IT's cooperation, have SQL Server set up. If you don't, have your data live in an Access database (which will easily handle immutable IDs) - not in Excel.
– Mathieu Guindon
Nov 9 at 18:17
|
show 3 more comments
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53230660%2fauto-increment-if-column-already-contains-a-certain-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Sounds like a job for a relational database.
– Comintern
Nov 9 at 17:47
Is there any sort of workaround?
– user7735684
Nov 9 at 18:02
4
If you're trying to create a relational database in something that isn't a relational database, everything will be a workaround.
– Comintern
Nov 9 at 18:03
1
sure there is
– Mathieu Guindon
Nov 9 at 18:09