View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier Roger Govier is offline
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(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