Ordering Dates

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I have a spreadsheet which has a list of projects on it.
The columns next to the project name contains various bits
of information, one of which is the tester we plan to use.
What i want to do is set up a calendar on another sheet,
so i can see how often i am using each tester, and then
check i haven't given them two jobs on consecutive days
hundreds of miles apart, and to show them their upcoming
schedule.

The inadequate solution i have done so far involves
copying the cells containing the date and tester onto a
new sheet (into 2 columns), and then after the date data,
inserted all the dates for the next two years. Then i have
sorted by column A, which puts all the Testers dates
spread along the calendar.

The problem with this is that there are many duplicate
dates which i wish to get rid of, and i also would like to
just view it on a month by month basis.

I hope i have given enough information for you, and that
you may be able to help!

Regards

Richard
 
Richard,

Here is a suggestion.

Have a matrix of testers and dates on say Sheet3, testers in rows, say
A2-A10, dates in columns in B1:AA1 (or beyond). Assuming data on Sheet1,
testers in column B, dates in column C. Then in B2 on Sheet3 use

=SUMPRODUCT((Sheet1!B1:B100=$A2)*(Sheet1!C1:C100=B$1))

It will then show 0, tester not used on that date, 1, only used once, 2
twice, etc. A very visible record.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top