PDA

View Full Version : Solved: DSUM



TedMosby
03-13-2009, 02:36 AM
I have attached my workbook for what am doing, but my problem is that using the DSUM or DGET doesnt actually bring all the relevant data back, it seems to bring some and not others. I'm not sure why this would be, can anyone help.

Looking at the Tab - Workload Cells C6 & D6 pick up through DSUM but using the same formula the Cells C7 & D7 dont work.

Bob Phillips
03-13-2009, 03:42 AM
The criteria range for the 2nd item has lost the header, so DSUM doesn't know where to look.

Try

=SUMIF(INDEX(Rota,0,1),$B6,INDEX(Rota,0,COLUMN(E1)))

instead, although adding up start times doesn't striuke me as being appropriate.

TedMosby
03-13-2009, 03:57 AM
I dont understand that formula, can you explain it?

Where is $B6 & E1 come into it?

Bob Phillips
03-13-2009, 04:17 AM
B6 is the name that you want to get details for, Barney Stinson, E1 is just so as to get a column number of 5 to index into the Rota table, so that when you copy it across for the finish time, you will get column 6 in that instance.

TedMosby
03-13-2009, 05:38 AM
I tried this


SUMIF(INDEX(Rota,0,1),$B9,INDEX(Rota,0,(E$1)))

and in E1 I put 5 so that would look at Start Time in Rota.

The result was a time that isnt even on the rota table (19:30). When I did the same for the Finish it shows 13:00 a time that is before the Start Time.

How can this be?

Bob Phillips
03-13-2009, 05:41 AM
Why did you do that?

TedMosby
03-13-2009, 06:09 AM
Because thats how I read from your reply. It seems I dont understand what you have said. Could you show me on the Workbook I attached?

Bob Phillips
03-13-2009, 07:04 AM
I have also changed the formulae in the Appts table.

TedMosby
03-13-2009, 07:23 AM
Thanks for that, the Rota is working great, apart from when I change the date it doesnt change the Shift Patterns. The other problem I have now is that the Appt Timings (Row13 to 17) and Appointments (Row19 to 23). Pick up the times and details from the Appts Range but it picks it up regardless of the date. How can I change it so that it only looks at the relevant date DSUMCriteria Row6?

Bob Phillips
03-13-2009, 07:31 AM
Where is the date that gets compared against/changed?

TedMosby
03-13-2009, 07:53 AM
On Sheet Tab DSUMCriteria Cell C6 is the date that I want to control the Workload sheet.

Bob Phillips
03-13-2009, 08:38 AM
Here you are

TedMosby
03-13-2009, 08:59 AM
Cool, that works a lot better. Thanks :)

My only problem now is that the section Rows 21 to 24 keep the same Appointments from the 12-March through to anyday I choose. How can I get it to look at the date and show me the Appointments for the day?

Bob Phillips
03-13-2009, 09:13 AM
I didn't touch that part.

TedMosby
03-13-2009, 03:09 PM
I know you havent touched that part, am asking how I can get around this problem

Rows 21 to 24 keep the same Appointments from the 12-March through to anyday I choose. How can I get it to look at the date and show me the Appointments for the day?