+ Reply to Thread
Results 1 to 5 of 5

Adding a value to a cell based on text chosen from a dropdown?

  1. #1
    Registered User
    Join Date
    07-25-2014
    Location
    Home
    MS-Off Ver
    2003
    Posts
    6

    Adding a value to a cell based on text chosen from a dropdown?

    Hi,

    Is it possible to add a number to a separate cell when a text value is chosen in a different one.

    For example this is basically for a rota system, and each of the tasks has a time it takes to do them, I would like to have it so when i choose a persons name it will automatically add the time that task takes into a seperate field for their name so I can see the total time they will be spending on tasks.

    So if I choose the same person for several different tasks, for example 1 that takes 1 hour , 1 that takes 2 hours and 1 that takes 3 hours, a separate field will update to "6", and if i remove them from the 2 hour task it will go down to "4".

    If this is possible could you give me some example formulae to get this to work?

  2. #2
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    Re: Adding a value to a cell based on text chosen from a dropdown?

    If you have a dropdown, I am assuming you have a list of the names somewhere. If you have the time it takes to complete each task for all of them, you can use a sumifs based on the activities. It is impossible to give you a better answer when you do not show your workbook or say where your data is.

  3. #3
    Registered User
    Join Date
    07-25-2014
    Location
    Home
    MS-Off Ver
    2003
    Posts
    6

    Re: Adding a value to a cell based on text chosen from a dropdown?

    I am building this from scratch so whatever the best option would be.

    As in attached picture examplerota.gif it would be something like this, so if Task 1 will take 1 hour, Task 2 will take 2 hours and Task 3 will take 3 hours. If I choose the persons name from the drop down for that task for that day it will add the amount of hours it takes to the 2nd table below.

    There will be a list of names and timings on another sheet.

    One way I have come across that will work is by doing a seperate table for each person and doing this formulae, =IF(Sheet1!D4="Bob",1,0) , for each task on each day, replacing "1" with the time taken for that task, and then taking the total for each day and displaying just the total on the second table on sheet 1. However this seems to be a very messy way of doing it.

    //Edit

    Think I figured out what you meant by using sumif.

    I have come up with this formulae =SUMIF(Sheet1!D4:E13,"Bob",Sheet2!D20:D24) which when i have a list of timings works a charm.
    Last edited by TrevP; 07-25-2014 at 05:49 PM.

  4. #4
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    Re: Adding a value to a cell based on text chosen from a dropdown?

    Tasks and Hours.xlsx
    Take a look at the attached file. It should do what you need. There is a table showing all of the tasks, employees and number of hours it takes for each. The first table on the second tab has hidden columns, unhide them to see how they work.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Adding a value to a cell based on text chosen from a dropdown?

    The formula given in the previous example works fine. Here is an idea for your dropdown list which is expandable.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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: 3
    Last Post: 01-08-2014, 03:56 PM
  2. Change text colour based on list option chosen
    By supergirl21 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-12-2013, 03:11 AM
  3. Replies: 6
    Last Post: 05-21-2013, 04:57 PM
  4. Changing number of days in a month based on month chosen from a dropdown list.
    By st_judeu@yahoo.com in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-08-2013, 02:26 PM
  5. Output value based on what is chosen in dropdown list
    By russell.suereth in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-03-2011, 09:51 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