Auto fill cells depending on Drop down List selection

[Trengor]

Hi,

I am trying to make a calendar spreadsheet to enable me to track allowance payments i am due from my company.

This payment is worked out by three conditions - where i am working (7 regions), how many days of the week (4,5,6 or7) and how many hours (<7,8-9 or 10-12)

I have made drop down lists to select each of the values for the three conditions and want to show the total for them

i can total the entire spreadheet for the month up using fairly large "COUNTIF" statements, but i want to be able to show what the exact amount is in each cell not just a complete total.

i.e. i am away for 6 days i get £15 extra, if i am in Europe i get another £50 and if i work 12 hours i get £30.

I want to be able to calculate this total in each cell for that day.

I can send a copy of the spreadsheet i have got at the moment if that will make things clearer than what i have tried to explain.

[NBVC]

Yes, attach a sample copy of your workbook showing what you mean.

[Trengor]

Here is a copy of the workbook and also what the allowance table we have to work from.

I am basically trying to make it that i can select the necessary parts and for it to display the total allowance for that individual day.

The formulas i have currently got in use to total up the number of each drop down selection are fairly long, but if i can get the spreadsheet to total up for each day then i will not need these.

[NBVC]

I am not too sure that I follow...

can you perhaps reattach the sheet with some sample input and expected results...

Also, if you need to refer to the table for any calculations, then re-attach it as an Excel sheet.

[Trengor]

I attached the table just to show what i was hoping to get the workbook to be able to total up for each day.

From the examples i have added in, it shows that i can total the amount of times each box has a selection using countif and then to just multiply the result by the amount paid for a total at the end of that row.

The red figures in the bottom right of each example day is what i am trying to get the spreadsheet to auto calculate as a sum of the three different payments for the drop down box selections.

i.e. i get £15 for working a 6 day week + £50 as i am in europe and not the UK + £15 for working an 8 hour day =£80

What i am hoping to do is remove all the result boxes from the bottom of the sheet and just have it auto adding the amount paid for each of the different drop downs and to display the total in each day.

I know it would be easier to just have the amounts as the drop downs but that means i got to remember the amounts for the different selections this way i can just enter how long and where.

I hope this explains it a bit better.

[NBVC]

Well, you can replace the formula in X16 with the following:

=COUNT(IF((MOD(COLUMN(B4:T12)-COLUMN(B4),3)=0)*ISNUMBER(SEARCH($F$15,B4:T12)),1))*30+COUNT(IF((MOD(COLUMN(B4:T12)-COLUMN(B4),3)=0)*ISNUMBER(SEARCH($F$15,B4:T12)),1))*0+COUNT(IF((MOD(COLUMN(B4:T12)-COLUMN(B4),3)=0)*ISNUMBER(SEARCH($F$15,B4:T12)),1))*15+COUNT(IF((MOD(COLUMN(B4:T12)-COLUMN(B4),3)=0)*ISNUMBER(SEARCH($F$15,B4:T12)),1))*30

and then you don't need the individual totals in F16, I16, L16, and O16

You can do similar in the the X cell sums... the formulas may be long though...

Basically the formula is a concatenation of several of these subformulas: COUNT(IF((MOD(COLUMN(B4:T12)-COLUMN(B4),3)=0)*ISNUMBER(SEARCH($F$15,B4:T12)),1))*30

which Counts if there is a match to what is in F15, in every 3 columns after column B. and multiplies the result by your multiple (e.g. 30 in this case).

Note: After you enter these formulas, they must be confirmed with CTRL+SHIFT+ENTER not just ENTER..where you will see { } brackets appear around it.

I attached your sheet showing the first formula in X16 with result you had.... you can delete the other stuff in Row 16 and the result should still remain.

[Trengor]

Thanks for that NBVC that will come in useful, but i'm finding it hard to fully explain what i am trying to do.

i'm looking to get rid of the cells in row 15 and below and then just have a total for each individual day not one for the whole month.

For the example i filled in for the 16th of the month (H8-J9) i would like to just have the total (J9) for the amounts for each different drop down.

e.g H8 needs to be changed from the selected to either £30 for 5s, 0 for 5, £15 for 6 and £30 for 7.

I8 would be £110 for offshore UK, 150 for offshore WW, £40 for UK, £50 for Euro, etc

J8 would be £0 for 7, £15 for 8 & 9 and £30 for 10, 11 & 12

i.e So that when i select 6, Euro and 8 it will then know to add 15, 50 and 15 to give me the total of 80 in J9.

I get the feeling from your last reply that this will either not be possible or will need a lot of work to setup and is not as easy as i first hoped.

[NBVC]

I think then you would need to use the table you initially posted with Vlookup or Index/Match to get those results...

Can you post that table as an Excel sheet?

[Trengor]

unfortunately not at the moment as i just took a screen shot of it as i only have it in a pdf document.

I will try Vlookup or Index/Match once i get it into an excel, until then i will use what you posted and sent back to me before.

Many Thanks for helping me out with this.

[NBVC]

Actually,

Perhaps this formula should do it...in J9

=LOOKUP(H8,{5,"5s",6,7},{0,30,15,30})+LOOKUP(I8,{"Africa","Euro","Offshore UK","Offshore WW","South America","UK","USA/Aus","West Africa"},{80,50,110,150,70,40,60,90})+LOOKUP(J8,{7,8,9,10,11,12},{15,15,15,30,30,30})

It consists of 3 LOOKUPs for each of H8, I8, J8 inputs.



You will see that the 1st argument in each Lookup is the reference cell, the 2nd argument is an array of Lookup Values (they must be in ascending order), and the 3rd argument is an array of respective corresponding results...

You may need to change/adjust to suit.

You can then copy the formula to the other daily cells.

Hope that helps.

[Trengor]

Again many thanks for the help.

I will try that in a bit when i get a chance

[Trengor]

I just had a look at what you sent back to me and that is exactly what i have been trying to do.

Thanks

[NBVC]

Great!

Thanks for the feedback.