Is it possible for Excel to search and then start a new search from where it left off?

I'm using Excel to track monthly scheduling variances for about a dozen individuals my agency supports. I have one monthly spreadsheet for the entire agency, arranged by date. (We'll call this Sheet 1.) Then, for each individual, I have a monthly worksheet where I track the variances just for that individual person. (We'll call this Sheet 2.) Currently, at the end of each month, I have to open Sheet 1, go down the list of variances looking for a single individual (we'll call him John Doe), copy and paste that information into that individual's Sheet 1, and then repeat the process for the next person. It's doable, but time-consuming, and I make mistakes due to incorrect copying and overlooking entries. (As a side note, I should point out here that there's not really a more efficient way to track these variances, for a couple of different reasons that you're probably not interested in hearing. I just wanted to eliminate the most obvious solution--skipping Sheet 1 altogether--as a possibility.)

Is there any way to enter a formula into a cell on Sheet 2 to search Sheet 1 until it finds that individual's name, enters the corresponding information on Sheet 2, and then picks up the search one row below where it left off searching on Sheet 1? Basically I want the spreadsheet to do exactly what I've been doing manually--start at the top of Sheet 1, go down the list until I find John Doe, enter his information in Sheet 2, return to the same spot on Sheet 1 and continue searching from the next row until I find John's name again and enter his information on Sheet 2, row 2, and so on and so forth.

I find it hard to explain these things without a visual aid, so I've attached a small version of the spreadsheet for John Doe and his fictional housemate Bob Smith that I hope will clarify what I'm trying to do. Thanks!

First of all, you can do what you are trying to achieve quite easily in Excel. I have some observations about your data, though.

It looks like Sheet1 only lists the variations that you have identified (separately), so it is fairly straightforward to just list a subset of those on a separate sheet according to month/year and name of staff selected.

On Sheet1, your date column (A) does not contain proper dates as Excel understands them. Your entry of 2-4 in A4, for example, will be treated by Excel as a text value. You should enter these values as dates (i.e. 2/4/2017), and you can apply a format to those cells to display them in the style you prefer.

By having February 2017 in F2, this implies either that you will have other dates further down the table, or other sheets for each month. It would be easier to analyse the data if it was all on one sheet, any by having proper dates in column A then F2 will be redundant - just keep adding new data at the bottom of what you have already.

On Sheet2 I would suggest that you put Individual name: in A3 and then you can use the name John Doe in B3. This could be derived from a drop-down list, to make it easier to select a new name. A separate list of all names would be needed (perhaps on a separate sheet (e.g. called ref_data), and these drop-downs could also be used in column E of Sheet1 to reduce typing and avoid mis-spelt names.

You could use H3 on Sheet2 in a similar way to choose the month and year of interest. It would be better to derive the table which occupies A5:H8 directly from the month/year chosen in H3, though I'm not sure where these numbers come from - presumably your staff work different shifts, so the hours per day-of-week would vary depending on shift pattern of the name chosen in B3.

As for an approach to tackling the problem, I would suggest that you use a helper column in Sheet1 (e.g. column H) to identify the records of interest, depending on the name chosen in B3 of Sheet2 and the month/year in H3, and then an INDEX/MATCH formula in Sheet2 could return the relevant data to row 13 onwards in Sheet2. Changing the name in B3 or the month/year in H3 would cause the display to change automatically, so you wouldn't need to list your staff individually further down the sheet.

Hope this helps.

Pete