Collect the most recent Data from 2 sheets

naeem.CHG

Hi All,

Here is the scenario, I have a master list of clients with the pertinent data for our business partners to reference. How ever we have an update sheet that has been built to request changes to that master list if needed. I would like to have data update in my row of the master list once there is an update from the request sheet. So my data would be in the Master list and only change when there is a request submitted from the request sheet.

I have used the following formula to find the latest date.

=MAX(COLLECT({Update Data Test Range 1}, {Update Data Test Range 3}, Client@row))

I am trying to figure out how to create and If statement that says give me the new data from the update sheet.

For example:

Master list has an "Client column" and cell value is "BMW" and "Account Manager" column is "John Doe". In another sheet (update request sheet) I have a new entry for the Client "BMW where the Account Manager has been updated to "Jane Doe".

I would like the Master list via if statement to bring in the new value "Jane Doe"

Any help would be great!

Thanks

Naeem

Genevieve P.

Hi @naeem.CHG

If I'm understanding you correctly, you need a way to bring in the Account Manager name into this sheet based on two criteria: the Date in the Max Date column and the Client.

Since you already have the MAX date and the Client figured out, all you need to do is use an INDEX(COLLECT formula to find the matching Account Manager!

Try this:

=INDEX(COLLECT({Account Manager Column}, {Date Column other Sheet}, [Max Date]@row, {Client Column other Sheet}, Client@row), 1)

Let me know if this makes sense and works for you.

Cheers,

Genevieve

naeem.CHG

Thank you, I may have had a confusing message. I would like to bring in the most up to date Account manager based on the latest updated cell from by referencing the two sheets and populating Account manager in the most updated cell.

Genevieve P.

Hi @naeem.CHG

Would you be able to post screen captures of both sheets, but block out sensitive data?