| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
accessing an address stored in another cell
Is it possible to access an address stored in another cell? For example:
G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#2
|
|||
|
|||
|
look at the indirect function
-- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#3
|
|||
|
|||
|
Thanks, but Indirect wants a textual cell address and that's not what resides
in my target cell (A100). "paul" wrote: look at the indirect function -- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#4
|
|||
|
|||
|
Use the udf:
The Code for GetFormula Function GetFormula(Cell as Range) as String GetFormula = Cell.Formula End Function Source: http://www.mvps.org/dmcritchie/excel/formula.htm Mangesh "SteveR" wrote in message ... Thanks, but Indirect wants a textual cell address and that's not what resides in my target cell (A100). "paul" wrote: look at the indirect function -- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#5
|
|||
|
|||
|
Hi!
In A100 enter G100, don't use the plus sign. You don't need to use plus signs like that in Excel, anyhow. So, if you have in cells: G100 = test A100 = G100 =INDIRECT(A100) will return test. Biff "SteveR" wrote in message ... Thanks, but Indirect wants a textual cell address and that's not what resides in my target cell (A100). "paul" wrote: look at the indirect function -- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#6
|
|||
|
|||
|
Thank you very much! uh -- one little problem. This is the first time I've
used the VB Editor. I pasted in Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function and still don't get anything but an error, when I do something like =GetFormula(B1534) "Mangesh Yadav" wrote: Use the udf: The Code for GetFormula Function GetFormula(Cell as Range) as String GetFormula = Cell.Formula End Function Source: http://www.mvps.org/dmcritchie/excel/formula.htm Mangesh "SteveR" wrote in message ... Thanks, but Indirect wants a textual cell address and that's not what resides in my target cell (A100). "paul" wrote: look at the indirect function -- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#7
|
|||
|
|||
|
In C100, enter:
=1*MID((MID(GetFormula(A100),2,255)),MATCH(FALSE,I SERROR(1*MID((MID(GetFormu la(A100),2,255)),ROW(1:10),1)),0),255) confrim with shift control enter Use the UDF to GetFormula as shown in my earlier post. The formula above is given in http://www.emailoffice.com/excel/arrays-bobumlas.html Mangesh G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). "Mangesh Yadav" wrote in message ... Use the udf: The Code for GetFormula Function GetFormula(Cell as Range) as String GetFormula = Cell.Formula End Function Source: http://www.mvps.org/dmcritchie/excel/formula.htm Mangesh "SteveR" wrote in message ... Thanks, but Indirect wants a textual cell address and that's not what resides in my target cell (A100). "paul" wrote: look at the indirect function -- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#8
|
|||
|
|||
|
Okay, thanks again for the great help. For some reason, both formulas give
me an error. Maybe it has something to do with my version being 97. "Mangesh Yadav" wrote: In C100, enter: =1*MID((MID(GetFormula(A100),2,255)),MATCH(FALSE,I SERROR(1*MID((MID(GetFormu la(A100),2,255)),ROW(1:10),1)),0),255) confrim with shift control enter Use the UDF to GetFormula as shown in my earlier post. The formula above is given in http://www.emailoffice.com/excel/arrays-bobumlas.html Mangesh G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). "Mangesh Yadav" wrote in message ... Use the udf: The Code for GetFormula Function GetFormula(Cell as Range) as String GetFormula = Cell.Formula End Function Source: http://www.mvps.org/dmcritchie/excel/formula.htm Mangesh "SteveR" wrote in message ... Thanks, but Indirect wants a textual cell address and that's not what resides in my target cell (A100). "paul" wrote: look at the indirect function -- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#9
|
|||
|
|||
|
In the VBE, go to Insert Module
Then paste the code in this module. Mangesh "SteveR" wrote in message ... Thank you very much! uh -- one little problem. This is the first time I've used the VB Editor. I pasted in Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function and still don't get anything but an error, when I do something like =GetFormula(B1534) "Mangesh Yadav" wrote: Use the udf: The Code for GetFormula Function GetFormula(Cell as Range) as String GetFormula = Cell.Formula End Function Source: http://www.mvps.org/dmcritchie/excel/formula.htm Mangesh "SteveR" wrote in message ... Thanks, but Indirect wants a textual cell address and that's not what resides in my target cell (A100). "paul" wrote: look at the indirect function -- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#10
|
|||
|
|||
|
You have probably pasted the code in the wrong module. As I said, put in in
a standard module by going to Insert Module. and then pasting the code. Mangesh "SteveR" wrote in message ... Okay, thanks again for the great help. For some reason, both formulas give me an error. Maybe it has something to do with my version being 97. "Mangesh Yadav" wrote: In C100, enter: =1*MID((MID(GetFormula(A100),2,255)),MATCH(FALSE,I SERROR(1*MID((MID(GetFormu la(A100),2,255)),ROW(1:10),1)),0),255) confrim with shift control enter Use the UDF to GetFormula as shown in my earlier post. The formula above is given in http://www.emailoffice.com/excel/arrays-bobumlas.html Mangesh G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). "Mangesh Yadav" wrote in message ... Use the udf: The Code for GetFormula Function GetFormula(Cell as Range) as String GetFormula = Cell.Formula End Function Source: http://www.mvps.org/dmcritchie/excel/formula.htm Mangesh "SteveR" wrote in message ... Thanks, but Indirect wants a textual cell address and that's not what resides in my target cell (A100). "paul" wrote: look at the indirect function -- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#11
|
|||
|
|||
|
Got it! Thanks again for the top notch comments.
"Mangesh Yadav" wrote: You have probably pasted the code in the wrong module. As I said, put in in a standard module by going to Insert Module. and then pasting the code. Mangesh "SteveR" wrote in message ... Okay, thanks again for the great help. For some reason, both formulas give me an error. Maybe it has something to do with my version being 97. "Mangesh Yadav" wrote: In C100, enter: =1*MID((MID(GetFormula(A100),2,255)),MATCH(FALSE,I SERROR(1*MID((MID(GetFormu la(A100),2,255)),ROW(1:10),1)),0),255) confrim with shift control enter Use the UDF to GetFormula as shown in my earlier post. The formula above is given in http://www.emailoffice.com/excel/arrays-bobumlas.html Mangesh G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). "Mangesh Yadav" wrote in message ... Use the udf: The Code for GetFormula Function GetFormula(Cell as Range) as String GetFormula = Cell.Formula End Function Source: http://www.mvps.org/dmcritchie/excel/formula.htm Mangesh "SteveR" wrote in message ... Thanks, but Indirect wants a textual cell address and that's not what resides in my target cell (A100). "paul" wrote: look at the indirect function -- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
|
#12
|
|||
|
|||
|
Glad its working. Thanks for the feedback.
Mangesh "SteveR" wrote in message ... Got it! Thanks again for the top notch comments. "Mangesh Yadav" wrote: You have probably pasted the code in the wrong module. As I said, put in in a standard module by going to Insert Module. and then pasting the code. Mangesh "SteveR" wrote in message ... Okay, thanks again for the great help. For some reason, both formulas give me an error. Maybe it has something to do with my version being 97. "Mangesh Yadav" wrote: In C100, enter: =1*MID((MID(GetFormula(A100),2,255)),MATCH(FALSE,I SERROR(1*MID((MID(GetFormu la(A100),2,255)),ROW(1:10),1)),0),255) confrim with shift control enter Use the UDF to GetFormula as shown in my earlier post. The formula above is given in http://www.emailoffice.com/excel/arrays-bobumlas.html Mangesh G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). "Mangesh Yadav" wrote in message ... Use the udf: The Code for GetFormula Function GetFormula(Cell as Range) as String GetFormula = Cell.Formula End Function Source: http://www.mvps.org/dmcritchie/excel/formula.htm Mangesh "SteveR" wrote in message ... Thanks, but Indirect wants a textual cell address and that's not what resides in my target cell (A100). "paul" wrote: look at the indirect function -- paul remove nospam for email addy! "SteveR" wrote: Is it possible to access an address stored in another cell? For example: G100 = "test" A100 = +G100 Now, can I get any information about the address stored in A100, which is G100? In C100, I would like to do something like =ROW(address stored in A100). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell color based upon cell value | Excel Discussion (Misc queries) | |||
| cell color index comparison | New Users to Excel | |||
| ADDRESS function - dynamic input cell | Excel Discussion (Misc queries) | |||
| How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
| How do I dynamically retrieve the cell address of the last cell t. | Excel Discussion (Misc queries) | |||
Linear Mode
