Here are the steps
Assuming current file is G3 Product Spec_sheet_V6.1.xls
1. in A1 on any sheet other than BOM enter
=MID(CELL("Filename",A28),FIND("[",CELL("Filename",A28),1)+1,(FIND("xls",CELL("File name",A28),1)+1)-(FIND("[",CELL("Filename",A28),1)-1))
2. In B1 enter
=HYPERLINK("["&A1&"]BOM!A1","hello")

This will take to BOM!A1 when clicked...

btw you don't have to do all this...
simply type hello in the cell you want then right-click and click on
hyperlink...
in the next dialog choose place in this docuement, choose BOM, choose A1
(chosen by default) and clik OK
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"TG" wrote:

Please excuse my ignorance, I am not sure what you mean. I went ahead and did
the replacement as you said but not it is not a valid formula. Could you
please type it out the way it should be in the cell?

"Sheeloo" wrote:

Then you need the INDIRECT function...
In the formula
=HYPERLINK("[codevalue(Main!A28)]BOM!A1","hello")

replace "[codevalue(Main!A28)]BOM!A1"
with
INDIRECT(formula)
where formula evaluates to the string you want as a result of "[codevalue(Main
!A28)]BOM!A1"
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"TG" wrote:

Thanks, this gives me the value but for some reason it still doesnt work for
what i need it to do.

I want to create a hyperlink like:
=HYPERLINK("[codevalue(Main!A28)]BOM!A1","hello") to get me to the
spreadsheet "BOM", the Main!A28 is where i used the user define function you
wrote. This still give me an error saying" cannot open specified value", all
i need is to create a link that will send me to another spreadsheet when
clicking on it.

"Sheeloo" wrote:

Define a USER DEFINED FUNCTION as

Function codeValue(c As Range) As String
codeValue = c.Value
End Function
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"TG" wrote:

hello,

I am trying to only obtain the value of a cell and not the formula.

For example:

lets say that in A1 I have :
=MID(CELL("Filename",A28),FIND("[",CELL("Filename",A28),1)+1,(FIND("xls",CELL("File name",A28),1)+1)-(FIND("[",CELL("Filename",A28),1)-1))

This gives me: G3 Product Spec_sheet_V6.1.xls


so what i want is to copy A1 to B1 but without getting the formula just the
value.
I do not want to copy and paste special..

What i need is to say something like =getvalue(A1).. and just gives me G3
Product....

Can anyone help?