Mixing
Excel Formula to find Next Sheet in workbook
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a huge file with many sheets and in each sheet I need the same formula that references the next sheet in sequence, but only the sheet that follows it.
For example:
Let's say I have sheets organized as follows in order from first sheet to last sheet (left to right):
"BOB"
"JIM"
"Larry"
"Tim"
"Joe"
...
The formula in "BOB" needs to refer to sheet "JIM", and the formula in "JIM" needs to refer to sheet "Larry", etc. There are dozens of sheets like this.
I've seen formulas that can refer to active sheet or current sheet, but is there a formula that can reference to the next sheet? This way I can just copy the formula into each sheet without modifying the formula's sheet reference each time.
Thanks!
EDIT: The sheets are not named after actual dates - I was just using that as an example to clearly state what I need; I guess it was a bad example. I'll correct that by replacing them with names.
excel reference formula next
asked Jan 3 at 16:03
StevenSteven
35118
add a comment |
I have a huge file with many sheets and in each sheet I need the same formula that references the next sheet in sequence, but only the sheet that follows it.
For example:
Let's say I have sheets organized as follows in order from first sheet to last sheet (left to right):
"BOB"
"JIM"
"Larry"
"Tim"
"Joe"
...
The formula in "BOB" needs to refer to sheet "JIM", and the formula in "JIM" needs to refer to sheet "Larry", etc. There are dozens of sheets like this.
I've seen formulas that can refer to active sheet or current sheet, but is there a formula that can reference to the next sheet? This way I can just copy the formula into each sheet without modifying the formula's sheet reference each time.
Thanks!
EDIT: The sheets are not named after actual dates - I was just using that as an example to clearly state what I need; I guess it was a bad example. I'll correct that by replacing them with names.
excel reference formula next
asked Jan 3 at 16:03
StevenSteven
35118
That would work well except the sheets are not actually dates, I was using that as an example but didn't make that clear. I edited the original post.
– Steven
Jan 3 at 16:41
Can you use VBA (UDF) or does it have to be a formula?
– BigBen
Jan 3 at 19:50
add a comment |
I have a huge file with many sheets and in each sheet I need the same formula that references the next sheet in sequence, but only the sheet that follows it.
For example:
Let's say I have sheets organized as follows in order from first sheet to last sheet (left to right):
"BOB"
"JIM"
"Larry"
"Tim"
"Joe"
...
The formula in "BOB" needs to refer to sheet "JIM", and the formula in "JIM" needs to refer to sheet "Larry", etc. There are dozens of sheets like this.
I've seen formulas that can refer to active sheet or current sheet, but is there a formula that can reference to the next sheet? This way I can just copy the formula into each sheet without modifying the formula's sheet reference each time.
Thanks!
EDIT: The sheets are not named after actual dates - I was just using that as an example to clearly state what I need; I guess it was a bad example. I'll correct that by replacing them with names.
excel reference formula next
asked Jan 3 at 16:03
StevenSteven
35118
I have a huge file with many sheets and in each sheet I need the same formula that references the next sheet in sequence, but only the sheet that follows it.
For example:
Let's say I have sheets organized as follows in order from first sheet to last sheet (left to right):
"BOB"
"JIM"
"Larry"
"Tim"
"Joe"
...
The formula in "BOB" needs to refer to sheet "JIM", and the formula in "JIM" needs to refer to sheet "Larry", etc. There are dozens of sheets like this.
I've seen formulas that can refer to active sheet or current sheet, but is there a formula that can reference to the next sheet? This way I can just copy the formula into each sheet without modifying the formula's sheet reference each time.
Thanks!
EDIT: The sheets are not named after actual dates - I was just using that as an example to clearly state what I need; I guess it was a bad example. I'll correct that by replacing them with names.
excel reference formula next
excel reference formula next
asked Jan 3 at 16:03
StevenSteven
35118
asked Jan 3 at 16:03
StevenSteven
35118
edited Jan 3 at 16:40
Steven
asked Jan 3 at 16:03
StevenSteven
35118
asked Jan 3 at 16:03
StevenSteven
35118
asked Jan 3 at 16:03
StevenSteven
35118
35118
That would work well except the sheets are not actually dates, I was using that as an example but didn't make that clear. I edited the original post.
– Steven
Jan 3 at 16:41
Can you use VBA (UDF) or does it have to be a formula?
– BigBen
Jan 3 at 19:50
add a comment |
That would work well except the sheets are not actually dates, I was using that as an example but didn't make that clear. I edited the original post.
– Steven
Jan 3 at 16:41
Can you use VBA (UDF) or does it have to be a formula?
– BigBen
Jan 3 at 19:50
That would work well except the sheets are not actually dates, I was using that as an example but didn't make that clear. I edited the original post.
– Steven
Jan 3 at 16:41
That would work well except the sheets are not actually dates, I was using that as an example but didn't make that clear. I edited the original post.
– Steven
Jan 3 at 16:41
Can you use VBA (UDF) or does it have to be a formula?
– BigBen
Jan 3 at 19:50
Can you use VBA (UDF) or does it have to be a formula?
– BigBen
Jan 3 at 19:50
add a comment |
1 Answer
1
active
oldest
votes
If you place an ordered list of sheet names somewhere you could do it this way, though daresay there is a quicker way.
In A5
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))
In A6 (which returns the next sheet)
=INDEX(A1:A3,MATCH(A5,A1:A3,0)+1)
answered Jan 3 at 16:48
SJRSJR
13.7k31219
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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
});
}
});
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%2f54025852%2fexcel-formula-to-find-next-sheet-in-workbook%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 place an ordered list of sheet names somewhere you could do it this way, though daresay there is a quicker way.
In A5
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))
In A6 (which returns the next sheet)
=INDEX(A1:A3,MATCH(A5,A1:A3,0)+1)
answered Jan 3 at 16:48
SJRSJR
13.7k31219
add a comment |
If you place an ordered list of sheet names somewhere you could do it this way, though daresay there is a quicker way.
In A5
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))
In A6 (which returns the next sheet)
=INDEX(A1:A3,MATCH(A5,A1:A3,0)+1)
answered Jan 3 at 16:48
SJRSJR
13.7k31219
add a comment |
If you place an ordered list of sheet names somewhere you could do it this way, though daresay there is a quicker way.
In A5
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))
In A6 (which returns the next sheet)
=INDEX(A1:A3,MATCH(A5,A1:A3,0)+1)
answered Jan 3 at 16:48
SJRSJR
13.7k31219
If you place an ordered list of sheet names somewhere you could do it this way, though daresay there is a quicker way.
In A5
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))
In A6 (which returns the next sheet)
=INDEX(A1:A3,MATCH(A5,A1:A3,0)+1)
answered Jan 3 at 16:48
SJRSJR
13.7k31219
answered Jan 3 at 16:48
SJRSJR
13.7k31219
answered Jan 3 at 16:48
SJRSJR
13.7k31219
answered Jan 3 at 16:48
SJRSJR
13.7k31219
13.7k31219
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f54025852%2fexcel-formula-to-find-next-sheet-in-workbook%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
That would work well except the sheets are not actually dates, I was using that as an example but didn't make that clear. I edited the original post.
– Steven
Jan 3 at 16:41
Can you use VBA (UDF) or does it have to be a formula?
– BigBen
Jan 3 at 19:50