Create a dynamic list from multiple sheets

**shbiskup** · 04-18-2007, 01:56 PM

I have a spreadsheet with 4 sheets in it. I want to have the 4th sheet create a list based upon the first 3. I want the list on the 4th sheet to be updated as changes happen in the first 3, which are maintained by individuals assigned to keep them updated. This is meant to help track assignments of individuals across multiple projects.

More specifically

In sheet 1, 2, and 3 column B will have a list of names. On sheet 4 it will be Column B again, where a list will be created of all the names from the column B on sheets 1, 2, and 3. The lists on 1,2,3 will have some duplicate names and thats fine I want to see multiple entries when a name appears on more than one list.

Just getting a formula or tip on how something like this would be set up would be a great start and GREATLY appreciated. I already know to how to make a cel on sheet 4 read from a specific cel on another sheet. It's the dynamically changing content of the first 3 sheets and how to make a list on sheet 4 with out gaps etc.. that is beyond my grasp right now.

Additional “wishes”,
1. Each name will actually have an associated pair of dates in the next to adjacent cels, C and D. Ultimately I will need to be able to keep them associated with the name they match.

2. I would like to be able to sort the list of names.

THANK YOU in advance!

-Stefan

**BigBas** · 04-18-2007, 02:18 PM

Check this out and let me know if it does what you would like:

**shbiskup** · 04-18-2007, 05:12 PM

Hey Thanks Big Bas!

this is definitely the basic idea.

What I found was if I went below the number of entries in the sheets of 1,2,3 Column A, in sheet 4 I got 0's in those(empty) cels. If I put in MORE names in the 1,2,3 sheets so that the total exceeds what is alotted in sheet 4 the names don't appear.

These are minor, I could live with the 0s and just have the formula copied all the way down the column.

I don't seem to be albe to sort the results thats the next step I guess.

THANK YOU so much for this its really helpful~!

**BigBas** · 04-18-2007, 05:54 PM

I had actually updated the file to combat JUST those two problems, but I guess I had somehow linked the older file. The new version of the file can handle up to 500 entries on the TOTAL page. If you need more, just fill the formula down:

**SLEUTH031** · 10-28-2022, 03:33 AM

Hello! Can you do this using vba macro? i have like 75 sheets that i want to get data from and the sheet names are just numbered from 1 to 75

**Glenn Kennedy** · 10-28-2022, 04:04 AM

Sleuth. Hi.

It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.