Forum Discussion
Dynamic sum of row data in non-contiguous columns based on changing date
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).
I might be making some progress but using Excel 365.
- How do I expand the range (# of rows in Column A) to accommodate down through row 18 ("DO NOT USE")? When I click F2 to edit, I can't use the mouse to pull the range down the addditional rows?
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).
- Thanks, Peter. I think I figured it out. Really appreciate your help.
PeterBartholomew1 Your solution appears to deliver the desired result. Is there anything else you believe is required? And Thanks!
