Linking between 2 sheets using "Primary Key"

[keanoppy]

Dear Gurus,

I'm wondering, whether I can make a cell that is linked to another cell in different sheet.
Yes, I do know that I can do it 1 by 1 manually.

Heres my example:
sheet1 Column : PID | Name
sheet2 Column : PID | Status

on both sheets, no duplications of PID

let say sheet1 has 700 rows of data
while sheet2 has 400 rows of data

How to make when I click sheet1's row 65 PID, it will go to the cell of anywhere in sheet2 with the same PID automatically?

Any ideas? Other than linking it one-by-one

Thank you soo much!

 

[maninweb]

Hi keanoppy...

I attached an XLS file with a sample how to dynamically create a hyperlink to the cell on the second sheet. Surely one solution among others.

In column D of the first sheet, a formula searches the address of the cell in the second sheet by comparing the PID's. In column E, the filename and sheet name of the second sheet are retrieved by using the formula Cell(). And in column E the two results are putted together for creating the hyperlink.

Hope this helps.

Regards

 

[Ken Puls]

Kenaoppy,

PID? Are you building something to do with Land/Property inventory?

 

[keanoppy]

Dear Maniweb,

Thank you soo much for your solution.
Can you help me in understanding the formula on the ""Filename + Address" column?
It works fine if I were to delete a row from PID_Status (i've didn't use ref+1 for PID in PID_Status)
When I tried to add a row on both sheets, with the PID 0021, it somehow cannot link >.<

 

[keanoppy]

Dear Ken,

More towards company talents database >.<
One sheet to hold all of the employees data, another sheet to hold talents.
Both may have the same PID you see, but out of 120 employees, maybe 4 are talents.
I'm hoping to map automatically in the employees sheet with talents sheet.

1st sheet to hold all of the employee data, 2nd sheet to hold development plan.
So if the employee is a talent, can click on the link to the next sheet to view the development plan automatically.
That's the whole idea though >.<

Maniweb is really helping out by his/her solution earlier. I'm trying to familiarize myself with his "Filename + Adress" formula >.<

 

[maninweb]

Hi keanoppy...

Ok, I will try to explain the formulas.

The goal was to hyperlink to a specific cell on another sheet. If we do this manually, we have, for example, to use [PID.xls]PID_Status!$A$24 as first argument in the Hyperlink() function. This argument includes the filename, the target sheet name and the cell address.

So, what I have done in the sample workbook, is to retrieve this information dynamically by using Excel functions. In case you change the filename or the sheet name of the target sheet, you will not have to replace static texts in your formulas.

For retrieving the filename and sheet name, we can use the Excel Cell() function. This function returns some infos about cells and the workbook by using keywords. One of these keywords is "Filename". So, if we write in a cell the formula CELL("Filename";PID_Status!$A$1), then we will get E:\Temp\[PID.xls]PID_Status as result. However, this result also includes the path to the workbook. Please note, that CELL() can only return a value, if the workbook was already saved somewhere. By the way: the keywords are normally localized to the Excel language used. The english keywords always work, the localized only on Excel with the same language.

So, for getting [PID.xls]PID_Status from E:\Temp\[PID.xls]PID_Status, we need to split the string from "[". This can be done by using the functions Find(), Right() and Len(). Let's imagine, the result of Cell() is contained in the cell B1. Then we can use =RIGHT(B1;LEN(B1)-FIND("[";B1)+1). Find() searches the position of the "[" character and retuns a value. We then take the right part of the string in B1 from the found position of "[" and for including the "[", we increase the value by one. The result will be [PID.xls]PID_Status. The first part for the hyperlink is now found.

You said that the PID's are unique identifiers, this allows us to search the relative position of an PID in a matrix by using the Match() function. Example: =MATCH(A2;PID_Status!$A$5:$A$24;0) returns 20, meaning the 20th entry in PID_Status!$A$5:$A$24. As in my sample the PID States start in row 5, I added the relative position of the head row by using ROW(PID_Status!$A$4). I did the same for the columns by not entering the column number directly but using the formula COLUMN(PID_Status!$A$4).

Now we have, depending on the searched PID, found the cell row and column numbers in the target sheet. We need then to get the address of this cell, what can be done by using the Address() function from Excel. Examples: Adress(1;1) returns $A$1 and Adress(8;10) returns $J$8.

And after that, we just need to put the results together for building the needed hyperlink string: =HYPERLINK(The_string_got_by_splitting_the_fileinfo & "!" & The_address_found;"Friendly text"). The last step can optionally be putting the formulas together. Of course, the solution may not be the only one, perhaps there is a quicker solution.

Hope this helps

Regards

 

[Ken Puls]

Dear Ken,

More towards company talents database >.<

Ah, cool. I work with land, and to us PID is a Property ID number. Every property in our province has a unique identification. In fact, when a property is subdivided, the old PID dies, and two new ones are created. I was just curious if it was the same. It's funny how so many acronyms can mean different things.

Glad to hear your getting good help here!