| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
| How can I make a blank cell in a formula cell with a range of cell | Excel Discussion (Misc queries) | |||
| Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
| How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
| Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Linear Mode
