sort into several columns

Hi Guys,

I am trying to build a spreadsheet to analyse some data, I need to be able to sort totals into time columns,

If Column 'A' is the total, Column 'B' is the time and Columns 'C' onwards are the time slots from 07:00 to 17:00 in 30min increments, I need a formula so that if I have say 10 in 'A1' and 08.45 in 'B1', then 10 will be the result in 'F1' ie the column for 08:30 - 09:00.

Is this possible, would save me an awful lot of data inputting if it is, I am using Excel 2002 SP3.

Phil.

 

Hi Guys,

I have managed to sort out a formula that seems to work, but the problem is I can't drag it across the spreadsheet as I only want some of the cell references to change not all of them.

The formula I have used is:

=IF(B2>C1,IF(B2<D1,A2,0),0)

Where I need B2 and A2 to stay the same as I drag horizontally, and when I drag vertically C1 and D1 stay the same

I hope that the attachment will help make this clearer. Any help would be appreciated