+ Reply to Thread
Results 1 to 4 of 4

Auto filling in cells based on other column drop down selections

  1. #1
    Registered User
    Join Date
    08-22-2014
    Location
    Kelowna, Canada
    MS-Off Ver
    Office 2010
    Posts
    30

    Auto filling in cells based on other column drop down selections

    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.

  2. #2
    Registered User
    Join Date
    08-22-2014
    Location
    Kelowna, Canada
    MS-Off Ver
    Office 2010
    Posts
    30

    Re: Auto filling in cells based on other column drop down selections

    I've solved all my own issues except getting N/A to appear in cells based on the selection of column A.

  3. #3
    Registered User
    Join Date
    08-22-2014
    Location
    Kelowna, Canada
    MS-Off Ver
    Office 2010
    Posts
    30

    Re: Auto filling in cells based on other column drop down selections

    So the only thing I need answered now is if "Amy" is selected as option in Cell A2, and I want Cell D2 to automatically switch to N/A (which is one of the options in the drop down for that cell) how do I do that?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,276

    Re: Auto filling in cells based on other column drop down selections

    This is a 'one and done' meaning that it will work the first time only. Before applying data validation to to D2 paste this formula in the cell: =IF(A2="Amy","N/A","") If you select Amy from the last in A2, N/A will appear in D2. If you then select one of the other names the cell goes blank until a value is chosen. If you again change to Amy the chosen value remains as the formula has been erased. The formula can be restored by using the undo button on the home tab (or Ctrl + Z). The formula for this solution goes is a variation on the one proposed by JBeaucaire in this thread (post #2).
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 01-29-2014, 01:35 PM
  2. Copying cells from one sheet to another then filling down based on previous column
    By mahowarth in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-07-2013, 08:18 AM
  3. Replies: 17
    Last Post: 02-05-2013, 08:20 PM
  4. Replies: 0
    Last Post: 08-10-2012, 12:55 PM
  5. Filling Cells Based on Drop Down Selection
    By Dredger in forum Excel General
    Replies: 14
    Last Post: 06-07-2011, 01:37 AM
  6. Auto filling cells based on drop down menu selection
    By Candace84 in forum Excel General
    Replies: 1
    Last Post: 11-19-2010, 02:59 AM
  7. Replies: 3
    Last Post: 11-04-2010, 10:00 PM
  8. Filling blank cells based on neighbouring column
    By ppataki in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-02-2009, 06:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1