Multiple data entry leading to multiple worksheets....I'm baffled!!

Hi, wonder if someone might help me out?

I've attached a sample of spreadsheet that hopes backs up this explanation!

Basically, I'd like to create data of work done by different people, sometimes charged at different rates (depending on the work done), where the data is then split into a number of different areas.

Hopefully, the attached example spreadsheet will make sense. Let's say I manually enter "AB" in "Contractor" column, then "1" as "Contractor reference", that should then determine a a rate appropriate to the reference in the "Rate" column. The "Hours" column reflects the time taken to do the job and will be manually entered. The "Amount" column is a simple multiple of rate / hours.

The columns marked "Phase", "Task" and "Activity" will be manually entered to reflect how the work is distributed within a number of options in each category. So, for example, we have between 1 and 15 phases, 1 and 41 tasks, and 1 and 10 activities.

Each time we allocate a phase / task or activity number, I'm trying to get a seperate worksheet to total up everything in each phase or task that has an amount recorded. On a seperate worksheet, I need the same detail as "Activity". As you'll hopefully see, the total amounts will always be the same under each category as it is for the total in the original.

I do hope this all makes sense! As you'll see, I've given an example of the initial data entry and, lower down the spreadsheet is a sample of what I'm trying to get automatically in two seperate worksheets within the same workbook - let's call one of them "Phase Task" and the other "Activity"

You'll see that, for example, there are two entries allocated to "Phase 6" - the total cost in this sample is £44 and that is the amount that appears on the "PhaseTask" sheet against Phase number 6.

It's worth perhaps mentioning that the original data entry will be unlimited rows of data.....

Just in case the attachment hasn't worked, here is a copy of the sample sheet.....

MANY thanks for anyone who can help me!!!

Mike

Contractor Description of work done Contractor reference Rate Hours Amount Phase Task Activity
AB 1 £20.00 0.20 £4.00 6 8 2
AB 2 £30.00 1 £30.00 4 9 4
HP 4 £20.00 2 £40.00 6 7 2
Total £74.00

Contractor reference determines rates which may differ per contractor Data to auto-populate to Separate sheet (let's call it PhaseTask sheet) Data to auto-populate to Separate sheet (let's call it Activity sheet)
Eg AB 1 £20
AB 2 £30 Total Amount Total amount Total amount
CR 3 £10 Phase 1 Task 1 Activity 1
HP 4 £20 2 2 2 £44
3 3 3
4 £30 4 4 £30
5 5 5
6 £44 6
7 7 £40
8 8 £4
9 £30
Total £74.00 £74.00 £74.00

Not sure if I understand but please try.

F3 =VLOOKUP(E3,$C$9:$D$12,2,0)
I11 =SUMIFS($H$3:$H$5,$I$3:$I$5,H11) you can use sumifs for task and Activity

FlameRetired thanks, I didn't look for version and file extension.
I11 =SUMIF($I$3:$I$5,H11,$H$3:$H$5)

Guys, this is wonderful thank you. Just a couple of follow ups if I can?

I've attached another copy of the sample spreadsheet and used the SUMIF formula suggested......as you can (hopefully!) see, everything looks great! The total (in this sample data, £140) appears in H9 as it should and also appears, as it should, in I24, L24 and P24. Thank you!!! But, if I don't have any data in cells E3:E8, the response is very different! No data in any of those cells gets a response "#N/A" in corresponding cells in columns F and H, as well as H9.

It also completely throws the bits I'm after (rows 12 to 24)!

Is there any way that lack of data in cells E3 to E8 would allow the same totalling / transfer of data without the "#N/A" response?

Thank you - again - so much

Genius!!!

Thank you SO much for your kind help. I really appreciate it!!!

Best wishes

Mike

Guys, I'm nervous about asking for a little more help, but have just been reminded there is some more automation needed....

Once again, a sample spreadsheet attached should help explain things! As you'll see, I've added a section (yellow filled cells)....Is it at all possible to have this section collecting data from rows 2-7 and collating it here? So, contractor AB / reference 2 appears twice and what I'd love the sheet do is to add up the hours and total amount as per the sample data I've manually entered.

Once again, your help really appreciated....Thank you!

Thank you for this. I'm following insructions to trim trailing spaces as per here (https://www.ablebits.com/office-addi...-entire-column) but failing miserably! When I do this, and then copy the formulas, two things happen:

Cells Q2:Q5 turn white and blank
Cells R2:R5 respond "#N/A"

I'm sure this is my inability to follow the instructions around trailing cells properly! But wondered if you might advise?

Thank you!

Folks, I'm sorry to ask again. This time, I've attached a further spreadsheet that I hope sets out the final part of what I am looking to achieve. "FlameRetired" - I have managed to get the formula you suggested to work (thank you), but it seems only to record the number of times a "contractors reference" is added, not the number of hours that each entry records. And, I;m afraid the result of the second formula you suggested merely gives me a blank cell....Perhaps - probably - it's me.....

Just to recap - at this final stage, what I am trying to do is to be able to enter, manually, details of - lets say - work done by different "contractors" and to then ask the spreadsheet to calculate, from what is likely to be many entries, details of a) how many hours each contractor has recorded and b) the cost per contractor.

There will be a set "pool" of contractors, but not all will be used to do tasks, and there will be times where the hours worked are recorded seperately - for example, different dates might be required over a period of time. So, as I hope is clear from the attached, this manual data would be in the section marked "Data entered to spreadsheet". The section in yellow fill is the part I'm hoping will populate correctly! For the purposes of this request, I've entered data to the yellow fill manually in an effort to demonstrate the outcome I'm hoping can be achieved automatically! (In this case, it wasn't hard to do - but if there are many,many different entries, that task becomes a lot harder to do manually!)

Thank you very much - again!!

Originally Posted by FlameRetired

An oversight at my end. The first formula has COUNTIFS. Excel 2003 does not have COUNTIFS. If you are not getting an error in Q2:Q5 I suspect you have newer version. If that is the case please update your profile ... members tailor solution with that in mind (usually ).

Could you upload the file you are talking about ... with sensitive data fictionalized?

Oops, sorry I've just seen your reply, thanks!

Perhaps my latest submission will help you better! Hope so, thank you

Originally Posted by FlameRetired

An oversight at my end. The first formula has COUNTIFS. Excel 2003 does not have COUNTIFS. If you are not getting an error in Q2:Q5 I suspect you have newer version. If that is the case please update your profile ... members tailor solution with that in mind (usually ).

Could you upload the file you are talking about ... with sensitive data fictionalized?

Sorry, I'm all at sea today.....I use Excel 2007 (profile now updated, thanks for the suggestion!)

Sox, my fault this time......hopefully, the attached new version will be better!

Hi thanks so much for your help. I'm afraid my brain hurts.....and I'm just not able to get my head around this. I thought it would be so simple!

As a last push, I've attached an extract from the actual workbook I'm using and wondered if you would be able to help by putting the formulas you suggest into the yellow area?

I know I'm asking a lot, but I am genuinely unable to get my head around what is needed here!

As you'll see, Ive out some sample data in and hoipe it makes sense. Everything you've advised before works really well - thank you - and I've managed to get the results appearinng elsewhere in the workbook. It's just this one last thing....

Thank you.