Issue with cut/paste making formula #REF

[mihael546]

Hi All,
I would appreciate a little bit of help if possible.

I am currently using excel spredsheet for typing data into it and thereafter I have to move this data to another side of the same sheet. (From one cell to another cell)
Once the data is moved from a cell to the another cell I am using formula to create a hyperlink and add the cell data to end of the same hyperlink, for this I am using below formula;
=IF(ISBLANK(C3),HYPERLINK("#"&CELL("address"),""), HYPERLINK("I HAVE TO REMOVE LINK DUE TO CONFIDENTIALITY" & C3,"Open"))
Example;
Formula located in C5
Type data into C4 (e.g. "123456")
Cut/paste data from C4 to C3
Once this is done my formula gets #REF
I completely undertand why is that but unfortunately not sucided to find a solution to it.

I have tried to use data duplication to be automatically deleted but unfortunately unnecesary data will be deleted too. (This was an ide for copy/paste as this is only way that keeps formula)
I found something to try to use with function =INDIRECT but not sucided to include it in my formula.
Unfortunately CUT/PASTE is very important and my work cannot work without that. (I use shortcuts CTRL + X and CRTL+V)

Help would be much appreciated!

 

[Domenic]

Try amending your formula as follows...

=IF(ISBLANK(INDEX(C:C,3)),HYPERLINK("#"&CELL("address"),""), HYPERLINK("I HAVE TO REMOVE LINK DUE TO CONFIDENTIALITY" & INDEX(C:C,3),"Open"))

Hope this helps!

 

[mihael546]

Try amending your formula as follows...

=IF(ISBLANK(INDEX(C:C,3)),HYPERLINK("#"&CELL("address"),""), HYPERLINK("I HAVE TO REMOVE LINK DUE TO CONFIDENTIALITY" & INDEX(C:C,3),"Open"))

Hope this helps!

Hi Domenic, thank you for your help.

Formula doesn't throw an error but unfortunately it doesn't creates a hyperlink either

Maybe I should mention on a seperate note that I have multiple of those formulas in same lines and rows.

Any other ideas?

 

[Domenic]

Here's before copy/paste...

Here's after copy/paste...

And when I click on Open, it follows the link to Google's web page.

Does this help?

 

[mihael546]

Hi Domenic,

I can confirm that everything works now. I would like to thank you for your time and for looking into my query. You are a star!

I have tried to implement formula into my existing sheet but it didn't work. Then once you have wrote me above answer, I decided to try it my self on a new blank sheet and it worked fine. On the end, I have created a new sheet and transferred all data to a new sheet. I don't know why it doesn't work on my old sheet but it is a a one less problem to worry about now.

Thank you again!

 

[Domenic]

Maybe the specified link location was invalid?

In any case, I'm glad you were able to work around the issue.

Cheers!