How to make newly inserted row in Excel include automatically generated data? Announcing the...
Significance of Cersei's obsession with elephants?
How to pronounce 伝統色
If Windows 7 doesn't support WSL, then what is "Subsystem for UNIX-based Applications"?
How does light 'choose' between wave and particle behaviour?
How could we fake a moon landing now?
Why can't I install Tomboy in Ubuntu Mate 19.04?
Random body shuffle every night—can we still function?
How often does castling occur in grandmaster games?
What's the point of the test set?
Do I really need to have a message in a novel to appeal to readers?
Semigroups with no morphisms between them
What order were files/directories output in dir?
Intuitive explanation of the rank-nullity theorem
Is there public access to the Meteor Crater in Arizona?
How long can equipment go unused before powering up runs the risk of damage?
Drawing spherical mirrors
Most bit efficient text communication method?
Co-worker has annoying ringtone
Strange behavior of Object.defineProperty() in JavaScript
Antipodal Land Area Calculation
Sliceness of knots
What are the discoveries that have been possible with the rejection of positivism?
How to write capital alpha?
Induction Proof for Sequences
How to make newly inserted row in Excel include automatically generated data?
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 23:30UTC (7:30pm US/Eastern)Microsoft Excel – How to link or copy cells to a different worksheet on every nth rowShowing name of row instead of excel cell nameReferenced cell containing date-time showing up as a number in ExcelUsing Min/Max function in excel how do I return a different value from the same row?Excel VBA Code to move data from 4 specific cells to another worksheet in the next available rowHow to get number from last row of dataFind corresponding column and row data based on data from another cell in ExcelHow to automatically calculate formulas from new form submission data?Reference cell by identifier(not address) between sheetsExcel 2016 - if value of any cell in column A of sheet 1 = A1 of sheet 2 then show value of sheet 1, column D of that row in A2 of sheet 2
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have an excel sheet that I am using as a requirements document. The sheet that I am looking at is using the following numbering sequence in A: FR01, FR02, FR03. The sheet may be sorted by other fields.
When a new row is created, no matter where it is generated within the sheet, I would like the cell in A to be populated by the next number in the FRxx sequence. If the highest value in A is FR55, I'd like FR56. I have tried various formulas and number schemes, but can't seem to get it right.
Thanks for your help!
microsoft-excel worksheet-function vba
edited 6 hours ago
Alex M
605314
asked 7 hours ago
EndroEndro
63
New contributor
Endro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
|
show 10 more comments
I have an excel sheet that I am using as a requirements document. The sheet that I am looking at is using the following numbering sequence in A: FR01, FR02, FR03. The sheet may be sorted by other fields.
When a new row is created, no matter where it is generated within the sheet, I would like the cell in A to be populated by the next number in the FRxx sequence. If the highest value in A is FR55, I'd like FR56. I have tried various formulas and number schemes, but can't seem to get it right.
Thanks for your help!
microsoft-excel worksheet-function vba
edited 6 hours ago
Alex M
605314
asked 7 hours ago
EndroEndro
63
New contributor
Endro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Please post a screenshot of your workbook or a sample of data to help us better understand your question. (You can paste an image from PrintScreen/Snipping tool directly into your post)
– user2800
7 hours ago
How are the FR numbers being generated now?
– Alex M
6 hours ago
1
I guess by automatic numbering, I meant using the fill or flashfill function. My thought here is that there should be a simple solution to this problem that I just haven't identified through the research I've done. The image gives you an idea of the layout of the sheet looks like. I thought I was pretty clear about what a solution would look like, but I'll give it another shot. When a new row is created, no matter where it is generated within the sheet, I would like the cell in column A to be populated by the next number in the FRxx sequence. If the highest Req number is FR55, I'd like FR56.
– Endro
6 hours ago
1
Yes, you will need VBA. If you want to simplify the code, you can create a formula somewhere in your workbook that identifies what FRxx code will come next. Then when you create your macro, all you need your code to do is insert a new row at a position of your choosing and copy the result of that formula into the first column.
– user2800
6 hours ago
1
In fact, @Endro, since you seem receptive to adding skills, I'm gonna make that my quasi-answer for you: Stick the formula I just wrote somewhere in your workbook. Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross all your i's, write up your solution as an answer to this question, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
– Alex M
5 hours ago
|
show 10 more comments
I have an excel sheet that I am using as a requirements document. The sheet that I am looking at is using the following numbering sequence in A: FR01, FR02, FR03. The sheet may be sorted by other fields.
When a new row is created, no matter where it is generated within the sheet, I would like the cell in A to be populated by the next number in the FRxx sequence. If the highest value in A is FR55, I'd like FR56. I have tried various formulas and number schemes, but can't seem to get it right.
Thanks for your help!
microsoft-excel worksheet-function vba
edited 6 hours ago
Alex M
605314
asked 7 hours ago
EndroEndro
63
New contributor
Endro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have an excel sheet that I am using as a requirements document. The sheet that I am looking at is using the following numbering sequence in A: FR01, FR02, FR03. The sheet may be sorted by other fields.
When a new row is created, no matter where it is generated within the sheet, I would like the cell in A to be populated by the next number in the FRxx sequence. If the highest value in A is FR55, I'd like FR56. I have tried various formulas and number schemes, but can't seem to get it right.
Thanks for your help!
microsoft-excel worksheet-function vba
microsoft-excel worksheet-function vba
edited 6 hours ago
Alex M
605314
asked 7 hours ago
EndroEndro
63
New contributor
Endro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 6 hours ago
Alex M
605314
asked 7 hours ago
EndroEndro
63
New contributor
Endro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 6 hours ago
Alex M
605314
edited 6 hours ago
Alex M
605314
edited 6 hours ago
Alex M
605314
605314
asked 7 hours ago
EndroEndro
63
New contributor
Endro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 7 hours ago
EndroEndro
63
asked 7 hours ago
EndroEndro
63
63
New contributor
Endro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Endro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Endro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Please post a screenshot of your workbook or a sample of data to help us better understand your question. (You can paste an image from PrintScreen/Snipping tool directly into your post)
– user2800
7 hours ago
How are the FR numbers being generated now?
– Alex M
6 hours ago
1
I guess by automatic numbering, I meant using the fill or flashfill function. My thought here is that there should be a simple solution to this problem that I just haven't identified through the research I've done. The image gives you an idea of the layout of the sheet looks like. I thought I was pretty clear about what a solution would look like, but I'll give it another shot. When a new row is created, no matter where it is generated within the sheet, I would like the cell in column A to be populated by the next number in the FRxx sequence. If the highest Req number is FR55, I'd like FR56.
– Endro
6 hours ago
1
Yes, you will need VBA. If you want to simplify the code, you can create a formula somewhere in your workbook that identifies what FRxx code will come next. Then when you create your macro, all you need your code to do is insert a new row at a position of your choosing and copy the result of that formula into the first column.
– user2800
6 hours ago
1
In fact, @Endro, since you seem receptive to adding skills, I'm gonna make that my quasi-answer for you: Stick the formula I just wrote somewhere in your workbook. Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross all your i's, write up your solution as an answer to this question, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
– Alex M
5 hours ago
|
show 10 more comments
1
Please post a screenshot of your workbook or a sample of data to help us better understand your question. (You can paste an image from PrintScreen/Snipping tool directly into your post)
– user2800
7 hours ago
How are the FR numbers being generated now?
– Alex M
6 hours ago
1
I guess by automatic numbering, I meant using the fill or flashfill function. My thought here is that there should be a simple solution to this problem that I just haven't identified through the research I've done. The image gives you an idea of the layout of the sheet looks like. I thought I was pretty clear about what a solution would look like, but I'll give it another shot. When a new row is created, no matter where it is generated within the sheet, I would like the cell in column A to be populated by the next number in the FRxx sequence. If the highest Req number is FR55, I'd like FR56.
– Endro
6 hours ago
1
Yes, you will need VBA. If you want to simplify the code, you can create a formula somewhere in your workbook that identifies what FRxx code will come next. Then when you create your macro, all you need your code to do is insert a new row at a position of your choosing and copy the result of that formula into the first column.
– user2800
6 hours ago
1
In fact, @Endro, since you seem receptive to adding skills, I'm gonna make that my quasi-answer for you: Stick the formula I just wrote somewhere in your workbook. Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross all your i's, write up your solution as an answer to this question, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
– Alex M
5 hours ago
1
1
Please post a screenshot of your workbook or a sample of data to help us better understand your question. (You can paste an image from PrintScreen/Snipping tool directly into your post)
– user2800
7 hours ago
Please post a screenshot of your workbook or a sample of data to help us better understand your question. (You can paste an image from PrintScreen/Snipping tool directly into your post)
– user2800
7 hours ago
How are the FR numbers being generated now?
– Alex M
6 hours ago
How are the FR numbers being generated now?
– Alex M
6 hours ago
1
1
I guess by automatic numbering, I meant using the fill or flashfill function. My thought here is that there should be a simple solution to this problem that I just haven't identified through the research I've done. The image gives you an idea of the layout of the sheet looks like. I thought I was pretty clear about what a solution would look like, but I'll give it another shot. When a new row is created, no matter where it is generated within the sheet, I would like the cell in column A to be populated by the next number in the FRxx sequence. If the highest Req number is FR55, I'd like FR56.
– Endro
6 hours ago
I guess by automatic numbering, I meant using the fill or flashfill function. My thought here is that there should be a simple solution to this problem that I just haven't identified through the research I've done. The image gives you an idea of the layout of the sheet looks like. I thought I was pretty clear about what a solution would look like, but I'll give it another shot. When a new row is created, no matter where it is generated within the sheet, I would like the cell in column A to be populated by the next number in the FRxx sequence. If the highest Req number is FR55, I'd like FR56.
– Endro
6 hours ago
1
1
Yes, you will need VBA. If you want to simplify the code, you can create a formula somewhere in your workbook that identifies what FRxx code will come next. Then when you create your macro, all you need your code to do is insert a new row at a position of your choosing and copy the result of that formula into the first column.
– user2800
6 hours ago
Yes, you will need VBA. If you want to simplify the code, you can create a formula somewhere in your workbook that identifies what FRxx code will come next. Then when you create your macro, all you need your code to do is insert a new row at a position of your choosing and copy the result of that formula into the first column.
– user2800
6 hours ago
1
1
In fact, @Endro, since you seem receptive to adding skills, I'm gonna make that my quasi-answer for you: Stick the formula I just wrote somewhere in your workbook. Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross all your i's, write up your solution as an answer to this question, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
– Alex M
5 hours ago
In fact, @Endro, since you seem receptive to adding skills, I'm gonna make that my quasi-answer for you: Stick the formula I just wrote somewhere in your workbook. Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross all your i's, write up your solution as an answer to this question, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
– Alex M
5 hours ago
|
show 10 more comments
1 Answer
1
active
oldest
votes
If you don't mind, my partial solution based on our discussion in the comments became interesting enough to me that I'd like to post it as an answer for posterity, even though I haven't gotten you all the way there yet.
The solution you're looking for has two parts; I've solved the first and, per our discussion, will leave the second to you.
Part I
Put this formula anywhere in your workbook:
="FR"&TEXT(MAX(IFERROR(VALUE(MID(INDIRECT("$A2:$A"&LOOKUP(2,1/(A:A<>""),ROW(A:A))),3,2)),0))+1,"00")
This is an array formula; confirm it after entry by pressing Ctrl + Shift + Enter
This will create a cell containing the 'FR' ID that should be assigned to the next-created record:
Some notes on this formula:
LOOKUPis a fun thing. This trick to use it to locate the last-filled row or column is very cool. Originally I had="FR"&TEXT(MAX(IFERROR(VALUE(MID(INDIRECT("$A2:$A"&COUNTA(A:A)),3,2)),0))+1,"00")butCOUNTAis not reliable when blanks exist. I include it here because the comparison between that version and the final one helps to demonstrate what's doing what in that long-ish formula.
1.5 I suppose I should include a shout out to excel jet dot net, from which theLOOKUPchunk of code here is taken directly.- Other than the
LOOKUPtrick inside anINDIRECTto determine the target range for the array function, what's going on here is actually quite simple; get theMAXofVALUEof theMID(3,2)string ofA(ignoring errors), add `, and prepend 'FR'. Easy. - This formula will work irrespective of sorting and regardless of how many blank rows might be inserted.
- Thank you to @Hannu for the note on using
TEXTto handle formatting single-digit outputs. Note that if you use theTEXT(...,"00")version, it will break for three-digit outputs; you'll need to adjust toTEXT(...,"000")in that case (assuming 'FR001' etc works for single digit outputs!).
Okay, so now you have the value you need, but it's just sitting there. What next?
Part II
Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross your i's and dot your t's, write up your solution as an answer, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
answered 3 hours ago
Alex MAlex M
605314
1
FWIW:TEXT(number,"00")will create double digit numbers for you.
– Hannu
2 hours ago
@AlexM Thanks for all your work on this! That formula is in my worksheet and functioning really well. I'm going to take time next week to dig into the macro/VBA needed to make the numbering scheme function. My goal after that is to automate the numbering process whenever a new row is put in. I'll post back with and update next week.
– Endro
2 hours ago
@Hannu Nice, can't believe I missed that, thank you very much. Edited answer.
– Alex M
1 hour ago
@Endro recording a macro is only nominally VBA (in that Excel creates a VBA module for you once you're done, but you don't interact directly with VB yourself when recording a macro). Conflating the two may make you feel like creating a macro is a much bigger task than it is. You're really going to love it.
– Alex M
1 hour ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Endro is a new contributor. Be nice, and check out our Code of Conduct.
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%2fsuperuser.com%2fquestions%2f1427417%2fhow-to-make-newly-inserted-row-in-excel-include-automatically-generated-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you don't mind, my partial solution based on our discussion in the comments became interesting enough to me that I'd like to post it as an answer for posterity, even though I haven't gotten you all the way there yet.
The solution you're looking for has two parts; I've solved the first and, per our discussion, will leave the second to you.
Part I
Put this formula anywhere in your workbook:
="FR"&TEXT(MAX(IFERROR(VALUE(MID(INDIRECT("$A2:$A"&LOOKUP(2,1/(A:A<>""),ROW(A:A))),3,2)),0))+1,"00")
This is an array formula; confirm it after entry by pressing Ctrl + Shift + Enter
This will create a cell containing the 'FR' ID that should be assigned to the next-created record:
Some notes on this formula:
LOOKUPis a fun thing. This trick to use it to locate the last-filled row or column is very cool. Originally I had="FR"&TEXT(MAX(IFERROR(VALUE(MID(INDIRECT("$A2:$A"&COUNTA(A:A)),3,2)),0))+1,"00")butCOUNTAis not reliable when blanks exist. I include it here because the comparison between that version and the final one helps to demonstrate what's doing what in that long-ish formula.
1.5 I suppose I should include a shout out to excel jet dot net, from which theLOOKUPchunk of code here is taken directly.- Other than the
LOOKUPtrick inside anINDIRECTto determine the target range for the array function, what's going on here is actually quite simple; get theMAXofVALUEof theMID(3,2)string ofA(ignoring errors), add `, and prepend 'FR'. Easy. - This formula will work irrespective of sorting and regardless of how many blank rows might be inserted.
- Thank you to @Hannu for the note on using
TEXTto handle formatting single-digit outputs. Note that if you use theTEXT(...,"00")version, it will break for three-digit outputs; you'll need to adjust toTEXT(...,"000")in that case (assuming 'FR001' etc works for single digit outputs!).
Okay, so now you have the value you need, but it's just sitting there. What next?
Part II
Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross your i's and dot your t's, write up your solution as an answer, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
answered 3 hours ago
Alex MAlex M
605314
1
FWIW:TEXT(number,"00")will create double digit numbers for you.
– Hannu
2 hours ago
@AlexM Thanks for all your work on this! That formula is in my worksheet and functioning really well. I'm going to take time next week to dig into the macro/VBA needed to make the numbering scheme function. My goal after that is to automate the numbering process whenever a new row is put in. I'll post back with and update next week.
– Endro
2 hours ago
@Hannu Nice, can't believe I missed that, thank you very much. Edited answer.
– Alex M
1 hour ago
@Endro recording a macro is only nominally VBA (in that Excel creates a VBA module for you once you're done, but you don't interact directly with VB yourself when recording a macro). Conflating the two may make you feel like creating a macro is a much bigger task than it is. You're really going to love it.
– Alex M
1 hour ago
add a comment |
If you don't mind, my partial solution based on our discussion in the comments became interesting enough to me that I'd like to post it as an answer for posterity, even though I haven't gotten you all the way there yet.
The solution you're looking for has two parts; I've solved the first and, per our discussion, will leave the second to you.
Part I
Put this formula anywhere in your workbook:
="FR"&TEXT(MAX(IFERROR(VALUE(MID(INDIRECT("$A2:$A"&LOOKUP(2,1/(A:A<>""),ROW(A:A))),3,2)),0))+1,"00")
This is an array formula; confirm it after entry by pressing Ctrl + Shift + Enter
This will create a cell containing the 'FR' ID that should be assigned to the next-created record:
Some notes on this formula:
LOOKUPis a fun thing. This trick to use it to locate the last-filled row or column is very cool. Originally I had="FR"&TEXT(MAX(IFERROR(VALUE(MID(INDIRECT("$A2:$A"&COUNTA(A:A)),3,2)),0))+1,"00")butCOUNTAis not reliable when blanks exist. I include it here because the comparison between that version and the final one helps to demonstrate what's doing what in that long-ish formula.
1.5 I suppose I should include a shout out to excel jet dot net, from which theLOOKUPchunk of code here is taken directly.- Other than the
LOOKUPtrick inside anINDIRECTto determine the target range for the array function, what's going on here is actually quite simple; get theMAXofVALUEof theMID(3,2)string ofA(ignoring errors), add `, and prepend 'FR'. Easy. - This formula will work irrespective of sorting and regardless of how many blank rows might be inserted.
- Thank you to @Hannu for the note on using
TEXTto handle formatting single-digit outputs. Note that if you use theTEXT(...,"00")version, it will break for three-digit outputs; you'll need to adjust toTEXT(...,"000")in that case (assuming 'FR001' etc works for single digit outputs!).
Okay, so now you have the value you need, but it's just sitting there. What next?
Part II
Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross your i's and dot your t's, write up your solution as an answer, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
answered 3 hours ago
Alex MAlex M
605314
1
FWIW:TEXT(number,"00")will create double digit numbers for you.
– Hannu
2 hours ago
@AlexM Thanks for all your work on this! That formula is in my worksheet and functioning really well. I'm going to take time next week to dig into the macro/VBA needed to make the numbering scheme function. My goal after that is to automate the numbering process whenever a new row is put in. I'll post back with and update next week.
– Endro
2 hours ago
@Hannu Nice, can't believe I missed that, thank you very much. Edited answer.
– Alex M
1 hour ago
@Endro recording a macro is only nominally VBA (in that Excel creates a VBA module for you once you're done, but you don't interact directly with VB yourself when recording a macro). Conflating the two may make you feel like creating a macro is a much bigger task than it is. You're really going to love it.
– Alex M
1 hour ago
add a comment |
If you don't mind, my partial solution based on our discussion in the comments became interesting enough to me that I'd like to post it as an answer for posterity, even though I haven't gotten you all the way there yet.
The solution you're looking for has two parts; I've solved the first and, per our discussion, will leave the second to you.
Part I
Put this formula anywhere in your workbook:
="FR"&TEXT(MAX(IFERROR(VALUE(MID(INDIRECT("$A2:$A"&LOOKUP(2,1/(A:A<>""),ROW(A:A))),3,2)),0))+1,"00")
This is an array formula; confirm it after entry by pressing Ctrl + Shift + Enter
This will create a cell containing the 'FR' ID that should be assigned to the next-created record:
Some notes on this formula:
LOOKUPis a fun thing. This trick to use it to locate the last-filled row or column is very cool. Originally I had="FR"&TEXT(MAX(IFERROR(VALUE(MID(INDIRECT("$A2:$A"&COUNTA(A:A)),3,2)),0))+1,"00")butCOUNTAis not reliable when blanks exist. I include it here because the comparison between that version and the final one helps to demonstrate what's doing what in that long-ish formula.
1.5 I suppose I should include a shout out to excel jet dot net, from which theLOOKUPchunk of code here is taken directly.- Other than the
LOOKUPtrick inside anINDIRECTto determine the target range for the array function, what's going on here is actually quite simple; get theMAXofVALUEof theMID(3,2)string ofA(ignoring errors), add `, and prepend 'FR'. Easy. - This formula will work irrespective of sorting and regardless of how many blank rows might be inserted.
- Thank you to @Hannu for the note on using
TEXTto handle formatting single-digit outputs. Note that if you use theTEXT(...,"00")version, it will break for three-digit outputs; you'll need to adjust toTEXT(...,"000")in that case (assuming 'FR001' etc works for single digit outputs!).
Okay, so now you have the value you need, but it's just sitting there. What next?
Part II
Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross your i's and dot your t's, write up your solution as an answer, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
answered 3 hours ago
Alex MAlex M
605314
If you don't mind, my partial solution based on our discussion in the comments became interesting enough to me that I'd like to post it as an answer for posterity, even though I haven't gotten you all the way there yet.
The solution you're looking for has two parts; I've solved the first and, per our discussion, will leave the second to you.
Part I
Put this formula anywhere in your workbook:
="FR"&TEXT(MAX(IFERROR(VALUE(MID(INDIRECT("$A2:$A"&LOOKUP(2,1/(A:A<>""),ROW(A:A))),3,2)),0))+1,"00")
This is an array formula; confirm it after entry by pressing Ctrl + Shift + Enter
This will create a cell containing the 'FR' ID that should be assigned to the next-created record:
Some notes on this formula:
LOOKUPis a fun thing. This trick to use it to locate the last-filled row or column is very cool. Originally I had="FR"&TEXT(MAX(IFERROR(VALUE(MID(INDIRECT("$A2:$A"&COUNTA(A:A)),3,2)),0))+1,"00")butCOUNTAis not reliable when blanks exist. I include it here because the comparison between that version and the final one helps to demonstrate what's doing what in that long-ish formula.
1.5 I suppose I should include a shout out to excel jet dot net, from which theLOOKUPchunk of code here is taken directly.- Other than the
LOOKUPtrick inside anINDIRECTto determine the target range for the array function, what's going on here is actually quite simple; get theMAXofVALUEof theMID(3,2)string ofA(ignoring errors), add `, and prepend 'FR'. Easy. - This formula will work irrespective of sorting and regardless of how many blank rows might be inserted.
- Thank you to @Hannu for the note on using
TEXTto handle formatting single-digit outputs. Note that if you use theTEXT(...,"00")version, it will break for three-digit outputs; you'll need to adjust toTEXT(...,"000")in that case (assuming 'FR001' etc works for single digit outputs!).
Okay, so now you have the value you need, but it's just sitting there. What next?
Part II
Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross your i's and dot your t's, write up your solution as an answer, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
answered 3 hours ago
Alex MAlex M
605314
edited 1 hour ago
answered 3 hours ago
Alex MAlex M
605314
answered 3 hours ago
Alex MAlex M
605314
answered 3 hours ago
Alex MAlex M
605314
605314
1
FWIW:TEXT(number,"00")will create double digit numbers for you.
– Hannu
2 hours ago
@AlexM Thanks for all your work on this! That formula is in my worksheet and functioning really well. I'm going to take time next week to dig into the macro/VBA needed to make the numbering scheme function. My goal after that is to automate the numbering process whenever a new row is put in. I'll post back with and update next week.
– Endro
2 hours ago
@Hannu Nice, can't believe I missed that, thank you very much. Edited answer.
– Alex M
1 hour ago
@Endro recording a macro is only nominally VBA (in that Excel creates a VBA module for you once you're done, but you don't interact directly with VB yourself when recording a macro). Conflating the two may make you feel like creating a macro is a much bigger task than it is. You're really going to love it.
– Alex M
1 hour ago
add a comment |
1
FWIW:TEXT(number,"00")will create double digit numbers for you.
– Hannu
2 hours ago
@AlexM Thanks for all your work on this! That formula is in my worksheet and functioning really well. I'm going to take time next week to dig into the macro/VBA needed to make the numbering scheme function. My goal after that is to automate the numbering process whenever a new row is put in. I'll post back with and update next week.
– Endro
2 hours ago
@Hannu Nice, can't believe I missed that, thank you very much. Edited answer.
– Alex M
1 hour ago
@Endro recording a macro is only nominally VBA (in that Excel creates a VBA module for you once you're done, but you don't interact directly with VB yourself when recording a macro). Conflating the two may make you feel like creating a macro is a much bigger task than it is. You're really going to love it.
– Alex M
1 hour ago
1
1
FWIW:
TEXT(number,"00") will create double digit numbers for you.– Hannu
2 hours ago
FWIW:
TEXT(number,"00") will create double digit numbers for you.– Hannu
2 hours ago
@AlexM Thanks for all your work on this! That formula is in my worksheet and functioning really well. I'm going to take time next week to dig into the macro/VBA needed to make the numbering scheme function. My goal after that is to automate the numbering process whenever a new row is put in. I'll post back with and update next week.
– Endro
2 hours ago
@AlexM Thanks for all your work on this! That formula is in my worksheet and functioning really well. I'm going to take time next week to dig into the macro/VBA needed to make the numbering scheme function. My goal after that is to automate the numbering process whenever a new row is put in. I'll post back with and update next week.
– Endro
2 hours ago
@Hannu Nice, can't believe I missed that, thank you very much. Edited answer.
– Alex M
1 hour ago
@Hannu Nice, can't believe I missed that, thank you very much. Edited answer.
– Alex M
1 hour ago
@Endro recording a macro is only nominally VBA (in that Excel creates a VBA module for you once you're done, but you don't interact directly with VB yourself when recording a macro). Conflating the two may make you feel like creating a macro is a much bigger task than it is. You're really going to love it.
– Alex M
1 hour ago
@Endro recording a macro is only nominally VBA (in that Excel creates a VBA module for you once you're done, but you don't interact directly with VB yourself when recording a macro). Conflating the two may make you feel like creating a macro is a much bigger task than it is. You're really going to love it.
– Alex M
1 hour ago
add a comment |
Endro is a new contributor. Be nice, and check out our Code of Conduct.
Endro is a new contributor. Be nice, and check out our Code of Conduct.
Endro is a new contributor. Be nice, and check out our Code of Conduct.
Endro is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2fsuperuser.com%2fquestions%2f1427417%2fhow-to-make-newly-inserted-row-in-excel-include-automatically-generated-data%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
1
Please post a screenshot of your workbook or a sample of data to help us better understand your question. (You can paste an image from PrintScreen/Snipping tool directly into your post)
– user2800
7 hours ago
How are the FR numbers being generated now?
– Alex M
6 hours ago
1
I guess by automatic numbering, I meant using the fill or flashfill function. My thought here is that there should be a simple solution to this problem that I just haven't identified through the research I've done. The image gives you an idea of the layout of the sheet looks like. I thought I was pretty clear about what a solution would look like, but I'll give it another shot. When a new row is created, no matter where it is generated within the sheet, I would like the cell in column A to be populated by the next number in the FRxx sequence. If the highest Req number is FR55, I'd like FR56.
– Endro
6 hours ago
1
Yes, you will need VBA. If you want to simplify the code, you can create a formula somewhere in your workbook that identifies what FRxx code will come next. Then when you create your macro, all you need your code to do is insert a new row at a position of your choosing and copy the result of that formula into the first column.
– user2800
6 hours ago
1
In fact, @Endro, since you seem receptive to adding skills, I'm gonna make that my quasi-answer for you: Stick the formula I just wrote somewhere in your workbook. Google "Excel record macro" and read a couple of tutorials. From there it should be pretty easy to complete your task. If you really want to cross all your i's, write up your solution as an answer to this question, and after a day or two you can accept your own answer. @ me in the comments and I'll upvote, too. This is a very useful skill and a perfect example use case.
– Alex M
5 hours ago