This is a multiple part issue I am having with attempting to use drop down fields that will automatically change other cells in other columns. Lets say in column A I have a drop down list consisting of four names (John, Joe, Amy, Rick). In Columns B, C, D I have a drop down consisting of options N/A, 0, 1, or 2. IN Column E I want a sum feature to add up the total values from Columns B,C,D while ignoring any cell with N/A input . In Column F I want a total max possibility score (so if the value 2 was selected in each column B,C,D), however if N/A was selected in any of the columns, then the total would decrease by that amount (IE: N/A was selected in one column on the same row then the total max would be 4, not 6).
In addition, I would like some of the drop down fields in columns B,C,D to fill in automatically with N/A if certain selections were made in column A. For example, if Amy was selected from drop down in Cell A2, then cell D2 would automatically switch to N/A and the total possible score in cell F2 would automatically drop to 4 (as it can only total scores from cells B2 and C2 now, each with a max of 2). If cell B2 was than manually set via drop down to N/A, F2 would again drop its value (from 4 to 2).
This is a simplified example of what I am actually trying to accomplish. Thanks in advance.
Bookmarks