Autofill cells based on conditions & unique values

Sarain - Feb 22, 2018 at 10:44 AM - Latest reply: ac3mark 10260 Posts Monday June 3, 2013Registration dateModeratorStatus August 15, 2018 Last seen
- Feb 23, 2018 at 04:34 PM
I am trying to auto populate cells that meet the same criteria with a value input by a user using either macros or a formula. For example, in the data set below if a user inputs a quantity of 5 in Column C where Column A is "fruit" and B is "apple" once the value is typed need the sheet to auto populate a 5 in Column C of all other rows which have "Fruit" and "Apple"


Example:
Col A Col B Col C
FRUIT APPLE 5
VEGETABLE CARROT
GRAIN BARLEY
FRUIT PLUM
GRAIN BARLEY
GRAIN WHEAT
VEGETABLE CELERY
FRUIT APPLE
VEGETABLE CELERY
See more 

Your reply

4 replies

ac3mark 10260 Posts Monday June 3, 2013Registration dateModeratorStatus August 15, 2018 Last seen - Feb 22, 2018 at 04:58 PM
0
Thank you
First of all, for any cell to have an automatic value assigned to it must be initated by a button push. With thta being said, the syntax for IF is as follows:
=IF(logic_test,True,False)

And the syntax for AND is as follows:
=AND(first_test_for_true,Second_test_for_True) [returns true if both are true.

So, with the above syntax, we can combine, starting with an AND. As in:

=AND(IF(a1="Fruit"),IF(B1="apple"),5)


And there you have it. Play with it and see what happens. I will check back if you have any questions.

Please understand I will not rewrite my examples to fit your model. The examples are there for you to learn by, not cut and paste into your homework!!!!!!


Sarain 1 Posts Thursday February 22, 2018Registration date February 23, 2018 Last seen - Feb 23, 2018 at 02:02 PM
Is there anyway to do the above computation before the number 5 is known. For example once a user inputs a value (5 in the example) if autocompletes the above?
ac3mark 10260 Posts Monday June 3, 2013Registration dateModeratorStatus August 15, 2018 Last seen - Feb 23, 2018 at 04:34 PM
No, it doesn't work like that.
Respond to ac3mark
ac3mark 10260 Posts Monday June 3, 2013Registration dateModeratorStatus August 15, 2018 Last seen - Feb 23, 2018 at 04:34 PM
0
Thank you
I am confused....

So I input a five, and you want the five to trigger what? A fruit? An apple? Is the five really an index to a fruit? I am confused, as your original scope has been addressed, and now it has chaned...

...there is a thing in the IT world, called SCOPE CREEP! This thing called SCOPE CREEP is why I only post examples, and not turnkey solutions. I always try to explain what the METHODS are doing, and how to make them apply to the OP's model. It always happens, the OP asks for something, then changes the question after I have spent time providing code and a a viable solution.

So, explain what you want the sheet to do now!

Respond to ac3mark