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

**TrevP** · 07-25-2014, 04:30 PM

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?

**gillyr7** · 07-25-2014, 04:53 PM

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.

**TrevP** · 07-25-2014, 05:02 PM

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.

**gillyr7** · 07-25-2014, 05:50 PM

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.