Hello,
Wondering if someone could help me out with a quick solution?
Here's the original formula: =-SUM(INDEX(SUMIFS(Table4300[Amount],Table4300[Restriction],INDEX(CATBOARD2[item1]:CATBOARD2[item29],MATCH([@[Temporary Restrictions]],CATBOARD2[Categories],0),0)),0))-SUM(INDEX(SUMIFS(Table4310[Amount],Table4310[Restriction],INDEX(CATBOARD2[item1]:CATBOARD2[item29],MATCH([@[Temporary Restrictions]],CATBOARD2[Categories],0),0)),0))
So I need a part of this formula to be dynamic as I may add columns to my table through the year.
The part I need help with: CATBOARD2[item1]:CATBOARD2[item29]
So the first part of this will be fine as item 1 will always be the first count. However, item29 may go up as columns are added. So if I added three columns, the third column added would be item32.
For this to be dynamic I was trying to use the functions concatenate/& and counta for the headers (=CATBOARD2[#Headers]-2) and subtract the first the two columns (col 1 is for a match, and col2 is reference somewhere else) as those are not needed to get my count of items there are [itemXX].
XX of the item above is what I would like to be dynamic.
If anyone could help, it would be greatly appreciated. If the file is needed please let me know.
Thanks,
Ant
Bookmarks