Variable within SUMIFS [closed]
up vote
-1
down vote
favorite
I have an Excel workbook which has multiple sheets. Each sheet represent a customer's monthly income and the last column of each sheet has total income. So Column "S" in each sheet has total income.
Now, I have a summary sheet in which I want to show total income of each of those customers whose income is appearing in those individual sheets. I'm using "SUMIFS" but I have to change formula for every customer because every customer's income is in different sheet and each sheet has it's own address/reference even though the columns are same.
My question is, can I make the sheet's name variable in my summary sheet so that I don't have to change formula for every customer. I can just add the sheet's name in my summary sheet and drag down the formula so that formula can pick sheet's name from there.
I hope I'm making sense.
Let me know if this is doable without Macros/VBA.
Thanks.
excel formula sumifs
asked Nov 22 at 15:15
ram singh
32
closed as too broad by QHarr, Roman Pokrovskij, Skynet, Rob, edgerunner Nov 22 at 22:35
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 |
up vote
-1
down vote
favorite
I have an Excel workbook which has multiple sheets. Each sheet represent a customer's monthly income and the last column of each sheet has total income. So Column "S" in each sheet has total income.
Now, I have a summary sheet in which I want to show total income of each of those customers whose income is appearing in those individual sheets. I'm using "SUMIFS" but I have to change formula for every customer because every customer's income is in different sheet and each sheet has it's own address/reference even though the columns are same.
My question is, can I make the sheet's name variable in my summary sheet so that I don't have to change formula for every customer. I can just add the sheet's name in my summary sheet and drag down the formula so that formula can pick sheet's name from there.
I hope I'm making sense.
Let me know if this is doable without Macros/VBA.
Thanks.
excel formula sumifs
asked Nov 22 at 15:15
ram singh
32
closed as too broad by QHarr, Roman Pokrovskij, Skynet, Rob, edgerunner Nov 22 at 22:35
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.
support.office.com/en-us/article/…?
– GSerg
Nov 22 at 15:20
Sounds like you want INDIRECT to me.
– Rory
Nov 22 at 15:50
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
I have an Excel workbook which has multiple sheets. Each sheet represent a customer's monthly income and the last column of each sheet has total income. So Column "S" in each sheet has total income.
Now, I have a summary sheet in which I want to show total income of each of those customers whose income is appearing in those individual sheets. I'm using "SUMIFS" but I have to change formula for every customer because every customer's income is in different sheet and each sheet has it's own address/reference even though the columns are same.
My question is, can I make the sheet's name variable in my summary sheet so that I don't have to change formula for every customer. I can just add the sheet's name in my summary sheet and drag down the formula so that formula can pick sheet's name from there.
I hope I'm making sense.
Let me know if this is doable without Macros/VBA.
Thanks.
excel formula sumifs
asked Nov 22 at 15:15
ram singh
32
I have an Excel workbook which has multiple sheets. Each sheet represent a customer's monthly income and the last column of each sheet has total income. So Column "S" in each sheet has total income.
Now, I have a summary sheet in which I want to show total income of each of those customers whose income is appearing in those individual sheets. I'm using "SUMIFS" but I have to change formula for every customer because every customer's income is in different sheet and each sheet has it's own address/reference even though the columns are same.
My question is, can I make the sheet's name variable in my summary sheet so that I don't have to change formula for every customer. I can just add the sheet's name in my summary sheet and drag down the formula so that formula can pick sheet's name from there.
I hope I'm making sense.
Let me know if this is doable without Macros/VBA.
Thanks.
excel formula sumifs
excel formula sumifs
asked Nov 22 at 15:15
ram singh
32
asked Nov 22 at 15:15
ram singh
32
asked Nov 22 at 15:15
ram singh
32
asked Nov 22 at 15:15
ram singh
32
asked Nov 22 at 15:15
ram singh
32
32
closed as too broad by QHarr, Roman Pokrovskij, Skynet, Rob, edgerunner Nov 22 at 22:35
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 QHarr, Roman Pokrovskij, Skynet, Rob, edgerunner Nov 22 at 22:35
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.
support.office.com/en-us/article/…?
– GSerg
Nov 22 at 15:20
Sounds like you want INDIRECT to me.
– Rory
Nov 22 at 15:50
add a comment |
support.office.com/en-us/article/…?
– GSerg
Nov 22 at 15:20
Sounds like you want INDIRECT to me.
– Rory
Nov 22 at 15:50
support.office.com/en-us/article/…?
– GSerg
Nov 22 at 15:20
support.office.com/en-us/article/…?
– GSerg
Nov 22 at 15:20
Sounds like you want INDIRECT to me.
– Rory
Nov 22 at 15:50
Sounds like you want INDIRECT to me.
– Rory
Nov 22 at 15:50
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
Yes you solve it without VBA, use the formula indirect and enter the sheet name as a string or reference.
String:
=INDIRECT("SheetName!A2")
Reference example - write sheet name in cell A1 and the formula will look for the answer in cell A2 for that sheet:
=INDIRECT(A1&"!A2")
If you use spaces in a sheet you must also add a single quote ' before and after the name.
Avoid mixing references to sheets with and without spaces to make it simpler,
otherwise try
=IFERROR(INDIRECT("'"&A1&"'!A2");INDIRECT(A1&"!A2"))
answered Nov 22 at 15:57
user 88 91
184
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
Yes you solve it without VBA, use the formula indirect and enter the sheet name as a string or reference.
String:
=INDIRECT("SheetName!A2")
Reference example - write sheet name in cell A1 and the formula will look for the answer in cell A2 for that sheet:
=INDIRECT(A1&"!A2")
If you use spaces in a sheet you must also add a single quote ' before and after the name.
Avoid mixing references to sheets with and without spaces to make it simpler,
otherwise try
=IFERROR(INDIRECT("'"&A1&"'!A2");INDIRECT(A1&"!A2"))
answered Nov 22 at 15:57
user 88 91
184
add a comment |
up vote
0
down vote
accepted
Yes you solve it without VBA, use the formula indirect and enter the sheet name as a string or reference.
String:
=INDIRECT("SheetName!A2")
Reference example - write sheet name in cell A1 and the formula will look for the answer in cell A2 for that sheet:
=INDIRECT(A1&"!A2")
If you use spaces in a sheet you must also add a single quote ' before and after the name.
Avoid mixing references to sheets with and without spaces to make it simpler,
otherwise try
=IFERROR(INDIRECT("'"&A1&"'!A2");INDIRECT(A1&"!A2"))
answered Nov 22 at 15:57
user 88 91
184
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
Yes you solve it without VBA, use the formula indirect and enter the sheet name as a string or reference.
String:
=INDIRECT("SheetName!A2")
Reference example - write sheet name in cell A1 and the formula will look for the answer in cell A2 for that sheet:
=INDIRECT(A1&"!A2")
If you use spaces in a sheet you must also add a single quote ' before and after the name.
Avoid mixing references to sheets with and without spaces to make it simpler,
otherwise try
=IFERROR(INDIRECT("'"&A1&"'!A2");INDIRECT(A1&"!A2"))
answered Nov 22 at 15:57
user 88 91
184
Yes you solve it without VBA, use the formula indirect and enter the sheet name as a string or reference.
String:
=INDIRECT("SheetName!A2")
Reference example - write sheet name in cell A1 and the formula will look for the answer in cell A2 for that sheet:
=INDIRECT(A1&"!A2")
If you use spaces in a sheet you must also add a single quote ' before and after the name.
Avoid mixing references to sheets with and without spaces to make it simpler,
otherwise try
=IFERROR(INDIRECT("'"&A1&"'!A2");INDIRECT(A1&"!A2"))
answered Nov 22 at 15:57
user 88 91
184
edited Nov 22 at 16:15
answered Nov 22 at 15:57
user 88 91
184
answered Nov 22 at 15:57
user 88 91
184
answered Nov 22 at 15:57
user 88 91
184
184
add a comment |
add a comment |
support.office.com/en-us/article/…?
– GSerg
Nov 22 at 15:20
Sounds like you want INDIRECT to me.
– Rory
Nov 22 at 15:50