I have a table with four columns: Item, Stock, Weight, and Serial. I would like to set up four consecutive cells that are conditional on each other, two dropdown menus and two autocalculates, for a form. The first dropdown menu would list the name of everything in the item column. The second dropdown menu would list integers starting at one and increasing until it hit the number in the stock column relative to the item selection. The next cell would multiply the selected stock by the number in the weight column relative to the item selection. The final cell would simply display a number that is in the serial column relative to the item selection.
To create the first drop down, I would just highlight all the items in the first column of the table and give it a name like "Items". Then in the cell I would want it to appear I would enter data validation, select list, and enter =Items.
However, I am unsure how I would make a second dropdown based on the first dropdown short of using a ton of if statements (and making it a pain to add new items in the future).
I have a similar problem with the third cell, as I could potentially set it equal to the previous cell multiplied by the weight using a ton of if statements. Same with the forth cell.
While I could just use the if statements, I know excel is much more powerful than I know how to use, so there is probably an easier way that I have never used or that I am overlooking. So if you could point me in the right direction of the function I need to use, I would be grateful.
Thank you for your time.
Bookmarks