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?

Thread: Solved: DSUM

Thread Tools

Display

Solved: DSUM

Posting Permissions