I have a table in Sheet1 that looks like this

```**Sport**
Volleyball
Volleyball
Football
Football
Football
Football
Football
Football
Hockey
Hockey
Hockey
```

I have a table in Sheet2 that looks like this:

```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<>"")))`

The formula in B2:

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

When Sheet1's column, however, is updated For instance, if one of the hockey fields is converted to a golf course, the HEADER is modified, but the formatting and formula below are not transferred automatically.

```SPORT   Basketball  Volleyball  Football    Hockey    Golf
SCORE       3           2          6           3```

The score for Gold is zero, as you can see. I require automatic filling of this. Is there a way for Excel to "pull" the formula that is adjacent to the column into the newly created row automatically?

Jan 6, 2023 in Others 341 views

## 1 answer to this question.

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

Change it to:

`=COUNTIF(Sheet1!\$A\$2:\$B\$15,B1#)`
• 63,420 points

## Excel formula gives error when write using Apache-poi library in Java

I tested some sample code. The cell ...READ MORE

## Add some word to all or some rows in Excel?

Solution: Select All cells that want to change. Right ...READ MORE

## In MS Excel what formula can be used to calculate growth when CAGR for the period is already given?

If those are true CAGR's, then just ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE