If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Using Dynamic Cell Address As Formula Location
I am using the following formula to return cell address $G$16:
=IF(E11>0,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 
Ads 
#2




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(E11>0,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




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(E11>0,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




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(E11>0,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




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(E11>0,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(E11>0,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 > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Dynamic Formula with Dynamic Address  dmz_asdf  Excel Worksheet Functions  7  December 15th 06 07:13 PM 
Average Function and dynamic cell address  spartanmba  Excel Worksheet Functions  2  September 30th 06 09:24 PM 
Dynamic flexible cell address  dewsbury  Excel Discussion (Misc queries)  2  March 9th 06 08:10 PM 
Cell Address Location  drizzt04240  Excel Worksheet Functions  2  December 1st 05 03:49 PM 
Using =address to provide address location for =average  ahills  Excel Worksheet Functions  2  November 30th 04 03:10 AM 