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]

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.

 

[ScottTemple]

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

So the X toward the end of the formula ...(x*(INDEX(d,,5)^2)))/12))) refers back to the Density column, why does that not read INDEX(d,,10) instead, as Density is in the tenth column?

 

[Fluff]

Because col J is not in the filtered data, its the xlookup function.

 

[ScottTemple]

Because col J is not in the filtered data, its the xlookup function.

Gotcha. Another quick question, I am trying to add another formula in the subsequent column, but I'm receiving an error. Here is the formula in the first tab, followed by what I believe it needs to be in the Spill formula string:

=IF(E2="","",(F2^2)/(E2^2)-0.012)
=IF(INDEX(d,,4)="","",((INDEX(d,,6)^2)/(INDEX(d,,4)^2))-0.012))

 

[Fluff]

Col E is the 5th column not the 4th.

 

[ScottTemple]

Col E is the 5th column not the 4th.

Thanks for catching that, but I'm still receiving the following error message:

 

[Fluff]

Without knowing what the complete formula is, I cannot help.

 

[ScottTemple]

Without knowing what the complete formula is, I cannot help.

The Spill formula is:

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

and I want to add on:
IF(INDEX(d,,4)="","",((INDEX(d,,5)^2)/(INDEX(d,,4)^2))-0.012))

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