What Causes #HREF! Errors in Excel 2007?

[WWGD]

TL;DR

Trying to fix #HREF! problems that come about when pasting formular-based columns in Excel 2007.

Hi all,
I am having #HREF! issues in Excel 2007 when copying formula-based data from one sheet to another. The suggested solutions online are not working.

I understand what the issue is: when copying from one sheet to another, Excel is trying to use data from the previous sheet, sheet_j, say, , which is not available to it, to obtain data for sheet_k. In this case, in sheet_j, the entries are averages of values in columns in sheet_j. When pasting, Excel apparently has no access to these values, so the formula, say, Average(A1:A5) makes no sense without knowing what A1:A5 are.

Still, the suggested solutions I found online are not working.Any other ideas?

 

[pbuk]

If you want to refer to data in another sheet, enter the formula as AVERAGE(sheet_j!A1:A5), or just select the range in the other sheet with the mouse.

 

[Asymptotic]

I've used LibreOffice so long I don't remember exactly, but one approach would be to copy the desired range, and use paste|special|link.

 

[WWGD]

I've used LibreOffice so long I don't remember exactly, but one approach would be to copy the desired range, and use paste|special|link.

Thanks, but when I do copy+paste, it seems to use the formula from the previous page.

 

[WWGD]

Thanks all. I tried something else that worked:
Choose the destination cell in sheet j. Set the input value to = , go to initial sheet k . Select the value, hit enter. That's it. You can then extend to the rest of the column.

 

[WWGD]

Thanks, but when I do copy+paste, it seems to use the formula from the previous page.

Never mind, this works too, I had misread, my bad.