Use Query, then Hyperlink

[TipFinder]

I'm trying to find a way to create a function to do the following:
1.Enter an ID# in Sheet1A1,
2. The query system would use this number to find the matching ID in Sheet 2
3. Once the matching ID in seet 2 has been found, Sheet1A1 would be hyperlink to the matching cell in Sheet 2.

Could this function be done? If so, how?

Thanks for helping.

 

[Greg Truby]

Not sure of your knowledge of VBA, but this thread (and the xref at the bottom) discusses how to do just that sort of thing.

 

[TipFinder]

My knowledge of VBA is none existance.

Where would I insert the following code in Excel? ray:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Sub foo()
'Set Target = [E5].... End If
Set WS = Nothing
End If
End Sub

Thanks for helping.

 

[Greg Truby]

Whewwy! This was a fun little feller for a Friday afternoon!

Okay - no VBA then...

Here's Sheet1:

Hyperlink Test.xls
	A	B	C	D
1	orange	orange
Sheet1

Formula in B1 is:
=HYPERLINK(RIGHT(CELL("Filename",Sheet2!A1),LEN(CELL("Filename",Sheet2!A1))-FIND("[",CELL("Filename",Sheet2!A1),1)+1)&"!"&ADDRESS(MATCH(A1,Sheet2!A:A,0),1),A1)

Here's Sheet2:

Hyperlink Test.xls
	A	B	C	D
1	Fruit
2	Apple
3	Orange
4	Grape
Sheet2

 

[TipFinder]

Hi Greg,

What's wrong with the formula in Sheet B. The formula is Sheet A works fine, but Sheet B does not; even though it uses the same formula

Sheet A

Records.xls
	F	G	H	I
1	P/ONO. w/Line#	PORecord	Unit/Price	Ship/Q'ty
2	4500041579	4500041579	45.20	180
3	4500042092	4500042092	103.00	50
4	4500042954	4500042954	103.00	50
5	4500041144	4500041144	37.79	300
6	4500041579	4500041579	45.20	120
7	4500044291	4500044291	45.20	300
8	4500041144	4500041144	61.20	496
9	4500041579LINE#20	#N/A	61.20	40
SheetA

Sheet B

Records.xls
	F	G	H	I
1	P/ONO. w/Line#	POCopy	ComcodeNo:	Unit/Price
2	4500246665	#N/A	500246	$54.45
3	4500266719	#N/A	500121	$7.00
4	4500274095	#N/A	500246	$58.85
5	4500291264	#N/A	500031	$65.20
6	4500301143	#N/A	500125	$8.50
7	4500304967	#N/A	500440	$55.50
8	4500301967	#N/A	500475	$18.00
9	4500286829	#N/A	500475	$18.00
SheetB

 

[Greg Truby]

My hunch is that you're getting some type of failure to Match(). For troubleshooting you can use Tools | Formula Auditing | Evaluate Formula to step through the formula and see which component is failing. Or you can build the formula up step-by-step to find the fault and then figure out how to fix the fault, i.e.
G2=MATCH(F2,PO!C:C,0)
G3=ADDRESS(MATCH(F3,PO!C:C,0),3)
...
and so forth.
Then twiddle with either formula components or inputs into the formula until it starts behaving itself.

HTH