So, I have this formula here (which works)
=SUM(SUMIF('Inventory 11.09.2018'!$B$2:$B$47;B2;'Inventory 11.09.2018'!$C$2:$C$47);SUMIF($H:$H;B2;$I:$I))
and I'd like to achieve, that instead of the actual worksheet name (Inventory 11.09.2018) the formula always refers to the last worksheet of the table. Every time an Inventory is done a worksheet is added to the table.
I did a bit of research and found this solution: How do I get my formula to always reference to the last sheet?
What I did with this solution is to define two range names.
wshNames
=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))
wshNameLast
=INDEX(wshNames,COUNTA(wshNames)+RAND()*0)
I then proceeded to alter my formula into this:
=SUM(SUMIF(wshNameLast&"!$B$2:$B$47";B2;wshNameLast&"!$C$2:$C$47");SUMIF($H:$H;B2;$I:$I))
But it throws this error message when hitting return: Excel_error_in_formula
Since this is German, it basically says:
There's a problem with this formula.
You didn't want to enter a formula?
And then explains what an apostrophe before a formula does.
If I use the defined names solely in a formula like
=wshNameLast
they work. If I remove the "SUM" around the formula like this, just to test if it would work without "SUM"
SUMIF('Inventory 11.09.2018'!$B$2:$B$47;B2;'Inventory 11.09.2018'!$C$2:$C$47)
it also doesn't work. So I figure there's something wrong the defined range names in combination with the formula the way I wanna use it.
Question is: What am I doing wrong and how can I get to work? Also, I would like to solve this problem without VB/VBA if possible.
You should use the INDIRECT function to convert the string wshNameLast&"!$B$2:$B$47" to a range. Like this INDIRECT(wshNameLast&"!$B$2:$B$47").
The complete formula:
=SUM(SUMIF(INDIRECT(wshNameLast&"!$B$2:$B$47");B2;INDIRECT(wshNameLast&"!$C$2:$C$47"));SUMIF($H:$H;B2;$I:$I))