Hi Canoafarms
There are two methods to remove (or hide) the empty rows.
1- You set up a filter to only show the rows that have # > 0. Or...
2- You go full dynamic with the following.
Enter these formulas in table Class 1.
in A2:
IFERROR(XLOOKUP("x",INDIRECT($J2),INDIRECT(ADDRESS(ROW()+$K1,COLUMN(),,,"Table 1")&":"&ADDRESS(ROWS(Table 1::Name),COLUMN()))),"")
in B2:
IFERROR(XLOOKUP("x",INDIRECT($J2),INDIRECT(ADDRESS(ROW()+$K1,COLUMN(),,,"Table 1")&":"&ADDRESS(ROWS(Table 1::'#'),COLUMN()))),"")
in C2:
IFERROR(XLOOKUP("x",INDIRECT($J2),INDIRECT(ADDRESS(ROW()+$K1,COLUMN(),,,"Table 1")&":"&ADDRESS(ROWS(Table 1::Horse),COLUMN()))),"")
in J2 (after the score columns):
IFERROR(ADDRESS(ROW()+K1,$L2,,,"Table 1")&":"&ADDRESS(ROWS(Table 1::D),$L2,),"")
in K2:
IFERROR(XMATCH("x",INDIRECT($J2))−1+K1,"")
in L2:
XMATCH(VALUE(TEXTAFTER(TEXTBEFORE(REFERENCE.NAME(A2,1),"::")," ")),Table 1::$1:$1)
I know, data in columns K and L could only be computed only once instead or being repeated on every line, but I made it this way so that adding or removing rows doesn't make a mess.
Copy down these formulas for as many rows as you need. Once you validate that it works without error, you can hide the last three columns.
Now you can simply copy table Class 1 and give the copy another name like Class 12. Everything will work automatically for the new table. The formula in column L assumes that table names always end with a space and a number.
Caution: Once the scores have started to be entered, DO NOT add to or remove a horse from a class, because the scores in the Class tables are not following the entries, they are static, whereas the horse list is dynamic.
The picture shows table Class 1 with all columns visible, and table Class 12 with the last three columns hidden.
