scanning worksheets to find date specific data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 22 Dec 2006 02:42 GMT

I am trying to analyse date specific data from several worksheets. I
am trying to track surgical refractive outcomes for 3 docs. I have 1
worksheet for each with a series of data relating to specific dates.
For example, original surgery date = 1/1/2006; then there is a visit at
1/2/06 with the collected data; another visit at 1/8 with more data
collected etc. How do I find the data the corelates to a three month
visit +/- 2 weeks and import that data to a separte worksheet for
anaylsis?

Thanks in advance for any help you can offer on this one!

Andy

Reply to this Message

Geoff Lilley - 29 Dec 2006 08:33 GMT

Andy:

This is a start. Don't know if I'm on the right track or not.

In Sheet 1, I have two columns:
1/1/2006    A
1/4/2006    B
1/5/2006    C

In Sheet 2, I have one column, with dates:
4/1/06
4/4/2006
4/5/2006

To find the corresponding letter (A,B,C) for those dates, I put the
following formula in B1:
=INDEX(Sheet1!$A$1:$B$3,MATCH(DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)),Sheet1!$A$1:$A$3,TRUE),2)

The function breaks down like this:
1) INDEX creates a database of cells A1:B3 in Sheet 1.
2) I'm looking for a date that's three months before the current date,
so I ask Excel to take the date in Sheet 2, A1, and subtract 3 months.
I don't like doing cell date minus 90, because not all months have 30
days. Then, I say, find the CLOSEST date to the result (thus the
TRUE), and give me the value from the second column (the letter A, B,
or C,); thus the 2.

Does that help? Post back, see if others can improve on what I've
done.

HTH
Geoff

> I am trying to analyse date specific data from several worksheets. I
> am trying to track surgical refractive outcomes for 3 docs. I have 1
[quoted text clipped - 8 lines]
>
> Andy

Reply to this Message

Mac Forum / Applications / Excel / December 2006

scanning worksheets to find date specific data