Excel 2003: Can a cell's value be automatically edited/updated?

[tmcrae]

I have a spreadsheet where I would like to incorporate some automatic editing logic.

For example, let's say in cell A1 I can key a number from 1 to 10. If, however, I key a 1, 2 or 3 in A1, I want to edit that value against whatever is in B1 (which can be "A", "B" or "C"). If B1 = "A", then the only valid value for A1 (from the set of 1, 2 or 3) is 1; if B1 = "B", then A1 must be 2 etc.

I want to be able to key either 1, 2 or 3 in A1 and have it automatically change (if necessary) to the correct value based on what is in B1, thus avoiding an error box from appearing and then rekeying the correct value. If the value is 4 through 10, no such editing (or, perhaps, a different set of edits) should apply.

Does anyone know of a way to accomplish this?

 

[tmcrae]

Oops...the title should read Excel 2007. Sorry about that.

 

[SkipVought]

Hi,

Take a look at Data/Validation.

Skip,

_[red][/red]

 

[tmcrae]

Hi Skip,

Well, actually I have employed Data/Validation before and it works quite well. But, in this instance, I want to be able to automatically change (not just validate) the value in a cell based on some logic automatically.

Right now, if I key an invalid number in A1, I get an error box which I then need to close and then I need to put the correct number in A1. What I want to be able to do is automatically change the incorrect value I entered in A1 to the correct one without my intervention. In other words, if I key a 1 in A1 and it is supposed to be a 2, then after I hit the ENTER key, the value in A1 automatically changes to a 2 and I can carry on entering data unimpeded.

I am not aware of a way to use Data/Validation to perform this function. If there is such a way, can you please point me in the right direction?

Thanks

 

[Gavona]

Given what is in B1 is there only one correct value for A1? In which case don't key into A1 at all - just have a formula - probably a vlookup.
If on the other hand there are several valid values for A1 but what these are depend on the value in B1 then a different solution is needed.


Gavin

 

[tmcrae]

In my original post, I had said that A1 could be any number from 1 to 10. But, if it were a 1, 2 or 3, then it would have to be edited against a value in B1. And, based on B1, the value in A1 would automatically be changed to a 1, 2 or 3. If, on the other hand, A1 were keyed with anything else (i.e. 4 through 10), this logic would not apply.

Sorry if this wasn't made clear.

 

[SkipVought]

You must use VBA Code. Post your question in Forum707.

Skip,

_[red][/red]

 

[yogia]

Hi tmcrae:

From your description, as Skip has already stated, it would require VBA. However if you want a formula solution, you may need to make your manual entry into a cell other than A1, say(A2) and then a check can be run to compare your entry in cell A2 with the value in B1 and the appropriate value can be placed in cell A1 automatically.

Depending on your project constraints, and/or your own preference, this method may or may not work you.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[tmcrae]

Thanks everyone for the feedback.

It sounds as though a macro may be my best (or only) solution. Can a macro be automatically triggered to run once a value has been entered into a field?

 

[SkipVought]

" Can a macro be automatically triggered to run once a value has been entered into a field?"

Yes. Please pursue the particulars in Forum707

Skip,

_[red][/red]

 

[tmcrae]

Thanks, Skip. I will.

 

[SkipVought]

Meanwhile, check out these FAQ's in Forum707...

faq707-4195
faq707-4973
faq707-4974
faq707-4975
faq707-4976
faq707-1945


Skip,

_[red][/red]

 

[tmcrae]

I actually got everything working yesterday afternoon. The VBA Forum you pointed me to lead me to the dicovery of the Worksheet_Change function. From there, it was just a few Google searches to find some examples, then copying and changing one to meet my needs.

Thanks, Skip.

 

[SkipVought]

tmcrae,

That's what I like to see! A person that can take a tip, like a horse takes the bit in its mouth, and runs with it!

You took a tidbit and dug for the answer. You're my kind of guy, even if you're a gal!

Up there in Manitoba, eh? Snow off the ground yet? ;-)

Skip,

_[red][/red]

 

[tmcrae]

Gal?!?! Oops...I must have checked the wrong box somewhere on the "Personal Information" form! I took a shower this morning, and everything was where it should be so as near as I can tell, I'm still a guy.

And yes, the snow is all gone here!

Thanks again, Skip.

 

[SkipVought]

Sorry, didn't mean to insult your. There is no M/F indicator in Personal Info, so just trying to be PC.

Carry on, eh!

Skip,

_[red][/red]

 

[tmcrae]

No worries! I wasn't insulted at all...just thought that I had made a mistake somewhere during the registration process!

Cheers!