- Jun 5, 2006
- 16,101
- 2
- 56
I have a spreadsheet that has the following, as an example.
A1 is an integer from 1-10, user-defined, can be changed at any time
B1 needs to be a drop-down menu, has 2 different possible options and must not be able to choose anything else (must drop-down and click, not type)
C1 is one of 2 or maybe 3 preset integers, depending on what is chosen in cell B1
D1 is one of 2 or maybe 3 preset integers, depending on what is chosen in cell B1
E1 is an integer, the a function, the result of which is based on the data chosen or typed into the other cells
F1 needs to be a drop-down menu, has choice of "yes" or "no" or "pending" and must not be able to choose anything else (must drop-down and click, not type)
I'm not sure if I need to actually explain what each cell is for specifically and why, but if I need to I can.
A user will start from a blank row in an existing worksheet. They'll enter an integer into A1. Then they'll select one of 2 or maybe 3 options in B1. That will auto-populate cells C1 and D1. As an example, if they want to set cell B1 for the option "Option 1" then it will set C1 to "100" and D1 to "200". If they go back to B1 and change it to "Option 2" then it will set C1 to "50" and D1 to "100".
From there, they'll then choose a one of the 3 options in cell F1 which should be "yes" or "no" or "pending", which will force cell E1 to calculate (or recalculate if it's already been populated).
Cell E1 is the key, it's the desired end result, the kingpin of the process.
When cells A1, B1, C1, D1, and F1 are populated, and F1 is set to "yes" then E1 should calculate a formula, taking the integer in A1, subtract 1, and multiply by the integer in D1. Then it will add the result to the integer in C1. The result would then be displayed in cell E1.
If cells data is missing or "pending" is selected in F1, then cell E1 must be some sort of null. I'd prefer it to be blank, but if it needs to be set to 0, I can live with that.
If F1 is set to "no" then E1 must be set to zero (which is why the "pending" option should be something different).
...is it possible to do this? I know some basic Excel functionality, but I'm not sure how to create drop-down menus in cells that are specifically limited to a pre-set list of options. I don't know how to choose one option in a drop-down menu which will force other cells to populate certain things.
Help?
A1 is an integer from 1-10, user-defined, can be changed at any time
B1 needs to be a drop-down menu, has 2 different possible options and must not be able to choose anything else (must drop-down and click, not type)
C1 is one of 2 or maybe 3 preset integers, depending on what is chosen in cell B1
D1 is one of 2 or maybe 3 preset integers, depending on what is chosen in cell B1
E1 is an integer, the a function, the result of which is based on the data chosen or typed into the other cells
F1 needs to be a drop-down menu, has choice of "yes" or "no" or "pending" and must not be able to choose anything else (must drop-down and click, not type)
I'm not sure if I need to actually explain what each cell is for specifically and why, but if I need to I can.
A user will start from a blank row in an existing worksheet. They'll enter an integer into A1. Then they'll select one of 2 or maybe 3 options in B1. That will auto-populate cells C1 and D1. As an example, if they want to set cell B1 for the option "Option 1" then it will set C1 to "100" and D1 to "200". If they go back to B1 and change it to "Option 2" then it will set C1 to "50" and D1 to "100".
From there, they'll then choose a one of the 3 options in cell F1 which should be "yes" or "no" or "pending", which will force cell E1 to calculate (or recalculate if it's already been populated).
Cell E1 is the key, it's the desired end result, the kingpin of the process.
When cells A1, B1, C1, D1, and F1 are populated, and F1 is set to "yes" then E1 should calculate a formula, taking the integer in A1, subtract 1, and multiply by the integer in D1. Then it will add the result to the integer in C1. The result would then be displayed in cell E1.
If cells data is missing or "pending" is selected in F1, then cell E1 must be some sort of null. I'd prefer it to be blank, but if it needs to be set to 0, I can live with that.
If F1 is set to "no" then E1 must be set to zero (which is why the "pending" option should be something different).
...is it possible to do this? I know some basic Excel functionality, but I'm not sure how to create drop-down menus in cells that are specifically limited to a pre-set list of options. I don't know how to choose one option in a drop-down menu which will force other cells to populate certain things.
Help?
Facebook
Twitter