Forum Discussion
Solved
Dynamic sum of row data in non-contiguous columns based on changing date
On attached file, I am trying to build a formula for each row (starting at row 6) that will dynamically sum the non-contiguous (alternate) columns starting with column J through column BE; however, ...
That is because of the way in which I select a relative row out of the budget or actuals table. The formula
= XLOOKUP(@ProjectName,ProjectName,RevenueBudget) used within = LET( projectBRevenue, XLOOKUP(@ProjectName,ProjectName,RevenueBudget), SUMIFS(projectBRevenue, DateLine, ">0"))will only be extendable as far down as the named ranges ProjectName, RevenueActual and RevenueBudget are defined. I tend to use Excel Tables for such problems and define the names in terms of structured references but it is possible to resize the ranges in Name Manager or even extend them down beyond the active range (if the formulas work and you need more explanation, I can provide that).
People tend to build cross-tabular models instead of IPO models,
I use a IPO approach in the attached file. Note: Due to normalization the original table had to be split in two tables.