Added Column Formula in a Spill Range - Cont.

[ScottTemple]

Hello,

I am attempting to connect two tables together, so that when table1 has a new row added, table2 automatically adds that row. While working with @SanjayGMusafir on this site, he was able to assist me with creating a Spill formula into a second tab, in place of a second table (due to errors with Spill data and tables). By taking the Spill data (headers in green), I am adding additional columns that contain formulas that pull from this Spill data (headers in orange).

Either continuing on with this setup or determining how to connect two tables with each other, so that when new rows of data are added to table1, table2 is automatically updated. *My company does not allow me to download third-party add ons, so I am unable to install XL2BB.

Tabe2 or Tab2

 

[Fluff]

All those formulae should spill, but you said they are not spill ranges.
Do they spill down?

 

[ScottTemple]

All those formulae should spill, but you said they are not spill ranges.
Do they spill down?

Oh, yea they do. What I mean by spill range is that columns A-I pull data from my first tab with just that one formula in A2; so A2 fill out A-I all the way down. The formulas in J2-N2 pull from the data in the A-I columns and they spill all the way down as well. The problem I run into is when I input a new formula that only takes data from the J-N columns, that 'A2#' part of the formula no longer works as its looking only in the A-I columns and I need to pull from the J-N columns. So the O formula would need to spill as well, but it would look something like =IF(M2="","",M2-N2), just not sure how to create the formula to encompass that so it spills as well.

 

[Fluff]

Just use the #signs as I have showed with the other formulae.

 

[ScottTemple]

Just use the #signs as I have showed with the other formulae.

That was my assumption and I came up with the below formula, but I get a #REF! error

=IF(INDEX(M2#,,13)="","",(((INDEX(M2#,,13)-(INDEX(N2#,,14))))))

 

[Fluff]

Col M is only one column wide, so you cannot index the 13th column. Just use M2# & N2#

 

[ScottTemple]

Col M is only one column wide, so you cannot index the 13th column. Just use M2# & N2#

Sorry, this type of formula is new to me, would that be something like:

=IF(M2#,,13)="","",(((M2#,,13)-(N2#,,14)))

 

[Fluff]

It should be

=IF(M2#="","",M2#-N2#)

 

[ScottTemple]

It should be

=IF(M2#="","",M2#-N2#)

Ah yea that did the trick. I have two last formula questions I'm hoping you can assist me with, and I'll leave you alone forever lol. I created a second A2# range in column X through AC.

Formulas
=IF(P2="","",IF(P2>O2,(P2-O2)*1440,(1-O2+P2)*1440))
=IF(F2="","",F2-SUM(Y2:Z2,AB2,AC2))

My attempts
=IF(INDEX(A2#,,11)="","",IF(((INDEX(A2#,,11)>(INDEX(A2#,,10),(((INDEX(A2#,,11)-(INDEX(A2#,,10)*1440,(1-((INDEX(A2#,,10)+(INDEX(A2#,,11)*1440)))
=IF(INDEX(A2#,,6)="","",(((INDEX(A2#,,6)-SUM(INDEX(X2#,,25:X2#,,26),(INDEX(X2#,,28:X2#,,29)))

 

[Fluff]

You cannot use SUM on a spill range like that, as it will simply add everything up & return a single value.
For the 1st formula, you seem to have ignored everything in this thread.

 

[ScottTemple]

You cannot use SUM on a spill range like that, as it will simply add everything up & return a single value.
For the 1st formula, you seem to have ignored everything in this thread.

Is there a work around for the Sum function?
Not sure what I've deviated from the in the second formula, I've been following the previous formulas that are working?