Click and drag hyperlink??

[picklefactory]

Hi folks
I have a wb with an ever growing number of sheets. I also have a register on my first sheet storing details of each sheet contents. I'd like to create a hyperlink on the register page to jump straight to any sheet based on the sheet name stated in Col A. I've tried a number of things and failed spectacularly. I imagine I'm missing something stupid.... as usual. I'm trying to create a formula to take the sheet name from col A and create a hyperlink in Col G, my current failed formula is shown below.
Am I missing something stupid folks?
Thanks

Excel 2010
	A	B	C	D	E	F	G
3	Sheet4	Castings PLC	14587	19/05/2015	860455	Penta Pump Housing	SheetA3!
4	Sheet5	Castings PLC	14587	20/05/2015	844048	Belt pulley modification
5	Sheet6	Castings PLC	14587	20/05/2015	1075841	Brake cylinder bkt family
Register

Cell Formulas
Range	Formula
B3	=IF(ISERROR(INDIRECT("'"&$A3&"'!$C$3")),"",INDIRECT("'"&$A3&"'!$C$3"))
B4	=IF(ISERROR(INDIRECT("'"&$A4&"'!$C$3")),"",INDIRECT("'"&$A4&"'!$C$3"))
C3	=IF(ISERROR(INDIRECT("'"&$A3&"'!$C$4")),"",INDIRECT("'"&$A3&"'!$C$4"))
C4	=IF(ISERROR(INDIRECT("'"&$A4&"'!$C$4")),"",INDIRECT("'"&$A4&"'!$C$4"))
D3	=IF(ISERROR(INDIRECT("'"&$A3&"'!$C$10")),"",INDIRECT("'"&$A3&"'!$C$10"))
D4	=IF(ISERROR(INDIRECT("'"&$A4&"'!$C$10")),"",INDIRECT("'"&$A4&"'!$C$10"))
E3	=IF(ISERROR(INDIRECT("'"&$A3&"'!$C$5")),"",INDIRECT("'"&$A3&"'!$C$5"))
E4	=IF(ISERROR(INDIRECT("'"&$A4&"'!$C$5")),"",INDIRECT("'"&$A4&"'!$C$5"))
F3	=IF(ISERROR(INDIRECT("'"&$A3&"'!$C$6")),"",INDIRECT("'"&$A3&"'!$C$6"))
F4	=IF(ISERROR(INDIRECT("'"&$A4&"'!$C$6")),"",INDIRECT("'"&$A4&"'!$C$6"))
G3	=HYPERLINK("Sheet"&"A3"&"!")

 

[scotts]

Hi there,

Try to use the following:

=Hyperlink("#Sheet4!A2",A3)

It is: Hyperlink("reference to the sheet you need",friendly name pickled up from the table you are referring to in column A)

The hashtag makes this one work and without it doesn't !

Hopefully this helps in some way

 

[picklefactory]

Thanks Scott, it works, but only on that first row, if I click and drag that down, the cells display the correct sheet number, but the link remains the same as the first one, so they all link to the same sheet.

Unless of course I'm missing something

 

[scotts]

Actually thinking more about it try this: =HYPERLINK("#'" & A3 & "'!A1", A3) (A1 is the cell you want to jump to on the sheet)

 

[picklefactory]

You beat me to it, I just got to the same point.
Thanks scotts, that works a treat, much appreciated.

 

[scotts]

Makes a change me getting there first!!

glad i could help