Alternative to Excel's INDIRECT function that references another sheet
In my workbook, I have multiple (15+) sheets, each with 8+ cells that use the INDIRECT function. All the usage of this function is just to reference another sheet in the workbook. This sheet, Data, has multiple rows, each row representing a dataset
Example,
one sheet needs 'Data'!B2, 'Data'!C2, 'Data'!F2, 'Data'!H2, 'Data'!J2, etc (there's about 8 of these). So what I do is I have a cells with "Data" and "2" and use INDIRECT to create these formulas. That way, I could easily change the row (2 to say 5) and also change the sheet source (there is another sheet just like Data that I want to work with). I don't want to manually change the formulas on each sheet, it's too tedious.
So as you can see, about 15+ sheets each with 8+ INDIRECT formulas make calculations very very slow.
Is there an alternative I could use that's faster?
[Note: I am aware of this question but 1. the answer doesn't make complete sense to me and 2. a lookup table is not dynamic enough (i.e. sheet names change frequently and the number of 'Data' sheets in a lot)
EDIT
I have revisited this sheet and realized that I have multiple array formulas which might also be contributing to calculations speed.
excel vba excel-formula excel-indirect
asked Nov 24 '18 at 8:05
OneirosOneiros
296
|
show 4 more comments
In my workbook, I have multiple (15+) sheets, each with 8+ cells that use the INDIRECT function. All the usage of this function is just to reference another sheet in the workbook. This sheet, Data, has multiple rows, each row representing a dataset
Example,
one sheet needs 'Data'!B2, 'Data'!C2, 'Data'!F2, 'Data'!H2, 'Data'!J2, etc (there's about 8 of these). So what I do is I have a cells with "Data" and "2" and use INDIRECT to create these formulas. That way, I could easily change the row (2 to say 5) and also change the sheet source (there is another sheet just like Data that I want to work with). I don't want to manually change the formulas on each sheet, it's too tedious.
So as you can see, about 15+ sheets each with 8+ INDIRECT formulas make calculations very very slow.
Is there an alternative I could use that's faster?
[Note: I am aware of this question but 1. the answer doesn't make complete sense to me and 2. a lookup table is not dynamic enough (i.e. sheet names change frequently and the number of 'Data' sheets in a lot)
EDIT
I have revisited this sheet and realized that I have multiple array formulas which might also be contributing to calculations speed.
excel vba excel-formula excel-indirect
asked Nov 24 '18 at 8:05
OneirosOneiros
296
1
15*8 indirect based formulas doesn't seem like a deal breaker. How slow is slow and what else is going on size wize/computationally in the workbook?
– QHarr
Nov 24 '18 at 8:44
@QHarr slow as in 10-12 mins whenever it calculates, which might not be much in the grand scheme of things but with the work I'm doing, anything more than 3 minutes of calculating is inefficient. it's about a 2.5mb workbook... no references to any other workbook... plus, I working on a i3 with 2 cores so I don't have my CPU to spare
– Oneiros
Nov 24 '18 at 8:55
I would definitely agree that is very slow. The size also isn't disturbing. I am just perplexed at the performance for what seems like a small number of formulas. Are they nested?
– QHarr
Nov 24 '18 at 9:01
If the need is to have the sheet name variable too, then there is no way aroundINDIRECTor VBA. Else=INDEX(Data!$1:$1048576,2,3)will getData!C2where both, row and column, may be variable.
– Axel Richter
Nov 24 '18 at 9:04
I agree with @QHarr that having a handful ofINDIRECTformulas in your sheet probably isn't (entirely) the root cause of your formula taking several minutes to recalculate. The only time I've foundINDIRECTunavoidable is if the sheet name itself is dynamic, but it doesn't sound like that is what is going on here. Sounds like you always refer to theDatasheet soINDIRECTcould be avoided by usingINDEXmost likely. Sounds like if you want this issue solved, you'll have to edit the question to provide us more details about your spreadsheet.
– ImaginaryHuman072889
Nov 24 '18 at 12:20
|
show 4 more comments
In my workbook, I have multiple (15+) sheets, each with 8+ cells that use the INDIRECT function. All the usage of this function is just to reference another sheet in the workbook. This sheet, Data, has multiple rows, each row representing a dataset
Example,
one sheet needs 'Data'!B2, 'Data'!C2, 'Data'!F2, 'Data'!H2, 'Data'!J2, etc (there's about 8 of these). So what I do is I have a cells with "Data" and "2" and use INDIRECT to create these formulas. That way, I could easily change the row (2 to say 5) and also change the sheet source (there is another sheet just like Data that I want to work with). I don't want to manually change the formulas on each sheet, it's too tedious.
So as you can see, about 15+ sheets each with 8+ INDIRECT formulas make calculations very very slow.
Is there an alternative I could use that's faster?
[Note: I am aware of this question but 1. the answer doesn't make complete sense to me and 2. a lookup table is not dynamic enough (i.e. sheet names change frequently and the number of 'Data' sheets in a lot)
EDIT
I have revisited this sheet and realized that I have multiple array formulas which might also be contributing to calculations speed.
excel vba excel-formula excel-indirect
asked Nov 24 '18 at 8:05
OneirosOneiros
296
In my workbook, I have multiple (15+) sheets, each with 8+ cells that use the INDIRECT function. All the usage of this function is just to reference another sheet in the workbook. This sheet, Data, has multiple rows, each row representing a dataset
Example,
one sheet needs 'Data'!B2, 'Data'!C2, 'Data'!F2, 'Data'!H2, 'Data'!J2, etc (there's about 8 of these). So what I do is I have a cells with "Data" and "2" and use INDIRECT to create these formulas. That way, I could easily change the row (2 to say 5) and also change the sheet source (there is another sheet just like Data that I want to work with). I don't want to manually change the formulas on each sheet, it's too tedious.
So as you can see, about 15+ sheets each with 8+ INDIRECT formulas make calculations very very slow.
Is there an alternative I could use that's faster?
[Note: I am aware of this question but 1. the answer doesn't make complete sense to me and 2. a lookup table is not dynamic enough (i.e. sheet names change frequently and the number of 'Data' sheets in a lot)
EDIT
I have revisited this sheet and realized that I have multiple array formulas which might also be contributing to calculations speed.
excel vba excel-formula excel-indirect
excel vba excel-formula excel-indirect
asked Nov 24 '18 at 8:05
OneirosOneiros
296
asked Nov 24 '18 at 8:05
OneirosOneiros
296
edited Nov 29 '18 at 21:32
Oneiros
asked Nov 24 '18 at 8:05
OneirosOneiros
296
asked Nov 24 '18 at 8:05
OneirosOneiros
296
asked Nov 24 '18 at 8:05
OneirosOneiros
296
296
1
15*8 indirect based formulas doesn't seem like a deal breaker. How slow is slow and what else is going on size wize/computationally in the workbook?
– QHarr
Nov 24 '18 at 8:44
@QHarr slow as in 10-12 mins whenever it calculates, which might not be much in the grand scheme of things but with the work I'm doing, anything more than 3 minutes of calculating is inefficient. it's about a 2.5mb workbook... no references to any other workbook... plus, I working on a i3 with 2 cores so I don't have my CPU to spare
– Oneiros
Nov 24 '18 at 8:55
I would definitely agree that is very slow. The size also isn't disturbing. I am just perplexed at the performance for what seems like a small number of formulas. Are they nested?
– QHarr
Nov 24 '18 at 9:01
If the need is to have the sheet name variable too, then there is no way aroundINDIRECTor VBA. Else=INDEX(Data!$1:$1048576,2,3)will getData!C2where both, row and column, may be variable.
– Axel Richter
Nov 24 '18 at 9:04
I agree with @QHarr that having a handful ofINDIRECTformulas in your sheet probably isn't (entirely) the root cause of your formula taking several minutes to recalculate. The only time I've foundINDIRECTunavoidable is if the sheet name itself is dynamic, but it doesn't sound like that is what is going on here. Sounds like you always refer to theDatasheet soINDIRECTcould be avoided by usingINDEXmost likely. Sounds like if you want this issue solved, you'll have to edit the question to provide us more details about your spreadsheet.
– ImaginaryHuman072889
Nov 24 '18 at 12:20
|
show 4 more comments
1
15*8 indirect based formulas doesn't seem like a deal breaker. How slow is slow and what else is going on size wize/computationally in the workbook?
– QHarr
Nov 24 '18 at 8:44
@QHarr slow as in 10-12 mins whenever it calculates, which might not be much in the grand scheme of things but with the work I'm doing, anything more than 3 minutes of calculating is inefficient. it's about a 2.5mb workbook... no references to any other workbook... plus, I working on a i3 with 2 cores so I don't have my CPU to spare
– Oneiros
Nov 24 '18 at 8:55
I would definitely agree that is very slow. The size also isn't disturbing. I am just perplexed at the performance for what seems like a small number of formulas. Are they nested?
– QHarr
Nov 24 '18 at 9:01
If the need is to have the sheet name variable too, then there is no way aroundINDIRECTor VBA. Else=INDEX(Data!$1:$1048576,2,3)will getData!C2where both, row and column, may be variable.
– Axel Richter
Nov 24 '18 at 9:04
I agree with @QHarr that having a handful ofINDIRECTformulas in your sheet probably isn't (entirely) the root cause of your formula taking several minutes to recalculate. The only time I've foundINDIRECTunavoidable is if the sheet name itself is dynamic, but it doesn't sound like that is what is going on here. Sounds like you always refer to theDatasheet soINDIRECTcould be avoided by usingINDEXmost likely. Sounds like if you want this issue solved, you'll have to edit the question to provide us more details about your spreadsheet.
– ImaginaryHuman072889
Nov 24 '18 at 12:20
1
1
15*8 indirect based formulas doesn't seem like a deal breaker. How slow is slow and what else is going on size wize/computationally in the workbook?
– QHarr
Nov 24 '18 at 8:44
15*8 indirect based formulas doesn't seem like a deal breaker. How slow is slow and what else is going on size wize/computationally in the workbook?
– QHarr
Nov 24 '18 at 8:44
@QHarr slow as in 10-12 mins whenever it calculates, which might not be much in the grand scheme of things but with the work I'm doing, anything more than 3 minutes of calculating is inefficient. it's about a 2.5mb workbook... no references to any other workbook... plus, I working on a i3 with 2 cores so I don't have my CPU to spare
– Oneiros
Nov 24 '18 at 8:55
@QHarr slow as in 10-12 mins whenever it calculates, which might not be much in the grand scheme of things but with the work I'm doing, anything more than 3 minutes of calculating is inefficient. it's about a 2.5mb workbook... no references to any other workbook... plus, I working on a i3 with 2 cores so I don't have my CPU to spare
– Oneiros
Nov 24 '18 at 8:55
I would definitely agree that is very slow. The size also isn't disturbing. I am just perplexed at the performance for what seems like a small number of formulas. Are they nested?
– QHarr
Nov 24 '18 at 9:01
I would definitely agree that is very slow. The size also isn't disturbing. I am just perplexed at the performance for what seems like a small number of formulas. Are they nested?
– QHarr
Nov 24 '18 at 9:01
If the need is to have the sheet name variable too, then there is no way around
INDIRECT or VBA. Else =INDEX(Data!$1:$1048576,2,3) will get Data!C2 where both, row and column, may be variable.– Axel Richter
Nov 24 '18 at 9:04
If the need is to have the sheet name variable too, then there is no way around
INDIRECT or VBA. Else =INDEX(Data!$1:$1048576,2,3) will get Data!C2 where both, row and column, may be variable.– Axel Richter
Nov 24 '18 at 9:04
I agree with @QHarr that having a handful of
INDIRECT formulas in your sheet probably isn't (entirely) the root cause of your formula taking several minutes to recalculate. The only time I've found INDIRECT unavoidable is if the sheet name itself is dynamic, but it doesn't sound like that is what is going on here. Sounds like you always refer to the Data sheet so INDIRECT could be avoided by using INDEX most likely. Sounds like if you want this issue solved, you'll have to edit the question to provide us more details about your spreadsheet.– ImaginaryHuman072889
Nov 24 '18 at 12:20
I agree with @QHarr that having a handful of
INDIRECT formulas in your sheet probably isn't (entirely) the root cause of your formula taking several minutes to recalculate. The only time I've found INDIRECT unavoidable is if the sheet name itself is dynamic, but it doesn't sound like that is what is going on here. Sounds like you always refer to the Data sheet so INDIRECT could be avoided by using INDEX most likely. Sounds like if you want this issue solved, you'll have to edit the question to provide us more details about your spreadsheet.– ImaginaryHuman072889
Nov 24 '18 at 12:20
|
show 4 more comments
0
active
oldest
votes
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%2f53456329%2falternative-to-excels-indirect-function-that-references-another-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53456329%2falternative-to-excels-indirect-function-that-references-another-sheet%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
15*8 indirect based formulas doesn't seem like a deal breaker. How slow is slow and what else is going on size wize/computationally in the workbook?
– QHarr
Nov 24 '18 at 8:44
@QHarr slow as in 10-12 mins whenever it calculates, which might not be much in the grand scheme of things but with the work I'm doing, anything more than 3 minutes of calculating is inefficient. it's about a 2.5mb workbook... no references to any other workbook... plus, I working on a i3 with 2 cores so I don't have my CPU to spare
– Oneiros
Nov 24 '18 at 8:55
I would definitely agree that is very slow. The size also isn't disturbing. I am just perplexed at the performance for what seems like a small number of formulas. Are they nested?
– QHarr
Nov 24 '18 at 9:01
If the need is to have the sheet name variable too, then there is no way around
INDIRECTor VBA. Else=INDEX(Data!$1:$1048576,2,3)will getData!C2where both, row and column, may be variable.– Axel Richter
Nov 24 '18 at 9:04
I agree with @QHarr that having a handful of
INDIRECTformulas in your sheet probably isn't (entirely) the root cause of your formula taking several minutes to recalculate. The only time I've foundINDIRECTunavoidable is if the sheet name itself is dynamic, but it doesn't sound like that is what is going on here. Sounds like you always refer to theDatasheet soINDIRECTcould be avoided by usingINDEXmost likely. Sounds like if you want this issue solved, you'll have to edit the question to provide us more details about your spreadsheet.– ImaginaryHuman072889
Nov 24 '18 at 12:20