0

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.

Lambert Brother
  • 1
  • 3
  • 2
    Possible duplicate of [Copy Excel worksheet and maintain relative cell reference in formulas](https://superuser.com/questions/397431/copy-excel-worksheet-and-maintain-relative-cell-reference-in-formulas) – cybernetic.nomad Apr 18 '18 at 15:22
  • This is not a duplicate of that question. I got around that problem by displaying the formulas using ctrl-` and copying into Word then copying it again into the original spreadsheet, as mentioned in my original question. My issue is with the formulas asking to Update Values. – Lambert Brother Apr 18 '18 at 15:36
  • It will only ask to update values if they reside in a different workbook. Check your data sources. Data Tab > click Edit Links – HackSlash Apr 18 '18 at 15:48
  • I tried that, opening Edit Links and deletinh and the #NAME? text changed to plain text, so I copied in the formulas from the Word document, but this had exactly the same result. – Lambert Brother Apr 18 '18 at 16:00

2 Answers2

0

I decided to try retyping the formulas from scratch, which worked onthis occasion for some reason. I think the key was to not copy and paste any of the actual formula, and instead only copy the text to be searched for and counted. This is all I did differently, and I hope reading this helps someone out who reads this.

Lambert Brother
  • 1
  • 3
0
  1. select the cell with the working formula in it
  2. Press F2 to edit the formula
  3. Select the entire formula in the formula bar (triple clicking it MIGHT work as select all or CONTROL+A)
  4. Copy the selection to clipboard (CONTROL+C, CONTROL+INSERT, right click copy)
  5. Select the destination cell on any worksheet or workbook.
  6. Paste (SHITF+INSERT, CONTROL+V, Right click paste)

This method has you copying the text of the formula and therefore it remains unchanged by excel when you paste. If you copy the cell, then excel will do its tricks for updating references when pasting to a new location.

Forward Ed
  • 1,514
  • 8
  • 16