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

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

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

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

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