|
get value of cell only
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? |
get value of cell only
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? |
get value of cell only
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? |
get value of cell only
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? |
get value of cell only
On Thu, 9 Apr 2009 14:32:04 -0700, 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? How about just B1: =A1 --ron |
get value of cell only
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? |
get value of cell only
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? |
| All times are GMT +1. The time now is 12:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com