Building formula

Hello Nat,

Thanks for your question. It is possible to do this. It will involve a few steps, though. If you haven’t done so yet, create a list of occupations. Select this list and create a range name for it (top left box above column headers). Name this range Yes. Then, click on a blank cell and create another range name, this time naming it No.
The idea is that when Yes is picked from the first drop-down, the occupations drop-down (which I’ll explain later) will populate with the occupations list and when No is picked, the list will be empty.

To create the occupations drop-down, go to Data – Data Validation – List. In the Source box, type the following: =Indirect($A$1) *Replace $A$1 with the cell reference which contains your Yes / No drop-down. The Indirect function will see the Yes or No in that cell. If Yes is picked, the occupations drop-down will show the occupations list, if No, there will be no drop-down options.

Next, write a Vlookup to match the chosen occupation to the corresponding value needed to multiply with years’ experience (if needed, create a drop-down for years’ experience).

Finally, create a formula to multiply the occupation value with the years’ experience. You will notice that picking No does not clear the occupation cell. Simply do this manually.

I hope this helps. Please post again if you need further assistance.

Kind regards
Marius Barnard
Microsoft Office Trainer