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]

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

 

[Fluff]

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

 

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

 

[Fluff]

In that case you are not using the formulae I have given already given you.

 

[ScottTemple]

In that case you are not using the formulae I have given already given you.

Not sure what you mean. I took the formulas below and inputted them in and then continued on with that format for columns M & N, as they pulled from that A2# range, which is Spill data that goes from column A to I.

In A2 put
Excel Formula:
=CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,3,4,5,6,8,9,15,16)

In J2 put
Excel Formula:
XLOOKUP(INDEX(A2#,,2),REF!$E$2:$E$8,REF!$F$2:$F$8)

In K2 put
Excel Formula:
=,INDEX(A2#,,3)^2*1.517

In L2 put
Excel Formula:
=IF(INDEX(A2#,,5)="","",(((INDEX(A2#,,6)*4)/3.14)/(J2#*(INDEX(A2#,,5)^2)))/12)

Formulas for column M & N I created based on your formula examples above. M & N pull from that spill range, but my new column, O, pulls from M & N, which is outside of that spill range.

M2
=IF(INDEX(A2#,,4)="","",(((INDEX(A2#,,5)^2)/(INDEX(A2#,,4)^2))-0.012))

N2
=IF(INDEX(A2#,,5)="","",(((INDEX(A2#,,5)^2)/(INDEX(A2#,,3)^2))))