excel - How to use automatically update formula without dragging

Question

I have a table in Sheet1 that looks like this

**Sport**
Basketball
Basketball
Basketball
Volleyball
Volleyball
Football
Football
Football
Football
Football
Football
Hockey
Hockey
Hockey

I have a table in Sheet2 that looks like:

SPORT   Basketball  Volleyball  Football    Hockey
SCORE       3           2          6           3

I applied the following formula in B1:

=TRANSPOSE(UNIQUE(FILTER(Sheet1!$A$2:$A$15,Sheet1!$A$2:$A$15<>"")))

formula in B2:

=COUNTIF(Sheet1!$A$2:$B$15,Sheet2!B1)

However when the column in Sheet1 is updated. For example, changing one of the hockey fields to Golf, this is updated in the HEADER but the formula and formatting below is not carried across WITHOUT having to physically drag it across.

SPORT   Basketball  Volleyball  Football    Hockey    Golf
SCORE       3           2          6           3

As you can see the score for Gold is empty.... I need this to be filled automatically. Is there a way that I can have excel automatically "pull" the formula that is contiguous in the column into the added row?

(Simplified data and formula for ease of understanding!!)

Answer 1

Use the spilled range operator # to reference the entire spilled range:

=COUNTIF(Sheet1!$A$2:$B$15,B1#)