Enter any one cell, change the others (1 Viewer)

[TomH]

I was asked by a coworker if this is possible, but I cannot find a way to get around a circular reference problem.

Simplest example I can think of is this: Let's say you have three related values such as radius, diameter and circumference. Is it possible to have three cells with these values so that as I change any one of them the other two will update to the correct related values?

I tried scenario analysis, vlookup, if-then... nothing. Any help is GREATLY APPRECIATED. If there is a simple VB answer, I would welcome it. I have used VB in Access but never in Excel, so the chance to write my first VB code in Excel would be a good thing... as long as it is simple enough for me to understand and explain to my coworker.

THANKS!!!

 

[jeffreyccs]

Hi Tom

One way to achieve this is using the Worksheet Change Event which will trigger when a cell is changed.

Attached is a sample using your example and this can be easily adapted for other scenarios.

Regards

Jeff

 

[TomH]

Jeffrey:

That appears to be EXACTLY what I need. In fact, I have numerous applications for this. Fantastic. THANK YOU.

I do have two additional questions, though. I understand what is happening, but I don't understand this line:

If Not Intersect(Target, Range("D2")) Is Nothing Then

Can you tell me what is happening there?

Also, I'm not sure I understand why there is separate code for the EnableEvents = true in the Module1 code and then the EnableEvents = False in the Sheet1 code.

Thanks again for all your help!
Tom

 

[jeffreyccs]

Hi Tom

Sorry for the delay in replying.

First of all you can delete module 1, this was just for me to use to enable events in case of an interrupted macro and does not have anything to do with the answer, I just forgot to delete it.

The If Not Intersect code is to tell Excel to only carry out the action if a change is made to the referenced cell as you do not want excel making changes if other cells are altered, it is just that the syntax is a little confusing.

Please let me know if you have any other questions.

Regards

Jeff

 

[TomH]

The If Not Intersect code is to tell Excel to only carry out the action if a change is made to the referenced cell as you do not want excel making changes if other cells are altered, it is just that the syntax is a little confusing.

Ahhh... the syntax. Thanks again, Jeff. I understand its purpose and it does work great. I was able to modify it to my need here and I see potential for many more applications. It's a matter of familiarity I guess.

Is there a list of common VBA applications in Excel... an FAQ guide, so to speak? I record macros and can modify the code readily to fit a certain situation... but if I were to learn, let's say, one new VBA command a day for a month, any ideas on which functions would offer me the most 'bang for my buck'?