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]

Not sure what I've deviated from the in the second formula,

You are indexing a spill range from A to I (which is 9 columns), but you are after cols P & O, so it should be done the same way as in post#37

 

[ScottTemple]

You are indexing a spill range from A to I (which is 9 columns), but you are after cols P & O, so it should be done the same way as in post#37

I made another spill range in column X, but I think I can just combine those two spill ranges and leave them in the A2# range instead. Where I get hung up is where to put the parathesis when using multi equations in a formula. The one I'm trying to create below either gives me the "not trying to type a formula" error or returns "missing an open or close parathesis". Can you recommend a good article on the process in how many parathesis are need and where in a formula?

=IF(INDEX(A2#,,6)="","",IF((INDEX(A2#,,6)>(INDEX(A2#,,8)),((INDEX(A2#,,6)-(INDEX(A2#,,8))*1440,(1-((INDEX(A2#,,8)+(INDEX(A2#,,6))*1440)))

 

[Fluff]

At a guess that should be

=IF(INDEX(A2#,,6)="","",IF(INDEX(A2#,,6)>INDEX(A2#,,8),(INDEX(A2#,,6)-INDEX(A2#,,8))*1440,(1-INDEX(A2#,,8)+INDEX(A2#,,6))*1440))

 

[ScottTemple]

At a guess that should be

=IF(INDEX(A2#,,6)="","",IF(INDEX(A2#,,6)>INDEX(A2#,,8),(INDEX(A2#,,6)-INDEX(A2#,,8))*1440,(1-INDEX(A2#,,8)+INDEX(A2#,,6))*1440))

Thanks Fluff, you've been extremely helpful in getting this sorted out!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ScottTemple]

Fluff, coming back to this thread with a question if you have some time. Is it possible to show blanks for the Index range? Right now, it will return a zero value if a cell is blank.

Data Entry Tab

Index Spill Range Tab

Original Formula
=CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,3,4,5,6,8,9,10,11,12,15,16,17,21,22,23,26,27,28)

 

[Fluff]

Try

=let(c,=CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,3,4,5,6,8,9,10,11,12,15,16,17,21,22,23,26,27,28),if(c="","",c))

 

[ScottTemple]

I'm getting an error message with that formula.

 

[Fluff]

Remove the = sign after the c,

 

[ScottTemple]

Perfect, thanks Fluff!