By using this website, you agree to our Terms of Use (click here)
I have an annual 12 month P&L that displays actual columns up to the period chosen, then displays the budget ledger after that. I am wonder if there is a way to sum what is visible (Actual ledger + Budget ledger for the total 12 months). Currently the last column only displays the YTD balance. Any ideas if this can be accomplished?
Hi Brian,
I haven't actually tried this formula to see if it would work, but what about something like the following?
Note: The formula isn't complete, but I think you can get the idea.
=Switch(Left(@StartPeriod,2)='01',B+E+G+I+K+M+O+Q+S+U+W+Y,Left(@StartPeriod,2)='02',B+D+G+I+K+M+O+Q+S+U+W+Y,Left(@StartPeriod,2)='03',B+D+F+I+K+M+O+Q+S+U+W+Y,0)
So that formula would go in the value row? What would the data source be?
Try leaving the Data Source field empty, changing the Type field from GL to Calc, and putting the formula in the Value field.
It looks like that is going to work. Thanks.
Cool, I'd love to see the completed formula and whether or not it worked when you get a chance.
=Switch(Left(@StartPeriod,2)='01',B+E+G+I+K+M+O+Q+S+U+W+Y, Left(@StartPeriod,2)='02',B+D+G+I+K+M+O+Q+S+U+W+Y, Left(@StartPeriod,2)='03',B+D+F+I+K+M+O+Q+S+U+W+Y,Left(@StartPeriod,2)='04',B+D+F+H+K+M+O+Q+S+U+W+Y,Left(@StartPeriod,2)='05',B+D+F+H+J+M+O+Q+S+U+W+Y,Left(@StartPeriod,2)='06',B+D+F+H+J+L+O+Q+S+U+W+Y,Left(@StartPeriod,2)='07',B+D+F+H+J+L+N+Q+S+U+W+Y,Left(@StartPeriod,2)='08',B+D+F+H+J+L+N+P+S+U+W+Y,Left(@StartPeriod,2)='09',B+D+F+H+J+L+N+P+R+U+W+Y,Left(@StartPeriod,2)='10',B+D+F+H+J+L+N+P+R+T+W+Y,Left(@StartPeriod,2)='11',B+D+F+H+J+L+N+P+R+T+V+Y,Left(@StartPeriod,2)='12',B+D+F+H+J+L+N+P+R+T+V+X)
It is working. Last challenge I am having is with my Row Set. I have some Rows that are % of Sales. Those are getting summed in my total column instead of giving me true total % of Sales.
Sweet! That's one mean looking formula!
Can you start a separate Topic for the Net Sales thing since a lot of people ask about that separately? I'm traveling today, but I can reply tomorrow or this weekend with the solution.
Online Members
