Find and Return All Values for Selected Date

[stupidname]

I'm currently in the progress of creating a spreadsheet but stuck and need some help please.


I need to be able to find and return all values for the selected month and handle errors if the formula don't return any values.

To sanity check what is returned, i will need a formula to count all values for the selected month.


Example:


Sheet 1

Contains all the data

A1: Start Date

B1: End Date

C1: Number of days

D1: Total


Sheet 2

Summarise data

A1 - Drop Down list of Months (Validation List)

Formatted: mmmm yy


B2 - {formula to count all values for the selected Month}

A3: Start Date

B3: End Date

C3: Number of days

D3: Total


A4 - D4: Would contain a formula to return all the values for the Selected Date (Decemeber 12) from A1


If i've missed anything let me know.


Thanks in advance.

 

[kaushik03]

Hi stupidname,


can u plz upload a workbook with sample data?


Kaushik

 

[stupidname]

That didn't work.


Try this site instead:

http://www.freeuploadsite.com/do.php?id=1201


Thanks

 

[Hui]

Stupidname


You may want to have a read of this post which does what I think you need

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


You may also want to tell us your name as I would much rather put your name at the top of my post than your pseudonym, "Stupidname"

 

[stupidname]

Hi Hui, my name is Stu :D


Thanks for the reply.


I managed to work it out.

So, i used the following formula:


Return multiple records

http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/


=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))


I had to add column which converted the date to the following format: =text(b2, "mmmm yy"), which i used as the "lookup" value.


If there is a better way, im open to ideas - however im happy with it right now.


Cheers


Stu