HYPERLINK function errors

Question

Hello again!

Still messing with hyperlinks. I have hand entered ones on sheet A that are fully functional. Some are www., some http:// and some are https:// but all are functional on sheet A.

I want to copy them to sheet B but can not. Using the INDEX function to retrieve them results is what appears to be a text-only value on sheet B with no active functionality.

I have tried using the HYPERLINK function to create a working link on sheet B but get #VALUE! errors no matter what I do.

What is the correct to copy these URL's from sheet to sheet other than a manual copy/paste which in this case is impractical due to the dynamic nature of the data that appears on sheet B.

=IF(AND(Transaction_Row<>"",INDEX('December Transactions'!URL,Transaction_Row-1,1)<>"",LEFT(INDEX('December Transactions'!URL,Transaction_Row-1,1),4)="http"),INDEX('December Transactions'!URL,Transaction_Row-1,1),"")

The above line of code results in a #VALUE! error. The receiving cell is General and the URL in the source sheet, 'December Transactions', is valid and functional in the cell named URL.

Any ideas?

Thanks, TheOldPuterMan AKA John

Answer 1

Hello

I am V. Arya, Independent Advisor, to work with you on this issue. May I request you to post the file with dummy data (no production / real data) to Onedrive or any other cloud sharing site and post the link here? This will help me to work out the right solution for you.

Answer 2

Yes, I tried that and experimented with various methods to get the URL's from sheet"A" to "B". It does work but when I added selection criteria to determine which data should appear on sheet "B" it kept giving me good old #VALUE!.

The following formula is what finally worked for me. The #VALUE! error had something to do with the data in The Transaction_Row array ($E$7:$E$606). when there was a numerical value in a cell all was well but as soon as that cell was blank the #VALUE! error appeared no matter what I did to test it.

The following is the formula that finally worked for me. It handles HTTP://, HTTPS:// and WWW. prefixes and created valid links.

=IF(Transaction_Row="","",IF(INDEX('December Transactions'!URL,@Transaction_Row-1,1)="","",IF(LEFT(INDEX('December Transactions'!URL,@Transaction_Row-1,1),4)="WWW.",HYPERLINK("http://"&MID(INDEX('December Transactions'!URL,@Transaction_Row-1,1),5,256),"Link to "&INDEX('December Transactions'!Provider,Transaction_Row-1,1)),HYPERLINK(INDEX('December Transactions'!URL,@Transaction_Row-1,1),"Link to "&INDEX('December Transactions'!Provider,Transaction_Row-1,1)))))

Thanks for your suggestion, TheOldPuterMan AKA John

Answer 3

Thanks but the results on the object sheet "B" are variable based on many factors and change daily or more often. Copy/Paste, while it works would be tedious and, knowing me, somewhat error-prone.

TheOldPuterMan

Answer 4

try working with  the hyperlink function, ie

=HYPERLINK(Sheet1!A1)

When I simply copied the value in sheet1 to sheet2, ie on sheet 2  =sheet1!A1

it came through as text only.

When I added the hyperlink() function it was a clickable link.

Answer 5

Hello

Thanks for reaching out.
And I'll be happy to help you out today.

Based on your description, you want to copy a certain cell formula and paste it to another worksheet?

How about copy and pasting as keep source formatting.
Also try to copy the formula in the formula bar and paste it to the other worksheet.

Update me if this solve the problem.

Have a nice day !

-Regards,

Marj :)