I am trying to add a formula in an excel document which needs to access data from separate sheet (in the same book), but it keeps trying to refer to another book. I created the formulas in a different book for testing purposes, and got it working perfectly fine. There were two types of functions involved; one was a CountIf function for how many rows had a certain value in one column, and the other funcion was counting how many rows had a certain value in one column and another value in a different column. The formula I used for the first account was:

```
=COUNTIF(Sheet1!$H$2:H$65536, "Data Protection Awareness")
```

And the second formula was:

```
=COUNTIFS(Sheet1!I$2:$M$65536,"Column2",Sheet1!D$2:$H$65536,"Column1")
```

This is how I set them up in the test environment, and it worked fine. However, when I copied it into the other spreadsheet, it kept trying to reference back to the original spreadsheet, which was no use to me, so I copied the formulas into a Word document, changed all the examples of 'Sheet1' to 'Example Data', (=COUNTIF(Example [Data]Data!$H$2:H$65536, "Data Protection Awareness")) then copied it into the original spreadsheet, where it opened an 'Update Values: Data' Window.

I tried setting the source to the correct sheet, but this changed the formula to

```
=COUNTIF(Example [Data]Data!$H$2:H$65536, "Data Protection Awareness")
```

which returns a #NAME? error. I have also tried typing in the formula from scratch, with the same result.

Every time I go into the cell and get rid of

```
[Data]
```

Excel opens a window that asks me to Update Values.

How do I transfer the formulas successfully?

All data in this question is anonymised.