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:
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
Excel opens a window that asks me to Update Values.
How do I transfer the formulas successfully?
All data in this question is anonymised.