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.
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"))