Adding a Hyperlink to a IF Formula ( with a twist )

Good Morning,

I have done my best to search google for a answer but have come up with nothing that I can understand or use. Sorry I am pretty new...

Utilizing a userform, users enter a series of entries into sheet 2 to of our log, it is a long string of data and not very user friendly. So I am using sheet 1 as a "cover page" to transfer some of the data over to allow for users to review entries using this formula (F1), I am using this formula (F2) to add a unique number to every entry so we can provide this number to other staff, customers etc. as a reference.

(F1)=IF(Sheet2!B3 = ISBLANK(TRUE),"",Sheet2!B3)
(F2)=IF(B4="","",CONCATENATE("CCR",LEFT("0000",4-LEN(ROW(A2)-1)),ROW(A2)-1))

What I require is for (F2) to contain a hyperlink that brings it back to the correct row on sheet 2 for users to review all the data if required, this hyperlink will also be useful as I have to create another sheet for "follow up" if required.

If I am missing any information that would help you to help me, please let me know.

Many Thanks !

Hello,

Here is my workbook. I am unsure if your formula does the same thing as mine took me awhile just to get mine right..

Using the work book. clicking on sheet 1 A4 should hyperlink you to row 3 on sheet 2 and so on.

Originally Posted by martindwilson

there is no ccr on sheet 2

Correct, only contained on sheet 1, I need it to point to the row (as I stated above) I am still very neew with excel, please make any changes you see fit to get this to work, I am very open to new idea's

Originally Posted by martindwilson

sorry but what info is on the sheet you are hyperlinking to ?could you post an example work book?
but doesnt =if(b4="","",="CCR"&TEXT(ROWS($A$1:A1),"0000") give the same result as F2?

Took some time to check out the formula you provided, I received errors. I revamped it to this: =if(b4="","","CCR"&TEXT(ROWS($A$1:A1),"0000")) and it now does the same thing, may I ask is one better than the other, or is it just two ways to get the same result?

Originally Posted by martindwilson

maybe in sheet1!a4
=IF(B4="","",HYPERLINK("#"&"sheet2!b"&ROWS($A$1:A3),"CCR"&TEXT(ROWS($A$1:A1),"0000")))

Works like a charm, I added to your reputation.

Many Thanks !!