Summary of Multiple Date Ranges

L

Laura

I have 5 worksheet tabs within the same spreadsheet. Each
tab has two columns with a beginning date (col A) and
ending date (col B). I.e.

Beg Date End Date Amt
11/24/03 1/14/04 $100
1/15/04 7/14/04 $150

etc.

The beginning and end dates are not the same for each tab.

I would like to create a summary that goes through all the
beginning dates and identifies the smallest date, the next
smallest date etc. to create one summary of all the dates.

MIN and MINA indentify the first smallest date, however, I
am unable to identify the next smallest date.

Is this possible? Thank you.
 
A

A.W.J. Ales

Laura,

If your data is in A1:A25, you can use
SMALL($A$1:$A$25, 2) to find the second smallest value. Bij changing the
value of 2 you can find 3th; 4th etc. smallest values.
SMALL($A$1:$A$25,1) is equivalent to MIN($A$1:$A:$25)



--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
L

Laura

I think SMALL may be the way to go, however, my data is in
('SHEET1'!A1:A25,'SHEET2'!A1:A25,'SHEET3'!A1:A25,'SHEET4'!
A1:A25,'SHEET5'!A1:A25). SMALL
 
L

Laura

-----Original Message-----
Laura,

If your data is in A1:A25, you can use
SMALL($A$1:$A$25, 2) to find the second smallest value. Bij changing the
value of 2 you can find 3th; 4th etc. smallest values.
SMALL($A$1:$A$25,1) is equivalent to MIN($A$1:$A:$25)



--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *




.
It seems SMALL may be the best route to pursue. The data
Click to expand...
is in ('Sheet1'!A1:A25,'Sheet2'!A1:A25,'Sheet3'!
A1:A25,'Sheet4'!A1:A25,'Sheet5'!A1:A25). When use SMALL
with this data, I receive #VALUE! which I am assuming is
Excel saying it doesn't like the multiple worksheets?

Is there some other means to accomplish?

Thank you!
 
A

A.W.J. Ales

Isn't it possible to copy the various ranges to one sheet and to then do a
simple sort of it.

If that isn't possible ( for instance because you regularly have to update
your data in the 5 "basic" sheets, I think you will need a macro to do what
you want).

I don't see how you can do what you want to do simply by "normal" Excel
functions.

The macro should have to loop through your sheets, copy the dataranges and
paste them onto your summarysheet.
After that it would sort the total data range on your summarysheet.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Top