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]

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.

 

[ScottTemple]

That's right.

Can you review the below formula and let me know where I'm making a mistake? It's the same error as above, but I can't figure out how my last IF statement is failing (I added spaces for clarity, they're not in the formula):

=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), IF(INDEX(d,,5)="",""(INDEX(d,,5)^2)/(INDEX(d,,3)^2)))

 

[ScottTemple]

You're missing a comma & the final closing bracket.

D'oh! Thanks Fluff!

 

[ScottTemple]

You're missing a comma & the final closing bracket.

Actually, might need your help again. My next formula in the string is grabbing from data that was not a part of the orignal tab, but from the Spill data, is ths possible? After adding another column/formula I'm getting a #REF! error:

=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),IF(INDEX(d,,5)="","",(INDEX(d,,5)^2)/(INDEX(d,,3)^2)), IF(INDEX(d,,13)="","",(INDEX(d,,13)-INDEX(d,,14)))))

 

[Fluff]

Let's do this another way.
In A2 put

=CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,3,4,5,6,8,9,15,16)

In J2 put

XLOOKUP(INDEX(A2#,,2),REF!$E$2:$E$8,REF!$F$2:$F$8)

in K2

=,INDEX(A2#,,3)^2*1.517

J2

=IF(INDEX(A2#,,5)="","",(((INDEX(A2#,,6)*4)/3.14)/(J2#*(INDEX(A2#,,5)^2)))/12)

and so on.

 

[ScottTemple]

Let's do this another way.
In A2 put

=CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,3,4,5,6,8,9,15,16)

In J2 put

XLOOKUP(INDEX(A2#,,2),REF!$E$2:$E$8,REF!$F$2:$F$8)

in K2

=,INDEX(A2#,,3)^2*1.517

J2

=IF(INDEX(A2#,,5)="","",(((INDEX(A2#,,6)*4)/3.14)/(J2#*(INDEX(A2#,,5)^2)))/12)

and so on.

Sorry about the delay responding back, got tied up the past few days.

These formulas are working, but what should they be once I go outside of that A2# range? Right now, A2# cover columns A-I, but additional formulas, as shown below with O2, are pulling data from outside of this A2# range, specifically columns M & N. My formula in O2 is my attempt to determine what the update formula would be.

 

[Fluff]

Are the formulae in M & N both spill ranges?

 

[ScottTemple]

Are the formulae in M & N both spill ranges?

No only columns A - I spill, columns J - N are outside that range but pull from A - I with their formulas, column O would pull from the J - N data.