Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Dynamic Cell Address As Formula Location
I am using the following formula to return cell address $G$16:
=IF(E110,CELL("address",OFFSET(E17,-1,E17)),"") I wish to then use the cell address as a formula location...cell address will change depending on the value in E17. Appreciate any ideas. Thanks, Sven |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Dynamic Cell Address As Formula Location
What do you mean by: "use the cell address as a formula location"?
Do you want to use that reference in another formula? Assume the result of that formula is in cell A1. Since that formula can return either a blank or a text reference you'd have to test for the blank: =IF(A1="",value_if_true,....................) If A1 does contain the text reference then you need to use the INDIRECT function to reference it: INDIRECT(A1). Since you didn't describe how you want to use that reference I don't know how to finish the above IF formula. Biff wrote in message oups.com... I am using the following formula to return cell address $G$16: =IF(E110,CELL("address",OFFSET(E17,-1,E17)),"") I wish to then use the cell address as a formula location...cell address will change depending on the value in E17. Appreciate any ideas. Thanks, Sven |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Dynamic Cell Address As Formula Location
On Mar 5, 10:24 pm, "T. Valko" wrote:
What do you mean by: "use the cell address as a formula location"? Do you want to use that reference in another formula? Assume the result of that formula is in cell A1. Since that formula can return either a blank or a text reference you'd have to test for the blank: =IF(A1="",value_if_true,....................) If A1 does contain the text reference then you need to use the INDIRECT function to reference it: INDIRECT(A1). Since you didn't describe how you want to use that reference I don't know how to finish the above IF formula. Biff wrote in message oups.com... I am using the following formula to return cell address $G$16: =IF(E110,CELL("address",OFFSET(E17,-1,E17)),"") I wish to then use the cell address as a formula location...cell address will change depending on the value in E17. Appreciate any ideas. Thanks, Sven- Hide quoted text - - Show quoted text - in the example I wish to populate cell $G$16 with something like $B5*E11 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Dynamic Cell Address As Formula Location
Oh, in that case you'll need to use VBA code. I can't help you with that.
Someone that can do that will probably "stop in" or, you could post this question in the programming forum. Biff wrote in message s.com... On Mar 5, 10:24 pm, "T. Valko" wrote: What do you mean by: "use the cell address as a formula location"? Do you want to use that reference in another formula? Assume the result of that formula is in cell A1. Since that formula can return either a blank or a text reference you'd have to test for the blank: =IF(A1="",value_if_true,....................) If A1 does contain the text reference then you need to use the INDIRECT function to reference it: INDIRECT(A1). Since you didn't describe how you want to use that reference I don't know how to finish the above IF formula. Biff wrote in message oups.com... I am using the following formula to return cell address $G$16: =IF(E110,CELL("address",OFFSET(E17,-1,E17)),"") I wish to then use the cell address as a formula location...cell address will change depending on the value in E17. Appreciate any ideas. Thanks, Sven- Hide quoted text - - Show quoted text - in the example I wish to populate cell $G$16 with something like $B5*E11 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Dynamic Cell Address As Formula Location
Hi
The following short piece of code may help to get you started on a solution Sub test() Dim c As Range ' cells(1,1) is "A1" Change both references to the same cell location as you ' currently have your formula Cells(1, 1) = "=IF(E110,CELL(""address"",OFFSET(E17,-1,E17)),"""")" Set c = Cells(1, 1) If c = "" Then Exit Sub Range(c.Text) = "=$B5*E11" End Sub You can copy the code and paste it into your Visual Basic Editor (VBE) in a Standard Module located in your file. To do this, Alt + F11 (open VBE) Ctrl + R (open Project Explorer) Select the file name on the left Insert Module Paste code in Module David McRitchie has lots of useful help on his site at http://www.mvps.org/dmcritchie/excel/install.htm http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Regards Roger Govier wrote in message s.com... On Mar 5, 10:24 pm, "T. Valko" wrote: What do you mean by: "use the cell address as a formula location"? Do you want to use that reference in another formula? Assume the result of that formula is in cell A1. Since that formula can return either a blank or a text reference you'd have to test for the blank: =IF(A1="",value_if_true,....................) If A1 does contain the text reference then you need to use the INDIRECT function to reference it: INDIRECT(A1). Since you didn't describe how you want to use that reference I don't know how to finish the above IF formula. Biff wrote in message oups.com... I am using the following formula to return cell address $G$16: =IF(E110,CELL("address",OFFSET(E17,-1,E17)),"") I wish to then use the cell address as a formula location...cell address will change depending on the value in E17. Appreciate any ideas. Thanks, Sven- Hide quoted text - - Show quoted text - in the example I wish to populate cell $G$16 with something like $B5*E11 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Formula with Dynamic Address | Excel Worksheet Functions | |||
Average Function and dynamic cell address | Excel Worksheet Functions | |||
Dynamic flexible cell address | Excel Discussion (Misc queries) | |||
Cell Address Location | Excel Worksheet Functions | |||
Using =address to provide address location for =average | Excel Worksheet Functions |