I have a report that I use one spreadsheet that tracks all activity of interview candidates. Management wants me to give them specific data from this report. I need to create away that I can pull separate data for Offers extended, offers accepted and start date. Each report needs to show the name, position and state.
I thought a lookup function would would and just have separate worksheets but I couldn't get that to work.
Here is your file back with formulae on each worksheet to retrieve the data required for that worksheet. The formula is as follows that is entered in A2 of each worksheet and filled down and across. The formula is an array formula which is entered with Ctrl + Shift + Enter:
Formula:
Please Login or Register to view this content.
The part of the formula in red is changed to use the indicated column as the key to retrieve the data for that worksheet.
Last edited by newdoverman; 02-19-2015 at 03:29 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
see the attached file
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thank you. How do I pull if they just want activity for the month?
Personally, I wouldn't use all the worksheets that you have and would instead use filters on the Tracking worksheet to extract the data required. Seeing that both nflsales and I have both extracted data to the individual worksheets, click in the data then click on the Data tab and click on Filter. Do this on all the worksheets and you will be able to filter by month and other criteria as required.
Last edited by newdoverman; 02-19-2015 at 04:23 PM.
Thank you for your help. I will try the filter but Why can't I get the separate worksheets to pull all of the data? (trying to learn as we do this)
On the Tracking worksheet, delete the blank row 2. Then, click in the data, click on the Data tab, click on the Filter button and you will be able to filter on the entire dataset. This will allow you to filter on any of the columns, sort the data etc.
I understand the filter but on the separate worksheet tabs it is only pulling to row 50. How do I get each worksheet to pull from the entire tracking sheet.
This is the basic formula that is on all the worksheets except the Tracking worksheet
=IFERROR(INDEX(Tracking!A$3:A$50,SMALL(IF(Tracking!$H$3:$H$50<>"",ROW(Tracking!$H$3:$H$50)-MIN(ROW(Tracking!$H$3:$H$50))+1),ROWS($1:1))),"")
Change the 50s (marked in red) to a much higher number to suit your data requirements. Do this in A2 on each of the worksheets, enter with Ctrl + Shift + Enter and fill down and across.
I don't know how much data you have and expect so the value that you replace the 50s with should be sufficiently large to handle expected data volume.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks