Using Multiple INDEX & MATCH????

[DonH]

Hi,

Hope you can help! I have 11 employee tables which cover their shifts for
the year. I would like to create an overview table of a given day showing
who is working.

I have used INDEX & MATCH before to retrieve the information I need from a
single table (searching by date in the header and their works number in the
left most column) but I am at a loss as to how to look through each of 11
date headers and works number columns.

All help appreciated!

Many thanks

DonH

 

[Roger Govier]

Hi Don

Why is the data in 11 Tables?
Could you not combine all the data into one table?
Let's suppose you have 3 shifts (alter number to suit).
If you had all your data set out as

Date Name Shift No Shift1 Shift2 Shift3

All you would need to have entered is, Date, Name and Shift No. for all
dates for all employees for the year.
In cell D2 enter
=IF($C2=COLUMN(A:A),$B2,"")
and copy across through cells E2:F2
Copy D2:F2 down for as many rows as you have data.

Mark the header row and choose Data>Filter>Autofilter and use the
dropdown on Date to select any day you want and you will see a list of
all employees working that day and which shift they are on.

 

[DonH]

Roger,

Many thanks for your reply. Although your suggestion is close I don't think
(on first read) it achieves what I'm after.

The 11 tables exist so admin can enter and view the employees shifts in a
monthly grid. Within each of these 11 tables are 5 groups of 10 employees
who each work a different shift, counting of the totals and flagging up of
shortfalls in skill base all take place in each monthly sheet so they are
easily seen as sickness or shift change are entered. As with most
organisations I cant bank on the employees being employed in the same shift
group from month to month.

My aim for this part of the project is to automatically provide a table of
10 names of employees who are on duty (these could be made up from employees
from any shift who have exchanged duty/worked overtime) and will then
combine to show their skills so admin can quickly see who to turn to in any
given circumstance on a given day.

Many thanks

DonH

 

[Roger Govier]

Hi Don

The 11 tables exist so admin can enter and view the employees shifts
in a monthly grid

Do you have a 1 month closedown each year then ?

The fact that there were 11, made me think they were not organised by
date.
Any given date you require, will therefore only exist on any one of
these tables.
Since you say you can achieve what you require from a single table, with
Index and Match, is it not just a question of a formula to convert the
date you require into a table name with the use of Vlookup, and then use
that table name in your Index Match formula?

 

[DonH]

Roger,

Thanks again! Yes the 11 tables is confusing they work with odd weeks.

I think you are right, but was in my thinking limited by the number of
nested If's allowed. However your reply to this and my other post has just
given me the idea of using more than 1 cell for the if formulas and another
to provide me with my cell ref. Not sure if it will work but I will give it
a go.

Before I go though can I take it I cant build multiple INDEX & MATCH
formulas ?

Many thanks

DonH

 

[Roger Govier]

Hi Don

Before I go though can I take it I cant build multiple INDEX & MATCH
formulas ?

I'm not sure what you mean here.
You could use Index Match on one table, to provide a value to be Matched
(and used with Index) in another table.

If you get stuck with your attempts, you can email me a copy of the
workbook and I will take a look for you.
Remove NOSPAM from my address to send direct.