SMALL Function across multiple date ranges

[Kingsof82]

Hi,

I'm looking to find the second lowest date entered in ranges from multiple worksheets. I can return the second lowest from each range individually, e.g.

=SMALL(Observation!I16:BF16,2)

However, attempting to pull the second lowest from all ranges using the following returns a value error:

=SMALL((Probation!J13:U13, Appraisal!J13:O13,Supervision!I13:CB13,Observation!I13:BF13),2)

Any help is greatly appreciated, thanks

 

[Domenic]

Thanks,

Unfortunately this is now returning a number error

There should be at least two numerical values within those ranges. Is this the case?

 

[Kingsof82]

There should be at least two numerical values within those ranges. Is this the case?

They're date ranges, and not all of the ranges contain two dates on each row, but the number error persists when they do.

 

[Peter_SSs]

Perhaps the dates are text dates rather than numerical dates?

Could you give us the 4 relevant ranges with XL2BB so that we have some realistic sample data to test with instead of us just guessing what data we are dealing with?
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[Kingsof82]

Perhaps the dates are text dates rather than numerical dates?

Could you give us the 4 relevant ranges with XL2BB so that we have some realistic sample data to test with instead of us just guessing what data we are dealing with?
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Thanks again, especially for your patience. I've only converted the column headers and top rows into the following mini sheets:

There are four sources ranges:

Probation tab:

Supervision Tracking Document Option 2.xlsm
	J	K	L	M	N	O	P	Q	R	S	T	U
14	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12
15	06/10/2024	04/11/2024
Probation

Appraisal tab:

Supervision Tracking Document Option 2.xlsm
	J	K	L	M	N	O
14	2023	2024	2025	2026	2027	2028
15
Appraisal

Supervision tab:

Supervision Tracking Document Option 2.xlsm
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB
14	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-26	Feb-26	Mar-26	Apr-26	May-26	Jun-26	Jul-26	Aug-26	Sep-26	Oct-26	Nov-26	Dec-26	Jan-27	Feb-27	Mar-27	Apr-27	May-272	Jun-27	Jul-27	Aug-27	Sep-27	Oct-27	Nov-27	Dec-27	Jan-28	Feb-28	Mar-28	Apr-28	May-28	Jun-28	Jul-28	Aug-28	Sep-28	Oct-28	Nov-28	Dec-28
15
Supervision

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AF15:AF30	Expression	=AF15>EDATE(MAX($I15:AE15),3)+7	text	NO
AE15:AE30	Expression	=AE15>EDATE(MAX($I15:AD15),3)+7	text	NO
AD15:AD30	Expression	=AD15>EDATE(MAX($I15:AC15),3)+7	text	NO
AC15:AC30	Expression	=AC15>EDATE(MAX($I15:AB15),3)+7	text	NO
AB15:AB30	Expression	=AB15>EDATE(MAX($I15:AA15),3)+7	text	NO
AA15:AA30	Expression	=AA15>EDATE(MAX($I15:Z15),3)+7	text	NO
Z15:Z30	Expression	=Z15>EDATE(MAX($I15:Y15),3)+7	text	NO
Y15:Y30	Expression	=Y15>EDATE(MAX($I15:X15),3)+7	text	NO
X15:X30	Expression	=X15>EDATE(MAX($I15:W15),3)+7	text	NO
W15:W30	Expression	=W15>EDATE(MAX($I15:V15),3)+7	text	NO
V15:V30	Expression	=V15>EDATE(MAX($I15:U15),3)+7	text	NO
T15:T30	Expression	=T15>EDATE(MAX($I15:S15),3)+7	text	NO
U15:U30	Expression	=U15>EDATE(MAX($I15:T15),3)+7	text	NO
S15:S30	Expression	=S15>EDATE(MAX($I15:R15),3)+7	text	NO
R15:R30	Expression	=R15>EDATE(MAX($I15:Q15),3)+7	text	NO
Q15:Q30	Expression	=Q15>EDATE(MAX($I15:P15),3)+7	text	NO
P15:P30	Expression	=P15>EDATE(MAX($I15:O15),3)+7	text	NO
O15:O30	Expression	=O15>EDATE(MAX($I15:N15),3)+7	text	NO
N15:N30	Expression	=N15>EDATE(MAX($I15:M15),3)+7	text	NO
M15:M30,AG15:CB30	Expression	=M15>EDATE(MAX($I15:L15),3)+7	text	NO
L15:L30	Expression	=L15>EDATE(MAX($I$18:$K$18),3)+7	text	NO

Observation tab:

Supervision Tracking Document Option 2.xlsm
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF
14	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-26	Feb-26	Mar-26	Apr-26	May-26	Jun-26	Jul-26	Aug-26	Sep-26	Oct-26	Nov-26	Dec-26	Jan-27	Feb-27	Mar-27	Apr-27	May-272	Jun-27	Jul-27	Aug-27	Sep-27	Oct-27	Nov-27	Dec-27	Jan-28	Feb-28	Mar-28	Apr-28	May-28	Jun-28	Jul-28	Aug-28	Sep-28	Oct-28	Nov-28	Dec-28
15
Observation

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I15:I30	Expression	=I15>EDATE(MAX(#REF!),3)+7	text	NO
J15:BF30	Expression	=J15>EDATE(MAX($I15:I15),3)+7	text	NO

And this is the destination table, where I'm trying to amalgamate the data from corresponding rows of the the above tabs chronologically. You'll see I've used the MIN function in the first column, but it's when trying to pull through the next lowest and so on that I'm having issues :

Supervision Tracking Document Option 2.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
4	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18	Column19	Column20	Column21	Column22	Column23	Column24	Column25	Column26	Column27	Column28	Column29	Column30
5	06/10/2024	#NUM!	#VALUE!
All - Chronological

Cell Formulas
Range	Formula
G5	G5	=MIN(Probation!J15:U15,Appraisal!J15:O15,Supervision!I15:CB15,Observation!I15:BF15)
H5	H5	=AGGREGATE(15,6,CHOOSE({1;2;3;4},--(Probation!J11:U11&""),--(Appraisal!J11:O11&""),--(Supervision!I11:CB11&""),--(Observation!I11:BF11&"")),2)
I5	I5	=MIN(IF((Probation!J15:U15,Appraisal!J15:O15,Supervision!I15:CB15,Observation!I15:BF16)>[@Column1],))

 

[Peter_SSs]

Your formula in H5 of the final sheet is pointing at row 11 in all the other sheets but the data you provided in the other sheets is in row 15.
If I change that formula to point at row 15 in each sheet the formula returns 4/11/2024 for me.

 

[Kingsof82]

Your formula in H5 of the final sheet is pointing at row 11 in all the other sheets but the data you provided in the other sheets is in row 15.
If I change that formula to point at row 15 in each sheet the formula returns 4/11/2024 for me.

If you're getting 4/11/24 on your end then it's working for you, as a manual check sees that as the second earliest date.

For some reason, changing the 11's to 15's still results in this for me:

Supervision Tracking Document Option 2.xlsm
	G	H
4	Column1	Column2
5	06/10/2024	#NUM!
All - Chronological

Cell Formulas
Range	Formula
G5	G5	=MIN(Probation!J15:U15,Appraisal!J15:O15,Supervision!I15:CB15,Observation!I15:BF15)
H5	H5	=AGGREGATE(15,6,CHOOSE({1;2;3;4},--(Probation!J15:U15&""),--(Appraisal!J15:O15&""),--(Supervision!I15:CB15&""),--(Observation!I15:BF15&"")),2)

 

[Peter_SSs]

Could you upload a sample file that just has the data shown in the mini sheets above and with your existing formula in H5 of 'All - Chronological' to DropBox or OneDrive or Google Drive etc and provide a public shared link to that file here? That way I can take a look at what might be different about your file & mine.

 

[Kingsof82]

Could you upload a sample file that just has the data shown in the mini sheets above and with your existing formula in H5 of 'All - Chronological' to DropBox or OneDrive or Google Drive etc and provide a public shared link to that file here? That way I can take a look at what might be different about your file & mine.

Thanks, the systems at my workplace are finally in the long process of being updated so I had to do it through my personal dropbox, hopefully this is suitable:

Dropbox

www.dropbox.com

 

[Peter_SSs]

Thanks for the file.
The suggested formula was
=AGGREGATE(15,6,CHOOSE({1;2;3;4},--(Probation!J15:U15&""),--(Appraisal!J15:O15&""),--(Supervision!I15:CB15&""),--(Observation!I15:BF15&"")),2)

Your actual formula in H5 when I open the workbook is this (though it looks right in XL2BB)
=AGGREGATE(15,6,CHOOSE({1;2;3;4},--(@Probation!J15:U15&""),--(@Appraisal!J15:O15&""),--(@Supervision!I15:CB15&""),--(@Observation!I15:BF15&"")),2)

 

[Kingsof82]

Thanks for the file.
The suggested formula was
=AGGREGATE(15,6,CHOOSE({1;2;3;4},--(Probation!J15:U15&""),--(Appraisal!J15:O15&""),--(Supervision!I15:CB15&""),--(Observation!I15:BF15&"")),2)

Your actual formula in H5 when I open the workbook is this (though it looks right in XL2BB)
=AGGREGATE(15,6,CHOOSE({1;2;3;4},--(@Probation!J15:U15&""),--(@Appraisal!J15:O15&""),--(@Supervision!I15:CB15&""),--(@Observation!I15:BF15&"")),2)

Thanks, the formula on the file at my end was the same as that suggested above, so I'm not sure where the '@' came from. I've since tried copying the above and pasting it in again, but keep getting the same issue. Could it be to do with the archaic version of excel I'm running?