Dynamic formula reference?

[Jubelnar]

Hi ALL
I have a "simple" but yet not soluted problem. I really hope you can help me out.

Problem: I have LOTS of formulas, which when corrected needed to be corrected for just as many.

Is it possible to LINK a formula only, so I can have a "master" formula, when corrected will correct linked formulas in all other cells.

Let me know if question is not clear...

Thanks so much in advance for your help and support.

Regards Martin (Denmark)

[Carim]

Hi Martin,

Your question would need some concrete example ...

However, there are, in generl terms several solutions ...

1. Using Ctrl H will allow you in one to find and replace portions of all your formulae in one go ...

2. Depending on the formulae and the types of relationships,
Offset() and Indirect() functions could be of assistance ...

HTH
Carim

[VBA Noob]

Hi,

Without an example of what your problem it's hard to guess.

Try this link which might help

http://www.contextures.com/xlNames01.html

Come back if it's not what your after

VBA Noob

[Jubelnar]

Hi again...
Thanks for super fast reply..

Example:
Cell F2 has formula =C2+D2

Formula is updated in G1


A B Formula SUPER FORMULA
3 1 A1+B1 =A"X"+B"X"
2 2 A2+B2
1 3 A3+B3

Is super formula changed, formulas will be changed accordingly. Can this be achieved?

Regards
Martin

[VBA Noob]

Hi,

Your example isn't great.

Think you might be talking about Absolute And Relative References

Try this link.

If you want to move your formula from F2 to G1 you can highlight the formula in the formula bar and then select it and cut it. Hit the x to get out of the formula bar and paste into G1

http://www.cpearson.com/excel/relative.htm

VBA Noob

[Carim]

Martin,

Do you mean ... ?

Please Login or Register  to view this content.

HTH
Carim

[Jubelnar]

Sorry... example was not as good presented, as "spaces" was not included...

What I need is simply a formula, which by correction changes other formulas.

Simplified Example from above with 4 coloumns:
A: Number
B: Number
C: Formulas summing A & B
D: Super Formula where from I can change formulas in C, so they example wise multiply A & B instead of summing.

Thanks a lot for your reply..

Regards
Martin

[VBA Noob]

Surely you just enter in D1

=A1*B1

Or would this link help

http://www.j-walk.com/ss/excel/tips/tip43.htm

VBA Noob

[Bryan Hessey]

Sorry... example was not as good presented, as "spaces" was not included...

What I need is simply a formula, which by correction changes other formulas.

Simplified Example from above with 4 coloumns:
A: Number
B: Number
C: Formulas summing A & B
D: Super Formula where from I can change formulas in C, so they example wise multiply A & B instead of summing.

Thanks a lot for your reply..

Regards
Martin

Hi,

In A1 your figure

In B1 another figure

In D1 number selected from the range 1 to 11 (for all amounts) or 101 to 111 (to excude hidden amounts) - specifically 6 or 9

In C1 =SubTotal(D1,A1:B1)

should solve your request.

hth
---

[Jubelnar]

Thanks...

I know how to sum and make formulas for several tables and cross links. What I need is a "super" formula, which defines and correct other cells with that formula.....

Sorry if specification is not too clear..

Thanks in advance

[rmadhusudanan]

hi martin,

These folks on this forum are extremely knowledgeable and can solve almost anything. However, you need to pose the problem clearly. Suggest you ask the question something like this (after altering it to suit your requirement)

A1 = 10, B1 = 2, FORMULA IN C1 = A1+B1, SUPERFORMULA IN D1 = A1*B1( I WANT THIS FORMULA IN C1 TO CHANGE TO A1*B1 WHEN I INPUT THE SUPER FORMULA(A1*B1) IN D1)

A2 = 5, B2 = 6, FORMULA IN C2 = A2+B2 ( I WANT THIS FORMULA IN C2 TO CHANGE TO A2*B2 WHEN I INPUT THE SUPER FORMULA(A1*B1) IN D1)

A3 = 8, B3 = 12, FORMULA IN C3 = A3+B3 ( I WANT THIS FORMULA IN C3 TO CHANGE TO A3*B3 WHEN I INPUT THE SUPER FORMULA (A1*B1) IN D1)

SUPPOSING THE SUPERFORMULA IS INPUTTED AS A1/B1, THEN ALL THE FORMULAS IN C1, C2 ETC., SHOULD CHANGE TO A1/B1,A2/B2, A3/B3 ETC.,

Good luck

Madhusudanan

[Jubelnar]

Thanks.... and sorry for the not too clear problem discription...

[Bryan Hessey]

Thanks.... and sorry for the not too clear problem discription...

Hi,

I hope that the post by rmadhusudanan was the answer you wanted rather than the question, as far as I know there is no formula that will alter the formula in another cell.

There can be VB code that will react to D1 and reset the formula in the C column, is that what you seek?

---

[Jubelnar]

No - his post was for helping me to define the problem/question.

Problem unfortunately not yet solved.

I have used a lot of vb coding for macros and other functionalities, but how can I use this for having a master formula?

[Bryan Hessey]

No - his post was for helping me to define the problem/question.

Problem unfortunately not yet solved.

I have used a lot of vb coding for macros and other functionalities, but how can I use this for having a master formula?

Please Login or Register  to view this content.

put into the Worksheet code.

ie, rightmouse the worksheet tab and View Code, put there.


then put A1/B1 into cell D1
---

[Jubelnar]

Thanks for suggestion..... I will try....

I succesfull you have just helped a 7 factory company controlling their stock holdings....

THANKS!!

[Bryan Hessey]

Thanks for suggestion..... I will try....

I succesfull you have just helped a 7 factory company controlling their stock holdings....

THANKS!!

Good that it worked for you, and thanks for the response.


note, it changes column C formula to the extent of data in column A, that can be changed if required.

Good Luck.

---