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]

How about

=LET(d,CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,3,4,5,6,8,9,15,16),x,XLOOKUP(INDEX(d,,2),Sheet8!$E$2:$E$8,Sheet8!$F$2:$F$8),HSTACK(d,x,INDEX(d,,3)^2*1.517,IF(INDEX(d,,5)="","",(((INDEX(d,,7)*4)/3.14)/(x*(INDEX(d,,3)^2)))/12)))

 

[SanjayGMusafir]

How about

=LET(d,CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,3,4,5,6,8,9,15,16),x,XLOOKUP(INDEX(d,,2),Sheet8!$E$2:$E$8,Sheet8!$F$2:$F$8),HSTACK(d,x,INDEX(d,,3)^2*1.517,IF(INDEX(d,,5)="","",(((INDEX(d,,7)*4)/3.14)/(x*(INDEX(d,,3)^2)))/12)))

@Fluff you always surprise me in more than many ways...

 

[ScottTemple]

Fluff, Sanjay, that worked! However, can you explain the Index formula to me, so I can add more columns/formulas? Will this work in a SharePoint shared Excel doc?

 

[Fluff]

Will this work in a SharePoint shared Excel doc?

Should do, but I don't have sharepoint so can't say for sure.

The index function is just pulling the relevant column from the filter function.

 

[ScottTemple]

Should do, but I don't have sharepoint so can't say for sure.

The index function is just pulling the relevant column from the filter function.

Sorry, I'm not sure how that works exactly. Also, my Footage column is coming out incorrect, I'm getting far less footage than I should be getting.

 

[ScottTemple]

Sorry, I'm not sure how that works exactly. Also, my Footage column is coming out incorrect, I'm getting far less footage than I should be getting.

Here is the results I'm receiving for the Footage based on the formula you provided versus what we orignally have:

IF(INDEX(d,,5)="","",(((INDEX(d,,7)*4)/3.14)/(x*(INDEX(d,,3)^2)))/12))) - 29'

IF(F2="","",(((H2*4)/3.14)/(R2*(F2^2)))/12) - 5,328'

 

[Fluff]

Are the first 9 columns from the formula the correct data?

 

[ScottTemple]

Are the first 9 columns from the formula the correct data?

Fluff, my mistake, my original positing had an error in the Footage formula and I repeated that error in my last posting. It should have read:

=IF(E2="","",(((F2*4)/3.14)/(J2*(E^2)))/12)

*I believe I figured out how the Index formula works. By changing IF(INDEX(d,,5)="","",(((INDEX(d,,7)*4)/3.14)/(x*(INDEX(d,,3)^2)))/12))) to IF(INDEX(d,,5)="","",(((INDEX(d,,6)*4)/3.14)/(x*(INDEX(d,,5)^2)))/12))), I am now seeing the correct footage come through. This also help me determine how to build out this formula to include additional columns to add more data. I am curious as to how to determine the Index(d,,3) for example, along with the X in the (x*(INDEX(d,,5)...

 

[Fluff]

Index(d,,3) is the 3rd column from the filter formula (ie col C) & the x is the result of the Xlookup function.