Trouble with INDIRECT formula [closed]
This is my formula:
=IF(ActualsThrough<F$5,SUMIF('209Budget'!$F:$F,'209ActBud'!$C7,'209Budget'!I:I),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))
I would like to have the formula use an indirect dynamic reference for 209Budget and 209ActBud.
The idea is that I can enter "209" in an reference cell and all the formulas will update. When I copy the sheet to a new tab, I can then change the reference cell to "210", and all the formulas will update.
The problem I am having is trying to make the ranges be relative. The indirect formula requires the range reference to be in "". This causes the formula to be static rather than dynamic, which makes me unable to copy the formula across rows and columns.
microsoft-excel microsoft-excel-365
edited Jan 14 at 5:57
fixer1234
18.4k144781
asked Jan 14 at 2:58
whholderwhholder
11
closed as too broad by Ramhound, n8te, LotPings, Simon Sheehan, Moses Jan 16 at 17:30
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
This is my formula:
=IF(ActualsThrough<F$5,SUMIF('209Budget'!$F:$F,'209ActBud'!$C7,'209Budget'!I:I),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))
I would like to have the formula use an indirect dynamic reference for 209Budget and 209ActBud.
The idea is that I can enter "209" in an reference cell and all the formulas will update. When I copy the sheet to a new tab, I can then change the reference cell to "210", and all the formulas will update.
The problem I am having is trying to make the ranges be relative. The indirect formula requires the range reference to be in "". This causes the formula to be static rather than dynamic, which makes me unable to copy the formula across rows and columns.
microsoft-excel microsoft-excel-365
edited Jan 14 at 5:57
fixer1234
18.4k144781
asked Jan 14 at 2:58
whholderwhholder
11
closed as too broad by Ramhound, n8te, LotPings, Simon Sheehan, Moses Jan 16 at 17:30
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
for some reason the formula didn't copy through.
– whholder
Jan 14 at 2:59
"=IF(ActualsThrough<F$5,SUMIF('209Budget'!$F:$F,'209ActBud'!$C7,'209Budget'!I:I),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))"
– whholder
Jan 14 at 2:59
1
please edit your question instead of putting details into comments.
– teylyn
Jan 14 at 3:10
add a comment |
This is my formula:
=IF(ActualsThrough<F$5,SUMIF('209Budget'!$F:$F,'209ActBud'!$C7,'209Budget'!I:I),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))
I would like to have the formula use an indirect dynamic reference for 209Budget and 209ActBud.
The idea is that I can enter "209" in an reference cell and all the formulas will update. When I copy the sheet to a new tab, I can then change the reference cell to "210", and all the formulas will update.
The problem I am having is trying to make the ranges be relative. The indirect formula requires the range reference to be in "". This causes the formula to be static rather than dynamic, which makes me unable to copy the formula across rows and columns.
microsoft-excel microsoft-excel-365
edited Jan 14 at 5:57
fixer1234
18.4k144781
asked Jan 14 at 2:58
whholderwhholder
11
This is my formula:
=IF(ActualsThrough<F$5,SUMIF('209Budget'!$F:$F,'209ActBud'!$C7,'209Budget'!I:I),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))
I would like to have the formula use an indirect dynamic reference for 209Budget and 209ActBud.
The idea is that I can enter "209" in an reference cell and all the formulas will update. When I copy the sheet to a new tab, I can then change the reference cell to "210", and all the formulas will update.
The problem I am having is trying to make the ranges be relative. The indirect formula requires the range reference to be in "". This causes the formula to be static rather than dynamic, which makes me unable to copy the formula across rows and columns.
microsoft-excel microsoft-excel-365
microsoft-excel microsoft-excel-365
edited Jan 14 at 5:57
fixer1234
18.4k144781
asked Jan 14 at 2:58
whholderwhholder
11
edited Jan 14 at 5:57
fixer1234
18.4k144781
asked Jan 14 at 2:58
whholderwhholder
11
edited Jan 14 at 5:57
fixer1234
18.4k144781
edited Jan 14 at 5:57
fixer1234
18.4k144781
edited Jan 14 at 5:57
fixer1234
18.4k144781
18.4k144781
asked Jan 14 at 2:58
whholderwhholder
11
asked Jan 14 at 2:58
whholderwhholder
11
asked Jan 14 at 2:58
whholderwhholder
11
11
closed as too broad by Ramhound, n8te, LotPings, Simon Sheehan, Moses Jan 16 at 17:30
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as too broad by Ramhound, n8te, LotPings, Simon Sheehan, Moses Jan 16 at 17:30
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
for some reason the formula didn't copy through.
– whholder
Jan 14 at 2:59
"=IF(ActualsThrough<F$5,SUMIF('209Budget'!$F:$F,'209ActBud'!$C7,'209Budget'!I:I),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))"
– whholder
Jan 14 at 2:59
1
please edit your question instead of putting details into comments.
– teylyn
Jan 14 at 3:10
add a comment |
for some reason the formula didn't copy through.
– whholder
Jan 14 at 2:59
"=IF(ActualsThrough<F$5,SUMIF('209Budget'!$F:$F,'209ActBud'!$C7,'209Budget'!I:I),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))"
– whholder
Jan 14 at 2:59
1
please edit your question instead of putting details into comments.
– teylyn
Jan 14 at 3:10
for some reason the formula didn't copy through.
– whholder
Jan 14 at 2:59
for some reason the formula didn't copy through.
– whholder
Jan 14 at 2:59
"=IF(ActualsThrough<F$5,SUMIF('209Budget'!$F:$F,'209ActBud'!$C7,'209Budget'!I:I),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))"
– whholder
Jan 14 at 2:59
"=IF(ActualsThrough<F$5,SUMIF('209Budget'!$F:$F,'209ActBud'!$C7,'209Budget'!I:I),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))"
– whholder
Jan 14 at 2:59
1
1
please edit your question instead of putting details into comments.
– teylyn
Jan 14 at 3:10
please edit your question instead of putting details into comments.
– teylyn
Jan 14 at 3:10
add a comment |
1 Answer
1
active
oldest
votes
Assuming that the cell with 209 is in A1 of the current sheet, you can build a reference with Indirect like this:
=Indirect("'"&A1&"Budget'!$F:$F")
If your sheet name does not have spaces, you don't need to wrap it in single quotes, so,
=Indirect(A1&"Budget!$F:$F")
Applied to the ranges that reference sheet 209, the whole bundle would look like
=IF(ActualsThrough<F$5,SUMIF(indirect(A1&"Budget!$F:$F"),indirect(A1&"ActBud!$C7"),indirect(A1&"Budget!I:I")),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))
Edit after comment: If you need the column reference in the Indirect to be relative, so it updates when the formula is copied to the right, you can use the Cell() function.
Instead of
...indirect(A1&"Budget!$F:$F")...
use
...indirect(A1&"Budget!"&cell("address",F:F))...
answered Jan 14 at 3:34
teylynteylyn
17.1k22539
Hi Teylyn (great name by the way). Thank you for your answer. Here is where I am having trouble. When the column that needs to be relative is enclosed in quotations >>>indirect(A1&"Budget!I:I")<<< the column reference inessence becomes static. When I copy the formula from column I to column J, the formula still references column I.
– whholder
Jan 15 at 4:24
no problem. I added a suggestion to my answer. If that solves your issue, please mark the answer as described in the tour.
– teylyn
Jan 15 at 7:33
great idea, but perhaps I am using the formula wrong. When I use ....&cell("address",F:F) the result is $F:$1. But I think I can find a way to work with that.
– whholder
Jan 21 at 15:53
Well, the question has been closed, so this is my last post.=SUM(INDIRECT($A$1&"Budget!" &CELL("address",F1)&":"&CELL("address",F100)))
– teylyn
Jan 21 at 19:48
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Assuming that the cell with 209 is in A1 of the current sheet, you can build a reference with Indirect like this:
=Indirect("'"&A1&"Budget'!$F:$F")
If your sheet name does not have spaces, you don't need to wrap it in single quotes, so,
=Indirect(A1&"Budget!$F:$F")
Applied to the ranges that reference sheet 209, the whole bundle would look like
=IF(ActualsThrough<F$5,SUMIF(indirect(A1&"Budget!$F:$F"),indirect(A1&"ActBud!$C7"),indirect(A1&"Budget!I:I")),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))
Edit after comment: If you need the column reference in the Indirect to be relative, so it updates when the formula is copied to the right, you can use the Cell() function.
Instead of
...indirect(A1&"Budget!$F:$F")...
use
...indirect(A1&"Budget!"&cell("address",F:F))...
answered Jan 14 at 3:34
teylynteylyn
17.1k22539
Hi Teylyn (great name by the way). Thank you for your answer. Here is where I am having trouble. When the column that needs to be relative is enclosed in quotations >>>indirect(A1&"Budget!I:I")<<< the column reference inessence becomes static. When I copy the formula from column I to column J, the formula still references column I.
– whholder
Jan 15 at 4:24
no problem. I added a suggestion to my answer. If that solves your issue, please mark the answer as described in the tour.
– teylyn
Jan 15 at 7:33
great idea, but perhaps I am using the formula wrong. When I use ....&cell("address",F:F) the result is $F:$1. But I think I can find a way to work with that.
– whholder
Jan 21 at 15:53
Well, the question has been closed, so this is my last post.=SUM(INDIRECT($A$1&"Budget!" &CELL("address",F1)&":"&CELL("address",F100)))
– teylyn
Jan 21 at 19:48
add a comment |
Assuming that the cell with 209 is in A1 of the current sheet, you can build a reference with Indirect like this:
=Indirect("'"&A1&"Budget'!$F:$F")
If your sheet name does not have spaces, you don't need to wrap it in single quotes, so,
=Indirect(A1&"Budget!$F:$F")
Applied to the ranges that reference sheet 209, the whole bundle would look like
=IF(ActualsThrough<F$5,SUMIF(indirect(A1&"Budget!$F:$F"),indirect(A1&"ActBud!$C7"),indirect(A1&"Budget!I:I")),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))
Edit after comment: If you need the column reference in the Indirect to be relative, so it updates when the formula is copied to the right, you can use the Cell() function.
Instead of
...indirect(A1&"Budget!$F:$F")...
use
...indirect(A1&"Budget!"&cell("address",F:F))...
answered Jan 14 at 3:34
teylynteylyn
17.1k22539
Hi Teylyn (great name by the way). Thank you for your answer. Here is where I am having trouble. When the column that needs to be relative is enclosed in quotations >>>indirect(A1&"Budget!I:I")<<< the column reference inessence becomes static. When I copy the formula from column I to column J, the formula still references column I.
– whholder
Jan 15 at 4:24
no problem. I added a suggestion to my answer. If that solves your issue, please mark the answer as described in the tour.
– teylyn
Jan 15 at 7:33
great idea, but perhaps I am using the formula wrong. When I use ....&cell("address",F:F) the result is $F:$1. But I think I can find a way to work with that.
– whholder
Jan 21 at 15:53
Well, the question has been closed, so this is my last post.=SUM(INDIRECT($A$1&"Budget!" &CELL("address",F1)&":"&CELL("address",F100)))
– teylyn
Jan 21 at 19:48
add a comment |
Assuming that the cell with 209 is in A1 of the current sheet, you can build a reference with Indirect like this:
=Indirect("'"&A1&"Budget'!$F:$F")
If your sheet name does not have spaces, you don't need to wrap it in single quotes, so,
=Indirect(A1&"Budget!$F:$F")
Applied to the ranges that reference sheet 209, the whole bundle would look like
=IF(ActualsThrough<F$5,SUMIF(indirect(A1&"Budget!$F:$F"),indirect(A1&"ActBud!$C7"),indirect(A1&"Budget!I:I")),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))
Edit after comment: If you need the column reference in the Indirect to be relative, so it updates when the formula is copied to the right, you can use the Cell() function.
Instead of
...indirect(A1&"Budget!$F:$F")...
use
...indirect(A1&"Budget!"&cell("address",F:F))...
answered Jan 14 at 3:34
teylynteylyn
17.1k22539
Assuming that the cell with 209 is in A1 of the current sheet, you can build a reference with Indirect like this:
=Indirect("'"&A1&"Budget'!$F:$F")
If your sheet name does not have spaces, you don't need to wrap it in single quotes, so,
=Indirect(A1&"Budget!$F:$F")
Applied to the ranges that reference sheet 209, the whole bundle would look like
=IF(ActualsThrough<F$5,SUMIF(indirect(A1&"Budget!$F:$F"),indirect(A1&"ActBud!$C7"),indirect(A1&"Budget!I:I")),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))
Edit after comment: If you need the column reference in the Indirect to be relative, so it updates when the formula is copied to the right, you can use the Cell() function.
Instead of
...indirect(A1&"Budget!$F:$F")...
use
...indirect(A1&"Budget!"&cell("address",F:F))...
answered Jan 14 at 3:34
teylynteylyn
17.1k22539
edited Jan 15 at 7:32
answered Jan 14 at 3:34
teylynteylyn
17.1k22539
answered Jan 14 at 3:34
teylynteylyn
17.1k22539
answered Jan 14 at 3:34
teylynteylyn
17.1k22539
17.1k22539
Hi Teylyn (great name by the way). Thank you for your answer. Here is where I am having trouble. When the column that needs to be relative is enclosed in quotations >>>indirect(A1&"Budget!I:I")<<< the column reference inessence becomes static. When I copy the formula from column I to column J, the formula still references column I.
– whholder
Jan 15 at 4:24
no problem. I added a suggestion to my answer. If that solves your issue, please mark the answer as described in the tour.
– teylyn
Jan 15 at 7:33
great idea, but perhaps I am using the formula wrong. When I use ....&cell("address",F:F) the result is $F:$1. But I think I can find a way to work with that.
– whholder
Jan 21 at 15:53
Well, the question has been closed, so this is my last post.=SUM(INDIRECT($A$1&"Budget!" &CELL("address",F1)&":"&CELL("address",F100)))
– teylyn
Jan 21 at 19:48
add a comment |
Hi Teylyn (great name by the way). Thank you for your answer. Here is where I am having trouble. When the column that needs to be relative is enclosed in quotations >>>indirect(A1&"Budget!I:I")<<< the column reference inessence becomes static. When I copy the formula from column I to column J, the formula still references column I.
– whholder
Jan 15 at 4:24
no problem. I added a suggestion to my answer. If that solves your issue, please mark the answer as described in the tour.
– teylyn
Jan 15 at 7:33
great idea, but perhaps I am using the formula wrong. When I use ....&cell("address",F:F) the result is $F:$1. But I think I can find a way to work with that.
– whholder
Jan 21 at 15:53
Well, the question has been closed, so this is my last post.=SUM(INDIRECT($A$1&"Budget!" &CELL("address",F1)&":"&CELL("address",F100)))
– teylyn
Jan 21 at 19:48
Hi Teylyn (great name by the way). Thank you for your answer. Here is where I am having trouble. When the column that needs to be relative is enclosed in quotations >>>indirect(A1&"Budget!I:I")<<< the column reference inessence becomes static. When I copy the formula from column I to column J, the formula still references column I.
– whholder
Jan 15 at 4:24
Hi Teylyn (great name by the way). Thank you for your answer. Here is where I am having trouble. When the column that needs to be relative is enclosed in quotations >>>indirect(A1&"Budget!I:I")<<< the column reference inessence becomes static. When I copy the formula from column I to column J, the formula still references column I.
– whholder
Jan 15 at 4:24
no problem. I added a suggestion to my answer. If that solves your issue, please mark the answer as described in the tour.
– teylyn
Jan 15 at 7:33
no problem. I added a suggestion to my answer. If that solves your issue, please mark the answer as described in the tour.
– teylyn
Jan 15 at 7:33
great idea, but perhaps I am using the formula wrong. When I use ....&cell("address",F:F) the result is $F:$1. But I think I can find a way to work with that.
– whholder
Jan 21 at 15:53
great idea, but perhaps I am using the formula wrong. When I use ....&cell("address",F:F) the result is $F:$1. But I think I can find a way to work with that.
– whholder
Jan 21 at 15:53
Well, the question has been closed, so this is my last post.
=SUM(INDIRECT($A$1&"Budget!" &CELL("address",F1)&":"&CELL("address",F100)))– teylyn
Jan 21 at 19:48
Well, the question has been closed, so this is my last post.
=SUM(INDIRECT($A$1&"Budget!" &CELL("address",F1)&":"&CELL("address",F100)))– teylyn
Jan 21 at 19:48
add a comment |
for some reason the formula didn't copy through.
– whholder
Jan 14 at 2:59
"=IF(ActualsThrough<F$5,SUMIF('209Budget'!$F:$F,'209ActBud'!$C7,'209Budget'!I:I),-IFERROR(OFFSET('Month TB'!$D$1,MATCH($C7&+"-"&+$C$2,'Month TB'!$B:$B,0)-1,MATCH(F$5,'Month TB'!$D$1:$O$1,0)-1),0))"
– whholder
Jan 14 at 2:59
1
please edit your question instead of putting details into comments.
– teylyn
Jan 14 at 3:10