 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006Using Multiple INDEX & MATCH????
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 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.  SignatureRegards Roger Govier > Hi, > [quoted text clipped - 12 lines] > > 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 > Hi Don > [quoted text clipped - 32 lines] >> >> DonH 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? SignatureRegards Roger Govier > Roger, > [quoted text clipped - 55 lines] >>> >>> 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 > Hi Don >> The 11 tables exist so admin can enter and view the employees shifts in a [quoted text clipped - 69 lines] >>>> >>>> DonH 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. SignatureRegards Roger Govier > Roger, > [quoted text clipped - 25 lines] >> 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?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.