Use dates to define a set of data?

[semper_si]

My first post here I'm afraid i've hit a snag in a project I'm doing at the moment, which is essentially a logbook of medical cases I've been involved in.

I've created a spreadsheet and a summary form for that spread sheet made up of lots of functions extracting data from the spreadsheet. What I want to do now is to have way by which a person can type in a start date and end date, and the summary functions then only execute on spreadsheet cells that are between those two dates.

I guess what I mean is that I want to define the range in which the functions extract the necessary data by the use of dates, which can be altered at will.

Does that make any sense?! Any help will be very very gratefully received

[duane]

"I guess what I mean is that I want to define the range in which the functions extract the necessary data by the use of dates, which can be altered at will"

what functions?

If the date is part of the record then you can easily use two ifs to determine if the record is in the date range.

[VBA Noob]

Hi,

Could you use a input box and filter

Try the below

Please Login or Register  to view this content.

Change Field 2 to column with date

VBA Noob

[semper_si]

"I guess what I mean is that I want to define the range in which the functions extract the necessary data by the use of dates, which can be altered at will"

what functions?

If the date is part of the record then you can easily use two ifs to determine if the record is in the date range.

Thanks for the input. The date is part of the record - all the records are date labelled. One of the (myriad) of functions is this, for example:

=COUNTIF('Aug 05'!G2:G341,"ENT")+COUNTIF('Aug 06'!G2:G301,"ENT")

where I count the number of columns in sheets Aug 05 and Aug 06 that are labelled ENT. What I want to do is count the columns for a particular range of dates (defined in column A in each spread sheet (cell A2 onwards)). More importantly I want to be able to change the range of dates when needed.

[semper_si]

Thanks VBA Noob for the idea of VB - I'm thinking about it, but part of the problem is that I need to return a number in a summary sheet. The forumla above is just one of the many:

A snapshot is here

Where the formula is contained in cell B8.

Hope that makes things clearer.

[duane]

no need for vba - just sub the cells with the dates in below, and also the column of dates

=sumproduct(('Aug 05'!G2:G341="ENT")*(firstdate>=range with dates)*(seconddate<=range with dates))+

same thing for other sheet


COUNTIF('Aug 06'!G2:G301,"ENT")

[semper_si]

duane: Wow!! Fantastic, that seems to have done the job! Thanks so very very much!

Somehow I've also managed to get this to work with another more complicated formula. From the image I linked to, you may notice the age range options. Basically, I've got a formula that tells me how many patients on an ENT list, say, were aged between 1 - 5. After using your suggested method, where L3=date1 and H3=date2:

=(SUMPRODUCT(--('Aug 05'!G2:G341=A8),--('Aug 05'!C2:C341>=1))-SUMPRODUCT(--('Aug 05'!G2:G341=A8),--('Aug 05'!C2:C341>5)))*((L3>='Aug 05'!A2:A341)*(H3<='Aug 05'!A2:A341))+(SUMPRODUCT(--('Aug 06'!G2:G341=A8),--('Aug 06'!C2:C341>=1))-SUMPRODUCT(--('Aug 06'!G2:G341=A8),--('Aug 06'!C2:C341>5)))*((L3>='Aug 06'!A2:A341)*(H3<='Aug 06'!A2:A341))

That's one huge formula! Is this really the main way of achieving this?

On a side note, if I am to copy this into various cells, is it possible to ensure that just the A8 label increments by one to A9, rather than all the cell labels?

[duane]

to answer your question, sure, just change all other adresses and rabges to absolute (change 'Aug 06'!G2:G341 to 'Aug 06'!$G$2:$G$341 for example)

also, you can make all the sumproduct "*"'s ",--" if you like

[duane]

oh, you can also combine the age range into one sumproduct rather that the difference of two - but now I'm confused, why do you only have the L3>= term on the >5 criteria?

=(SUMPRODUCT(--('Aug 05'!G2:G341=A8),--('Aug 05'!C2:C341>=1)),--('Aug 05'!C2:C341<=5),--(L3>='Aug 05'!A2:A341))
+
(SUMPRODUCT(--('Aug 06'!G2:G341=A8),--('Aug 06'!C2:C341>=1),--('Aug 06'!C2:C341<=5),--(L3>='Aug 06'!A2:A341))

[semper_si]

Thanks sooo much! I'll play around with the forumlae tonight, and that $ may just save me from anymore headaches.

I think the reason the L3>= term looks like it only covers the >5 range is because I perhaps naievley believed that by surrounding:

SUMPRODUCT(--('Aug 05'!G2:G341=A8),--('Aug 05'!C2:C341>=1))-SUMPRODUCT(--('Aug 05'!G2:G341=A8),--('Aug 05'!C2:C341>5))

with brackets:

((SUMPRODUCT(--('Aug 05'!G2:G341=A8),--('Aug 05'!C2:C341>=1))-SUMPRODUCT(--('Aug 05'!G2:G341=A8),--('Aug 05'!C2:C341>5)))

then the SUMPRODUCT formula for the date range would enact on all within the first set of brackets, hence:

=(SUMPRODUCT(--('Aug 05'!G2:G341=A8),--('Aug 05'!C2:C341>=1))-SUMPRODUCT(--('Aug 05'!G2:G341=A8),--('Aug 05'!C2:C341>5)))*((L3>='Aug 05'!A2:A341)*(H3<='Aug 05'!A2:A341))

In any case, your suggestion looks far more elegant. I think I need to go and read more about what SUMPRODUCT does