Hi i have a list of names, dates and times. The dates and times for each session are identical i.e 1 pm each day and four customers per day 9equates to the same date being used four times).
I need to select a montha and list the names of the clients that match the criteria, ie date and time in sequence.
Thanks in advance
Please see the attached sheet for details.
Does a pivot table accomplish what you are looking for?
Please click the * icon below if I have helped.
Hi it needs to be in workbook format with formulas as I have 4 sections per month to fill with names depending on the date.
Cheers
Will the dates all be within the same year?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
The user has the choice from a drop down list as to what year they will be using
Try this...
All formulas are array formulas**.
Entered in A4:
=IFERROR(INDEX(Data!C:C,SMALL(IF(TEXT(Data!$B$2:$B$9,"mmmmyyyy")=C$1&D$1,ROW(Data!C$2:C$9)),ROWS(A$4:A4))),"")
Entered in B4:
=IFERROR(INDEX(Data!A:A,SMALL(IF(TEXT(Data!$B$2:$B$9,"mmmmyyyy")=C$1&D$1,ROW(Data!A$2:A$9)),ROWS(A$4:A4))),"")
Entered in C4:
=IFERROR(INDEX(Data!B:B,SMALL(IF(TEXT(Data!$B$2:$B$9,"mmmmyyyy")=C$1&D$1,ROW(Data!B$2:B$9)),ROWS(A$4:A4))),"")
Format as Date
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Select A4:C4 and copy down until you get blanks.
I can't get it to work - the array formula just brings up blank cells and dosen't list the four names for each month only the 3rd name of each list. Any ideas?
Cheers
JD
Hi just to amend the last post I have got the formula working, I need a second criteria adding if possible please, I need the formula to look at another column and if it displays the number 1 in the cell then to take that into consideration so the lookup criteria would be month, year and the number 1.
Cheers
JD
Last edited by john dalton; 10-08-2013 at 05:34 AM. Reason: spelling
Post the formula you are using.
What column do we need to look in for the number 1?
Hi the type list is in cloumn a of the data entry sheet, I have attached the updated sheet for viewing.
I used the formula you created Tony.
JD
Last edited by john dalton; 10-10-2013 at 08:19 AM. Reason: added text
Try this...
On the Appointments Book sheet...
C1 = some month
D1 = some year
E1 = some type
Array formula** entered in A4:
=IFERROR(INDEX(Data!D:D,SMALL(IF(TEXT(Data!C$2:C$9,"mmmmyyyy")=C$1&D$1,IF(Data!A$2:A$9=E$1,ROW(Data!A$2:A$9))),ROWS(A$4:A4))),"")
Array formula** entered in B4:
=IFERROR(INDEX(Data!B:B,SMALL(IF(TEXT(Data!C$2:C$9,"mmmmyyyy")=C$1&D$1,IF(Data!A$2:A$9=E$1,ROW(Data!A$2:A$9))),ROWS(A$4:A4))),"")
Array formula** entered in C4:
=IFERROR(INDEX(Data!C:C,SMALL(IF(TEXT(Data!C$2:C$9,"mmmmyyyy")=C$1&D$1,IF(Data!A$2:A$9=E$1,ROW(Data!A$2:A$9))),ROWS(A$4:A4))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Select A4:C4 and copy down until you get blanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks