Forum Discussion
Issue finding the proper formula or macro
I have an Excel spreadsheet with multiple worksheets (tabs) of data. I am attempting to build a dashboard on the 1st tab where I can have a formula search for the data that is located somewhere on the other tabs and populate the field.
For example:
1st tab of my spreadsheet will list the NAME in column A, their ID# in column B, and their JOB TITLE in column C. In Columns D, E, and F will have the headers titled CLASS A, CLASS B, and CLASS C. These are the cells I want to have formulas that will search the other tabs for the date each of the classes were completed.
NAME ID# JOB TITLE Class A B C
| Doe, John | 12345 | Supervisor | |||
| Jones, Mike | 54321 | Driver | |||
| South, Robert | 33557 | Driver |
2nd worksheet is titled Class A Results. This will include the same column headers Name, ID#, Job Title from the 1st worksheet but will have completion dates for Class A located in column D of this worksheet.
3rd worksheet is titled Class B Results. This will include the same columns A, B, and C from the 1st worksheet but will have the completion dates for Class B in column D of this worksheet.
4th worksheet is titled Class C Results. This will include the same columns A, B, and C from the 1st worksheet but will have the completion dates for Class C in column D.
On the 1st worksheet (dashboard) in column D, I need a formula that would only search the 2nd worksheet (Class A) to find the specific person and populate the date that is stored for that person in column D of the 2nd worksheet. In column E, I need a formula that would only search the 3rd worksheet (Class B) to find the specific person and populate the date that is stored for that person in column D of the 3rd worksheet. In column F, I need a formula that would only search the 4th worksheet (Class C) to find the specific person and populate the date that is stored for that person in column D of the 4th worksheet.
Hi Roland,
You could do this with 'index match'
There is a tutorial here that explains how it works:
https://www.deskbright.com/excel/using-index-match/
Although if you google 'index match' then loads of tutorials will come up.
I've attached an example of it working, I've put everything on the one worksheet to make it easier to see how it working.
7 Replies
Hi Roland,
You could do this with 'index match'
There is a tutorial here that explains how it works:
https://www.deskbright.com/excel/using-index-match/
Although if you google 'index match' then loads of tutorials will come up.
I've attached an example of it working, I've put everything on the one worksheet to make it easier to see how it working.
Another question expanding on this. I would like to be able to sort the columns and maintain the data in the column....if this is possible. While this is working successfully, I noticed that after the formula is in the cell and if I sort a column, the data changes because the formula has a specific Excel cell built into it. As you can see below, the formula is using cell B16 as the source of the MATCH. When I sort a column, the formula does not change and it is looking for whatever data that is in cell B16 after the column was sorted.
This is the actual formula that I am successfully using in Excel from your guidance =INDEX(EVULN405!I:I,MATCH(FLD_Employees!B16,EVULN405!B:B,0))
Cells in column B contain a person's unique employee ID# and column A contains that person's name.
Can this INDEX MATCH formula be altered to replace B16 (which would also affect the B:B in the formula) with an identifier that will stay with the person if the column is sorted?
While Index and Match are great if you have complex data, I would use VLOOKUP with the way you have yours set up. I have also come to appreciate the use of tables for data, rather than just placing the data in the worksheet. Tables make formula reference a lot easier. In the attached document, I placed the information you gave us on sheet 1 or "Rollup" as I've called it, and in a table that I've named "RollupTbl". There are separate worksheets for Class A Results, etc, and the data on each sheet is stored in a table as well, ClassATbl, etc. I use the Iferror first in case the name doesn't have a match (blank, as in my example, or different spelling, etc.), and the If statement next to handle blank fields (without it, the formula was returning 0 for blanks, which is 1/1/1900 if the cell is formatted as a date). To add a new line to the table, just put your cursor in the bottom right cell and hit tab
This was great!!! It didn't work for me originally because I didn't realize that I needed to format the cells for the date. Once I formatted for the date, it populated accurately! WOW!!!
