Hi, I have a formula in A1, for incident the formula is "=B1+C2+D3"

How can i put this formula in text to a given cell, example, in E1, it shows a text "=B1+C2+D3"

Put a single quotemark, usually found next to the Enter key,

in front of your formula:'=B1+C2+D3

MIKE

Why do you want to do this? If want to see (or print) the formulas, then press Ctrl and

`(the grave accent, under the tilde ~)All of the formulas will be shown. If printed, it will print the formulas.

You can toggle formulas on and off with Ctrl -

`

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks mmcconaghy and DerbyDad03 for your advice.

There is a simple formula in a given cell. It is "=B1+B2+B3"

But the reference in the formula is dynamic, for incident, B1 could be B4 and there might be additional reference, like B4+B5+B6+B9.

What i want to do is to setup a formula to calculate the next row of given formula.

If the formula given is B1+B2+B3, then i want to calculate C1+C2+C3.

Since the reference formula is dynamic, it's very difficult to set a formula to get the result.I was thinking using substitute function to replace "B" to "C" in the given formula but this function requires a text feild.

And all functions are intended to calculate the result of a given formula not the formula itself.

This is the key issue and that is why i want to extract the text information of the formula. This should be done automatically, so it better not require user to input something.Thanks,

Well, you have totally confused me... I have no clue what this means:

And all functions are intended to calculate the result of a given formula not the formula itself.You also said that =B1+B2+B3 could become B4+B5+B6+B9, but if you have B1+B2+B3, then you want to calculate C1+C2+C3.

I, for one, have no idea what you are trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

To make it simple, given formula could be =B1+B2+B3 , or =B4+B5+B6+B9,

The calculation i want to do is simply substitute "B" to "C",

If given cell's fomula is =B1+B2+B3, then calculate =C1+C2+C3

If given cell's fomula is =B4+B5+B6+B9, then calculate =C4+C5+C6+C9

I'm sure there's more to this "simple" task than you are telling us. Even in it's "simplest" form as shown above, it's pretty complicated. First, there's no way for Excel to calculate anything other than the formula in the cell. If the formula says =B1, Excel is not going to return the value in C1.

You could use =OFFSET(B1,0,1) to return C1, or for your first example above...

=OFFSET(B1,0,1)+OFFSET(B2,0,1)+OFFSET(B3,0,1)

If you used Mike's suggestion of a single quote to force the formula to be text...

'=B1+B2+B3Then you could use:

=OFFSET(INDIRECT(MID(A1,2,2)),0,1)+

OFFSET(INDIRECT(MID(A1,5,2)),0,1)+

OFFSET(INDIRECT(MID(A1,8,2)),0,1)Unfortunately, neither of these are going to be dynamic. In both cases you'd need to know what the original formula was and then write a new formula based on that.

There's a slim chance that VBA could be used to break the original formula down into the individual referenced cells, but either the formulas would have to be fairly simple or the VBA would have to be so complicated as to be prohibitive.

In either case, we would need to know a lot more detail about the formulas in question before anything could be written.

I could write some VBA that would work on the specific examples that you gave us, but it would only work for formulas that are summing 3 or 4 cells. Anything other than that and it would fail.

This is one of those cases where trying to keep it simple doesn't work. Perhaps if you told us

exactlywhat you are trying to do, a solution could be found.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History