A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Using Dynamic Cell Address As Formula Location



 
 
Thread Tools Display Modes
  #1  
Old March 6th 07, 03:54 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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  
Old March 6th 07, 04:24 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default 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  
Old March 6th 07, 04:40 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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  
Old March 6th 07, 04:52 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default 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  
Old March 6th 07, 07:37 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier
external usenet poster
 
Posts: 2,886
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic Formula with Dynamic Address dmz_asdf Excel Worksheet Functions 7 December 15th 06 08: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 09:10 PM
Cell Address Location drizzt04240 Excel Worksheet Functions 2 December 1st 05 04:49 PM
Using =address to provide address location for =average ahills Excel Worksheet Functions 2 November 30th 04 04:10 AM


All times are GMT +1. The time now is 08:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.