Looking to create a list of appointments that match a time and date

[john dalton]

Hi I have a workbook that requires the user to enter data for an appointment calendar.

I need a formula that will list all the appointmets that are entered for a seleted date and time.

Please see the attached workbook with an example of what is required.

Cheers

JD

[Pete_UK]

Put this formula in H4:

=IF(AND(E4=$B$1,F4=$B$2),MAX(H$3:H3)+1,"")

and copy down to beyond the bottom of your data (to allow for expansion). Then you can use this formula in A5:

=IFERROR(INDEX(D:D,MATCH(ROWS($1:1),H:H,0)),"")

and this one in B5:

=IFERROR(INDEX(G:G,MATCH(ROWS($1:1),H:H,0)),"")

then copy both these down as far as you think you might need them. Then just change the entries in B1 and B2 to suit your requirements.

Hope this helps.

Pete

[john dalton]

Great result, I f I want to exstend this so that the result chart displays monday to frday of each week and 10am until 5pm are displayed how would I change the formulas to suit.

Please see attached sheet for further details.

Sorry for the update just been asked If a full week can be displayed insted of invidual days.

Cheers

JD

[AZ-XL]

Hi
This is for Name
=IFERROR(INDEX($D$4:$D$8,SMALL(IF(($B$1=$E$4:$E$8)*($B$2=$F$4:$F$8),ROW($E$4:$E$8)-3,""),ROW()-4)),"")

Requirement
=IFERROR(INDEX($G$4:$G$8,SMALL(IF(($B$1=$E$4:$E$8)*($B$2=$F$4:$F$8),ROW($E$4:$E$8)-3,""),ROW()-4)),"")

They both are array. Hit ctrl+shift+enter after pasting

[Pete_UK]

@John

I think you have just posted the same workbook - is it meant to show the extended requirements?

Pete

[john dalton]

Sorry attached the wrong workbook here you go.

[Pete_UK]

So, will B1 always be a Monday?

Do you want Monday to Friday going across (like a calendar), and then the times going down, with sufficient rows to accommodate a number of entries for each time (e.g. 5) ?

Pete

[john dalton]

Yeah cheers Pete

[Pete_UK]

The attached file does what you asked for. I've put comments in blue, but it should be fairly self-explanatory - put your data in the List sheet, then use the Weekly_summary sheet to see the data in the form you requested by putting a date in the yellow cell. You can use the filter in O2 to hide empty rows and thus bunch the data up.

Hope this helps.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, as I've helped you before, a click on the "star" icon would be appreciated.

Pete

By the way, times are rounded to the nearest hour so if you put in 10:15:00 it will be treated as 10:00:00.

[john dalton]

Hi Pete just a quick question, how do I take the nearest hour calc out of the formula as I have half hour slots as well and may extend this to every fifteen minute slots in the future.

Thanks for all your help it works very well.

JS

[Pete_UK]

It is the MROUND(cell,1/24) function which rounds the times to the nearest hour. This is used in column A of the List sheet and in columns C to L of the summary sheet. It is probably easiest to leave it in and do a Find & Replace (CTRL H) to change the 1/24 parameter (as there are 24 hours in a day) to 1/48 (for half-hour) or 1/96 (for quarter-hour) increments in both sheets.

Thanks for the rep, by the way.

Hope this helps.

Pete

[john dalton]

Learning all the time mate but its slow progress.

Cheers

JD