Horizontal sum on rows on spilled table

[garyj]

Hello again..

Been a while since I've been around, but once again I ran into a tough one. I built a dynamic sheet in which each row gives an amount and the rightmost column gives the subtotal of that row. Since the subtotal is not part of the dynamic spill, I create it as a spill by using the hashtag symbol after the first row spilled. The actual sheet is shown below, though I narrowed the first column to hide actual names. Notice how my total formula in row 1 is correct, but in row 2 is incorrect. The formula used there is:

=IF(NOT(ISBLANK(I3#)),SUM(J3:N3)).

I3 is the first value of the half hidden Vendor column. J3:N3 would be that entire first row beginning at Current thru Over 90. I used I3# to make the total column dynamic based on the first column. It works dynamically, but is makes row 2 the same total as row 1.

Any ideas how to fix this?

temp1.png

Thanks
Gary

[June7]

Not sure how this should work. I entered your formula with adjustment for cell references under Total then filled down. The calculations are correct. Seems the # is ignored.

This use of # character is new to me. I will have to do some research.

Exactly how is the sheet dynamic? How is data in J1:N3 generated?

You could provide sample workbook.

[Aussiebear]

@june7. The # refers to a Spill

[p45cal]

Is Calculation set to Manual? If so, set it to Automatic.

[June7]

As far as I can determine from my research, referencing spill range can return only one result. Do a normal Sum formula and fill down.

[garyj]

It is set to Automatic.

[garyj]

Thanks, June.

Something said in the thread above caused me to realize I had the answer. Since each column under J2:N4 is generated by a spill formula (to answer yr question), I can go back to the table from which that is spilled to get the data instead.

Here are the formulas... for the first of each column and the one that now works for the final column.
I3 =SORT(FILTER(TableContacts[Name],TableContacts[Status]="Active"))
J3 =IF(I3#<>"",SUMIFS(INDIRECT("Y3:Y" & Y1), R3#, I3#, AA3#,TRUE),"")
K3 =IF(I3#<>"",SUMIFS(INDIRECT("Y3:Y" & Y1), R3#, I3#, AB3#,TRUE),"")
L3 =IF(I3#<>"",SUMIFS(INDIRECT("Y3:Y" & Y1), R3#, I3#, AC3#,TRUE),"")
M3 =IF(I3#<>"",SUMIFS(INDIRECT("Y3:Y" & Y1), R3#, I3#, AD3#,TRUE),"")
N3 =IF(I3#<>"",SUMIFS(INDIRECT("Y3:Y" & Y1), R3#, I3#, AE3#,TRUE),"")
O3 =IF(I3#<>"",SUMIFS(INDIRECT("Y3:Y" & Y1), R3#, I3#),"")

I have done a lot of work with referencing spills, and it seems that SUM and SUBTOTAL and SUMIFS all somehow cause the spill function # to falter. In fact I chose that INDIRECT method to fix that problem in totaling the amounts owing because the SUMIFS would not work otherwise. I don't know why... but it seems to be the case.

I3# is a single column spill. If it were a multi-column spill, I'd have to reference the first column by CHOOSECOLS(I3#,1). There have been instances when I was able to create a new single column spill by referencing the first one with the hashtag. Eg1: =I3#-I3+K3. Eg2: = If I3#<>"" then K3.

I can't tell you how helpful it has been to code this way. This particular spreadsheet is a good example of why. The General Ledger (GL) is a double entry listing of all transactions. GL2 is a table that only contains the Accounts Payable terms, due date, interest, account info, etc. I create a Filter function in a hidden spreadsheet to obtain all the lines that relate to the A/P side of each transaction where an amount is still owed. To create an aging report is a challenge, because under 1-30 would be a subtotal of those invoices that are due and the time since the due date is less between 0 and 31 days. Imagine the forumula required for that! So rather than build such a formula, I create extra columns in the Filtered GL/GL2 Spill. One column tests the dates of each transaction and sets it as TRUE if the time since the due date is less than 31 days. I do that for each column in the aging report. Each requires an added column, spilled by the reference to a previous spilled column(s). That way I only need a simpler formula in the aging report that I was asking about.

Anyway, Thanks again.
Gary

PS: The reason I didn't post a sheet example is because the sheets contain a lot of private information, and I don't know how many people can view it on here nor how to protect that once it is online. It's a fairly large set of pages.

[June7]

To provide file, you make a copy then remove or anonymize sensitive data.

[garyj]

Thx. That is easier with smaller files. I suppose I would have only to change names and remove addresses in Contacts and then do the same on the General Ledger. I will keep that in mind for another question. Thank you.