I have a dynamic named range. Is it possible to have a formula in an adjacent column that is also dynamic?

[helpneeded2]

I have a named range of data that I have defined dynamically using the OFFSET method.

The range is a fixed 8 columns, and a varying amount of rows.

In the 9th column, I have a formula that is to be applied to 4 of the columns in the range, based on a conditional criteria. The rows of data could be anywhere from 10 rows of data to 100,000 rows of data. Rather than copying down the formula over 100k rows, I was wondering if there was another way to do this so that it dynamically populates only in the rows that have data. (I hope that all made sense -- I mix up my terminology sometimes).

I want to have the formula below to be utilized on the 4 rows of the table "transactionData_filtered".

=IF(J2="",0,IF(ISERROR(MATCH(J2,transactionType_list,0)),0,SUM(K2:N2)*IF(VLOOKUP(J2,transactionType_table,2,FALSE)="Positive",1,-1)))

Is there a way via formula to apply this dynamically, or is this beyond the features of Excel?

 

[shift-del]

Hi

Make use of an Excel table (CTRL-T). Formulas and formatting are copied down when new data is appended.

 

[helpneeded2]

Hi

Make use of an Excel table (CTRL-T). Formulas and formatting are copied down when new data is appended.

Unfortunately using a table for the data does not work. It is how I wanted to do it first, however my filter formula just results in a #spill error when I convert the data to a table. I have done this on blank spreadsheet, so I know there is no other data in the way that is causing the SPILL error. Unfortunately the formula is not table friendly, but works fine when not used in a table.

=LET(f,FILTER(INDEX(table_jackpotDataRaw,SEQUENCE(ROWS(table_jackpotDataRaw)),XMATCH(A1:P1,table_jackpotDataRaw[#Headers])),table_jackpotDataRaw[Date  Time]<>""),IFERROR(f+0,f))

 

[shift-del]

I'm confused. If the formula spills then there is no need to copy it down and everything is fine.