Copy different (and changing) length lists from multiple sheets

Good Day Everyone!

I have all the names with their division on Sheet "Roster." Each division (1ST DIV and 2ND DIV) will enter what "Type" a person has completed over the reporting period. The DH needs the summary (DEPT HEAD) of who completed Type 2 and Type 3, and their start and stop cycles.

All data in "yellow" (on 1ST DIV and 2ND DIV) is entered by the individual divisions. All the data in "orange" (on ROSTER) can be updated by the person compiling the Dept Head report. All other cells are locked.

The trick is that I cannot use VB or macros (system security blocks them). The data flow i have is: 1) each division selects the appropriate Type (dropdown); 2) If Type 2 or Type 3 is selected, the name auto populates (INDEX/SMALL), and the division must enter the START/STOP info; 3) the division-entered data is copied to the ROSTER sheet {VLOOKUP) next to the appropriate name; 4) the DEPT HEAD info is taken from the updated table of data on the ROSTER PAGE (INDEX/SMALL).

How can I copy the data from the Division sheets to the Dept Head sheet without putting it through the Roster sheet? The data can change daily as to who does what type, making the lists on each division variable in length.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Hello mccannmj4130 and Welcome to Excel Forum.
Thank You for changing the title.
If there are only two tables from which to pull then you could use the following to get the first and last name as well as the start and stop values:

Formula:

Please Login or Register  to view this content.

The following will yield the division: =IF(A14="","",IF(ROWS(A$14:A14)<=SUMPRODUCT(--('1ST DIV'!A$14:A$18<>"")),"1ST DIV","2ND DIV"))
Let us know if you have any questions.

As your profile shows that you are using the 2010 version of Excel do you have, or could you get, the Power Query add-in which is free and is produced by Microsoft?

IN B2 then copied across

Please Login or Register  to view this content.

IN A14 then copied to A & B columns

Please Login or Register  to view this content.

IN C14 then copied down

Please Login or Register  to view this content.

IN D14 then copied to Columns D & E

Please Login or Register  to view this content.

suitable changes can be done for second table.

It is better to keep tables for Different Types Side by side instead of one below the other in all tabs. I have given in example tab.