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)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 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!

Attached Files

test.xlsx (13.6 KB, 5 views)

Maybe like shown in the attached.

Attached Files

3D_Lookup.xlsx (17.4 KB, 7 views)

Thank you for doing this!
Hmm this seems to be only picking up the latest record in the first sheet? When I then add another one for company A in the other later sheets it doesn't update it. I want it to be able to pick up the most recent record for that company name within the whole workbook essentially.

You are right.
Forget about that workbook.

Ahh. Do you think what I want is impossible?

I wish I could get the information to update live into 1 table. The real spreadsheets have userforms to add records, so a new line is made per record. Would be great for that line to auto update to a table on a separate workbook (without VBA So the user doesn't need to have the other workbook open), and as each user adds a record it adds to the same table in order... But I can't see how that is possible

I don't know if it is possible.l

If you can accept helper cells like in sheets 1:4, H3:I28 and sheet Dashboard H2:H5 then you might want to take a look at the attached workbook. Helper cells can be hided or moved to somewhere out of sight if you want.

Attached Files

3D_Lookup_3.xlsx (20.6 KB, 4 views)

This works great thank you!!

In the document I will actually be using - each sheet will have about 8 columns and for the latest record of each company I would like to bring back all columns associated with the company.

I added an extra column on sheet 1,2,3,4 but when I try to bring the most recent one back onto the dashboard it just comes back with '0' (obviously I am doing it wrong!!).

Do you know how I could add extra columns and bring them all back as well as the date?

Again thank you soo much for this I have been so stuck.

Please upload a workbook with full data set.

I actually can't post the real data as it's confidential but I have added more of the sample columns which match those I will be using in the doc.

It's for a telesales call tracker type thing. I have 4 userforms which submit call records and those records will be linked into this document (the table will be linked and set to refresh data every 5 mins or so) so this dashboard will be used to keep an overview of all of the companies they will ring without needing to see all records for them.

Attached Files

3D_Lookup_2 added columns.xlsx (23.4 KB, 5 views)

That's really quite a complication.
Will see, but not at all sure if I can come up with anything useful.

How were you able to do the date? Was it based literally on most recent date (and therefore not work for the other columns?).

The other option I was thinking is for each record to have a unique call ID and if the recent date is for that record then do a lookup using the ID. But not sure how I would bring the ID in to perform the v lookup.

Other option I was thinking was to then do a vlookup and be required to match the company name and date (and possibly time if it would make it more accurate as I will have a timestamp for each record?)

I regret, but do not expect a solution from me.

I tried once more without having tested.

Take a look and see if it looks correct.

Attached Files

3D_Lookup_4.xlsx (37.1 KB, 6 views)

Oh!! It works! Wow I am looking at that formula... complex.

Really good stuff thanks so much for the help