Hi all,
I have recently made 4 relatively basic 'call trackers' for a campaign at work. Allows the temps to easily make calls and register each record in a simple way whilst keeping it all in excel.
This is all great and working fine, I also created a data link to a separate excel doc to analyse the data, so each sheet brings in the table that contains all the live-tracking raw data from each all they make (on their separate tracker documents).
One of the things I had on my old 1-person tracker was a 'master' doc of all the companies a person would ring (Imagine they have to call e.g. 100 companies but they may end up speaking to some of them multiple times, so the master document took the most recent call and showed that info so we could see the final result of each company easily without searching for the latest record within the raw data).
I did this using the formula (I have deleted the file name and just put an example)
Code:
IFERROR(LOOKUP(7,1/('FILENAME'!TblMain[Company]=[@Company]),'FILENAME'!TblMain[Disposition]),"")
The problem I am having is that more than 1 person could at some point call the same company, and I want the most recent record (between the sheets that are each pulling the datasource into a table. I found this formula online and tested it out to check vlookups between sheets
Code:
=VLOOKUP(A1,INDIRECT("'"&INDEX(Sheet_List,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheet_List&"'!$D$1:$E$40"),A1)>0),0))&"'!$D$1:$E$40"),2,FALSE)
This worked (as an array formula) however I am not sure how I would translate this into the lookup (aka for the FINAL record of that company, not the first) but also even if I did - would a new record on 1 sheet change the value or would only adding a record to the final sheet within the array do that?
Apologies if this doesn't make sense.
To put what I am trying to do simply - I want to pull 4 datasources into a sheet each which will be being updated regularly throughout the day. I want to be able to create 1 big table which essentially does a vlookup for the most recent record for the value being looked up. So somebody could go in and see this summary at any point in the day whilst the 4 users are updating their own tables. I have added an example workbook with 1 table each in 4 sheets and what I imagine the 'master' summary page looks like.
Super appreciative in advance if anyone can help! Feel like I may have explained it terribly but fingers crossed.. thank you!