excel, without script, just using its built in fea

Question

Need advice on the following; Thanks in advance.: Excel, the visual description of the issue

Further description of the issue: certain Product code = certain Product name of certain Size. The help-table on the attached image shows that a certain product code = what product name of what size, as a guide.

This is what I try to do. This is what my question is: How to achieve: Manual entry in cells of column A should trigger automatically filled content in cells of columns B and C based on the content of A and based on the dependencies described in the help-Table.

Edited: As the user proceeds adding new records in new rows, the appropriate columns (B and C) should "fill itself" automatically as soon as there's an entry in the same row in column A. This automatically filled content in column C and B depends on what is the content of column A as can be seen on the attached image("help-table"). And all this should work without me using a formula in columns B and C.

So far I used an "IF" formula which is not practical for many possible product codes. A formula is also impractical as its usage requires the formula to be copied to next row, which I want to avoid. I want the set rule to be applied to a whole column.(for example as the data validation or conditional formatting work in excel)

It would be ideal to use it in a way like the conditional formatting works, but in this case it should work with values(content) instead of colors and should be applicable for an entire column without a need of copying to the next row. That means I'd like to avoid macros or VBA if possible, just want to use built in features.

I already use Defined names and Data validation for columns A,B and C which works for me as a choice list and also to control column B and C for allowed entries only, so their content is not mistyped and has the strict format needed. Excel 2010 -if that matters.

Important: there will be 1000's of rows added, I just made a short description above

Formula for cells in column B:

=IF(A2=10,"prod1",IF(A2=20,"prod1",IF(A2=30,"prod2",IF(A2=40,"prod2",""))))

Formula for cells in column C:

=IF(A2=10,"50",IF(A2=20,"75",IF(A2=30,"10",IF(A2=40,"50",""))))

Also tried a CSE formula for the entire column B or C like follows (but the file size was couple of MB which is not desired):

=IF(A:A=10,"prod1",IF(A:A=20,"prod1",IF(A:A=30,"prod2",IF(A:A=40,"prod2",""))))

Answer 1

If the entry in column A will be only 10,20,30 and 40 then use OR formula in column B

=IF(OR(A2=10,A2=20),"prod1","prod2")

In Column C we can reduce two IF

=IF(A2=20,"75",IF(A2=30,"10","50"))

And regarding the file size please save it in binary format. Hope this will help :)