Join Table Entry to Pivotable

[therock003]

I have made an excel workbook that is intended to be used as a lightweight CRM solution. I'll be brief with my setup and what i want

I got one tab functioning as an inventory table, where each row describes the inventory item's properties and then i got a second tab for a phonebook for suppliers.

The Second tab has a pretty basic structury with 3 columns. Name, Phone Type and Phone Number.

e.g

John The Supplier, Work Number, 555-555
Supplier Eric, Home Number, 555-556
Supplier Eric, Mobile Phone, 656-555

And i have consrquently created a pivot table so each name expands and collapses to reveal multiple numbers (in case ive stored work and mobile etc)

My request is that when i'm on the inventory table on the first tab, i need from there to be able to grab-redirect dont know to the number cell of the supplier.

So for example lets say inventory table entry is

pencil, red, available, Supplier Eric

I need to reach Supplier Erics contact information from the Pivotable on my Second Excel Tab in order to make a phone call.

I know you guys are going to say everything would be easier if you'd just use a database management system, but i mean cmon its a lightweight file, of 200 entries at best

 

[GraH]

Have you simply tried to "point" at the pivot after typing "=". Then change the hardcoded supplier name, with the cell reference where you have the name filled out/selected.

 

[therock003]

If only it were that easy. First off Pivotable changes from time to time when new suppliers are added or old ones removed. So if for example i use a reference like =A1 where A1 on the tab with pivotable is John the Supplier, maybe tomorrow Jim the Supplier is added, so the reference =A1 on the first table will give Jim the Supplier which is completely false

More over, even if i point to that cell and get the name, that doesnt do my lot of good. How will i click the plus icon to expand to the numbers from there..

 

[GraH]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Supplier	Phone	Type	something else			Supplier	Type	Phone
2	John	555-555	work	100			John	work	555-555
3	Eric	555-556	Home	150			Eric	Home	555-556
4	Eric	656-555	Mobile	780			Eric	Mobile	656-555
5
6
7
8
9
10									Home	Work	Mobile
11							Select a Supplier	Eric	555-556	#N/A	656-555
12									555-556		656-555
Sheet1

Cell Formulas
Range	Formula
I11:K11	I11	=INDEX($I$2:$I$4,MATCH($H11&I$10,$G$2:$G$4&$H$2:$H$4,0))
I12:K12	I12	=IFERROR(INDEX($I$2:$I$4,AGGREGATE(15,6,(ROW($G$2:$G$4)-1)/(($G$2:$G$4=$H11)*($H$2:$H$4=I$10)),1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[therock003]

Guys i'm sorry maybe i didnt phrase it right. So once again. I have one sheet with suppliers and a second one with their contact details as such.
I want to click on a supplier and "jump" to the corresponding entry on the contact details pivot table. Heres some pics of my workbook

 

[GraH]

Hi, thanks for the additional information.
Why doing this via the pivot? Adding a column with hyperlink formula might be all you need.

Book1
	A	B	C	D	E	F	G	H
1	Supplier	This	That	Location	Code	Fee	Ratio	Link PhoneBook
2	John							GoTo John
3	Eric							GoTo Eric
Supplier

Cell Formulas
Range	Formula
H2:H3	H2	=HYPERLINK("#PhoneBook!$A$"&MATCH([@Supplier],tPhonebook[Supplier],0)+1,"GoTo "&[@Supplier])

Where the link "GoTo Eric" lands here