MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dynamically constructing a cell formula in Excel

Posted by Patrick Minderlein on March 26, 2001 7:33 AM

I need to dynamically change the formula of a cell depending on values of other cells.

I can construct the formula I want - no problem. But how do I assign that string to be the formula of a cell rather than the value of a cell.

Thanks for any suggestions.

Posted by mseyf on March 26, 2001 7:56 AM

look at the INDIRECT() function

Posted by Patrick Minderlein on March 26, 2001 8:06 AM

Thanks for the suggestion.

I tried that. It works if I need to change which sheet I am referencing, but I need to change the source tag in a DDE call. In this case, I cannot get the INDIRECT() function to work.

Posted by Aladin Akyurek on March 26, 2001 8:21 AM

Just curious: What are the values in the other cells that you want to use to build a formula? And how should the latter look like?


Posted by Patrick Minderlein on March 26, 2001 10:01 AM

I am trying to retrieve values from another app via DDE. The formula will look like :


The Datapoint is dynamic. It is actually an array like this: Timestamp[0], Timestamp[1] etc.

This does not work: APP|Document!Timestamp[A1] where A1 would be the array index (0, 1, etc).

Posted by Aladin Akyurek on March 27, 2001 7:02 AM

Patrick: I'm not sure I can help you, but still curious about the problem.

As I understand it, you seem to get data from some app into an Excel sheet. A formula on the sheet must induce the app to send in data thru DDE. The data is apparently an array to which the name Datapoint is referring. The formula that will do it, as you seem to imply, is


How does this formula behaves? Is this the formula that you want to build? From what?


I'm a bit confused about the fact that you report another formula that doesn't work.