Automatically Adjusting Rows in a Range

Hi schlag58

Here are some possible solutions I can think of at the moment. You can try adapting one of these options to your case.

Option 1: Use Dynamic Formulas with the IFERROR Function

To avoid values like #N/A or #VALUE! from interfering with your formulas, you can use the IFERROR function. This allows the formula to continue working even if an error occurs.

Example:

=IFERROR(VLOOKUP(A2, Players_Table, 2, FALSE), "")

Here, the IFERROR function ensures that if the VLOOKUP generates an error, Excel will return a blank cell, preventing a negative impact on other formulas.

Option 2: Control the Number of Players with Formulas

Create a cell to automatically count the number of players present, for example:

=COUNTA(A2:A100)

 Here A2:A100 would be the range where the player names are listed.

Now, use this value to adjust your ranking and summing formulas. Combine this count with formulas that avoid processing empty or erroneous cells.

Option 3: Filter Valid Rows

Use the FILTER function (if available in your version of Excel) to create a dynamic range that automatically excludes cells with errors:

This example filters all rows that have valid (numeric) values in column A.

=FILTER(A2:B100, ISNUMBER(A2:A100))

Option 4: Using VBA

If you want to fully automate the table updates based on the number of players, you can use a small VBA script that checks the player count and adjusts the tables accordingly.

Example:

Sub AdjustTables()
    Dim lastRow As Long
    ' Assuming the player names are in column A
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Here you can adjust the table you want to resize dynamically
    Range("Players_Table").Resize(lastRow, 2).Sort Key1:=Range("A2"), Order1:=xlAscending
End Sub