Client data Sheet - Help NEEDED!

Good day.

I have a Workbook "Client Report List". I need information to be carried over from Sheet 1 till Sheet 200, from Column B2 till B8 and copied over to the sheet "Clients Data". With a filter function should the client's information ("Client Name & Surname) already have been filled in then it only needs to update the "Last visited" column on "Clients Data" to the most recent date. I have used =Sheet1!$B$2 for the required fields but this does not help in filtering duplicated names and automatically updating to the most recent date in the "last visited" column. What formula or Macro can I use?

Client Reports List - Jacques.xlsx

200 sheets? Seriously? Why?? You only need one sheet with a drop down Data Validation client list. Then use VLOOKUP to populate the rest of the cells. If you need to print a page for every client, you could have a macro to loop through the client codes, populate the sheet and print it.

Whatever, if you want 200 sheets, I'd use VLOOKUP to populate the cells, not a simple link.

Try:

Cell B4:

Formula:

Please Login or Register  to view this content.

copied across to column G

Cell H4:

Formula:

Please Login or Register  to view this content.

And add a new column I

Cell I4:

Formula:

Please Login or Register  to view this content.

Array entered with Ctrl-Shift-Enter rather than just Enter

Column H will reflect the date of the visit on the relevant sheet. Column I will reflect the last visit for a mine/contact

Copy all formulae down to row 203.

Given the structure of your workbook, I'm not sure you can.

The formulae basically extract the data from the visit sheets.

You could, perhaps, insert a Pivot Table and just include the Contact Name and the Maximum of the Last Visited Date.

See the updated example