Using Dynamic Cell Address As Formula Location

[sven.halb]

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

 

[T. Valko]

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

 

[sven.halb]

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









- Show quoted text -

in the example I wish to populate cell $G$16 with something like
$B5*E11

 

[T. Valko]

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

 

[Roger Govier]

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