lookup one value over multiple sheets & return multiple responses

**rsagar-patt** · 11-25-2012, 06:30 AM

hi,

this is my first time asking a question on this forum (or any forum), below are the details of my problem

we are currently trying to create a scheduler for a complimentary service the company has just started for its employees, because its free, it needs to limited too.

workbook-1 names Appointment summary i have 7 columns
Column A - Dates
Col B - Availability (this is have formulated)
Col C-G we need the user ID's to appear from another workbook based on services required

workbook-2 complete schedule - a separate excel sheet gives the following details (i'll keep it as brief as possible)
Col A-C are employee details (column A of this sheet is what i need automated on Workbook-1 C-G)
Col D-H is vehicle details
Col I-O is service requested
Col P is the date of service (this date is calculated for Workbook Col B for slots availability)

since we can only offer service for maximum 5 employees per day, the workbook-1 gives a summary of who has booked for that date

my query:
i want Workbook-1 Col C-G employee details to be automated with reference to Workbook-2 employee details
it has to specifically tell me which employee has booked the services for that particular date

i was unable to successfully use a pivot table as it restricts me from formulating the availability then - if there is a way i can further formulate the pivot table, that would be the easiest.

i have attached a sample of the document for your reference (its just a sample, so there are no details on it)

on a separate note, can i ask excel to give the user an error once a date has been requested for 5 times? i need this error to appear in Workbook-2

**dilipandey** · 12-01-2012, 06:34 AM

HI rsagar,

Welcome to the forum.

I have checked that there are 89 views for this post.. but no replies.. suggest you to revisit the thread - may be post a single workbook with required sheets included. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**JBeaucaire** · 12-01-2012, 11:30 AM

There needs to be a helpful code in your Schedule.xlsx file to make looking names/appts sequentially easier.

1) Open both workbooks

2) In Car Services Schedule.xlsx put this formula in S4, then copy down a ways, far enough to cover your needs:
=P4&"."&COUNTIF($P$4:$P4,$P4)

This formula creates an index and numbers the date with how many entries for that date exist already.

3) In the Appointment Sheet.xlsx put this formula in C2, then copy to the right and down as needed:
=IF(COLUMN(A1)<=(5+$B2), INDEX('[Car Services Schedule.xlsx]Sheet1'!$A:$A,MATCH($A2&"."&COLUMN(A1),'[Car Services Schedule.xlsx]Sheet1'!$S:$S,0)), "")

4) Save the Car Services Schedule.xlsx and close it, now the Appointment workbook will update the formula to include the full path and should continue to work.