How to generate formula from text?

[elkinm]

Basically cell "A1" contains B. Cell A2 contains 4.
Now I put the exact code into A3: ="A"&A1&A2
Now A3 holds the text: AB4.
What I want is to generate the formula that will link to cell AB4 and not the text.
Also making A3: ="=A"&A1&A2 changes the text to =AB4 but does not give me the formula.

The question is how can I create a formula form text without using macros?
And if macros are truly needed, then how can I make a simple set of macros to implement to make any cell generated from other cells on the fly with macros.
It can be one time but the cells or ranges must be selectable like in a function and generate the appropriate formula.

Thanks

 

[mayest]

You just want to pull the value from cell AB4, right? If so, then you are halfway there. Just use the Indirect function on the string that you constructed:

=INDIRECT("A"&A1&A2)

Any changes that you make in A1 and A2 will automatically change the cell from which you pull the data.

 

[elkinm]

Originally posted by: mayest
You just want to pull the value from cell AB4, right? If so, then you are halfway there. Just use the Indirect function on the string that you constructed:

=INDIRECT("A"&A1&A2)

Any changes that you make in A1 and A2 will automatically change the cell from which you pull the data.

Thanks, I am not sure how indirect works exactly, but I still don't think it will do what I need.
I do need to generate the formula. My example is simple, but my final goal is to generate a link to an external program generated based on entries in excel cells. Can indirect do this?

I will try it out tomorrow to see what happens.

Thanks again.

 

[mayest]

I'm not sure I understand what you ultimately want to do. Indirect simply uses a string as a cell address and returns the result. Say if I have a formula in A1 that returns 20 as the value. If I now go somewhere else and enter =INDIRECT("A1") it will convert the string "A1" to a cell address and then pull in the value from cell A1, which is 20. Obviously, most real uses are more complicated, but that's the idea.

Give me an example of the formula that you are trying to create.

 

[elkinm]

The goal is to create a DDE link to an external program.

The format is =Program1|Feed1|Data1

The link is the same as it I copy and paste link into Excel. I can change the Feed name and Data name to get different values. Instead of pasting and updating all the links one at a time I want to generate the links from excel cells with Feed and Date name which can be easily filled and copied.

I know this can be done in VBA with writing to the formula field, but I don't know how to do it in a function format so I can chose which cells should have formulas on the fly.

Thanks

 

[mayest]

Ok, Indirect won't work with formulas. I think you are going to have to create a VBA sub to do this. It should be pretty straightforward. Something along these lines:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A3").Formula = "=A1+A2"
End Sub

Put that into the Sheet module (e.g. it will be named Sheet1 (Sheet 1) if you haven't renamed the sheet) for whichever sheet you want to put the cells that you will change. In the above, it just puts the formula =A1 + A2 into cell A3.

Obviously, you will need to build up the formula string from the changing cells and the rest of the structure of the link. This will change the formula everytime you change anything on that worksheet.

Let me know if you need more explicit instructions. If so, give me some exact examples of the inputs and outputs.

 

[elkinm]

Originally posted by: mayest
Ok, Indirect won't work with formulas. I think you are going to have to create a VBA sub to do this. It should be pretty straightforward. Something along these lines:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A3").Formula = "=A1+A2"
End Sub

Put that into the Sheet module (e.g. it will be named Sheet1 (Sheet 1) if you haven't renamed the sheet) for whichever sheet you want to put the cells that you will change. In the above, it just puts the formula =A1 + A2 into cell A3.

Obviously, you will need to build up the formula string from the changing cells and the rest of the structure of the link. This will change the formula every time you change anything on that worksheet.

Let me know if you need more explicit instructions. If so, give me some exact examples of the inputs and outputs.

Yes this will do what I am looking for. But it seems that you are fixing the cells you combine with A1 + A2 and put it into A3. This is good if I have a permanent spreadsheet.
I would like more control. Something like =MakeFormula(A1,A2,A3) where Makeformula is a function. I am assuming I cannot put this formula in A3 as it will override the original entry.
But functions cannot modify cells and I don't think I can call a sub like a function from Excel, Can I?

Thanks again.

 

[mayest]

You are correct that functions cannot modify a cell, but a Sub can call a function. So, what I would do is use the Worksheet_Change sub (so that you know something has changed) and then use that to call a function to build the strings that you need.

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest as String, Func as String
Dest = GetDest()
Func = MakeFormula()
Range(Dest).Formula = Func
End Sub

Function GetDest() as String
'Figures out the destination cell
<Put your logic here>
End Function

Function MakeFormula() as String
'Builds the string for the formula to be placed on the worksheet
<Put your logic here>
End Function

You will have to figure out the logic and any required arguments for those two functions. Also, you can't call MakeFormula from the worksheet unless you just want to show a string in the cell (which you don't).

At a minimum, I think that you are going to need fixed cells that contain the information needed to build the formula. Those would be arguments to the MakeFormula function. If you don't want to use fixed cells, then you could build a simple UserForm that prompts you for the information needed to build the formula and the location to put it. You would also need a Sub that would load the form, get the data that you entered, and build and place the formula. The downside to this would be that you would have to explicitly run this sub after you change the cell addresses. You could attach the Sub to a button on the worksheet, though.

Another way to go would be to use a helper cell on the worksheet where you concatenate a string for your function. Build a Sub that reads that string, and then puts it as a formula into the destination cell.

I hope this helps. I'm still willing to help some more if you need it.