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

 

[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))

 

[Fluff]

You need to add the new formula inside the HSTACK, not at the end of the formula like

=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)))

 

[ScottTemple]

You need to add the new formula inside the HSTACK, not at the end of the formula like

=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)))

I think I see the patter now, so if I want to continue to add more columns/formulas I remove the last two parathesis at the end, add my new formula, and then once I've completed the entire string, add those last two parathesis?

The first one is my addition and the second is your correction, where we see those two parathesis removed:

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)))

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)))

 

[Fluff]

That's right.