Create List sourced from Multiple sheets of varying lengths

[desertSol]

Hello,

I'm new here, and only recently began using Excel as it's intended. I'm
really excited to be digging into it, and enjoy solving my dilemnas, but this
one has me stumped. And I know I am overlooking something obvious! Any help
is greatly appreciated.

Here's how my challenge:

I have a "test system workbook" that has a sheet for each tester on a
project. The tester sheet includes a cell for the person's name and a cell
for location, and a 4 column table that each tester fills with their test
system(s) info; "ID number," "System Name," "WIKI HyperLink," and "Status"

The number of systems any tester uses varies dramatically.

On Sheet 1, I'm needing to compile a "master list" of the rows of IDs,
System Names, WIKI Links, and Status from the tables across all sheets, while
adding the correct Tester Name and location from the sheet that the systems
are drawn from.

The master list needs to be dynamic, so that if the Tester on Sheet 3 adds a
new system to his table, the master list is updated to include the info.

I can do it statically, using references to ranges across the sheets, etc.
But I really need it to be dynamic, and I don't feel like it should be so
difficult.

Please! Help me overcome my ignorance.

Thanks in advance!

 

[Otto Moehrbach]

I'll try to help you with that. The layout of the Master sheet you describe
is not clear. Also, something being "dynamic" is wide open to
interpretation. Excel can react automatically, using VBA, to the occurrence
of a large number of events. It would be up to you to decide exactly when
you want this "dynamic update" to occur. You say each tester has a 4 column
table into which he adds a row. Would you want the update to occur each and
every time that an entry is made into the last of the 4 columns on any
tester sheet? Or maybe do the update when the workbook is saved. Or
closed? Or the sheet is deselected? Or something else? If you wish, send
me your workbook. If your data is proprietary, fake the data. I need only
the layout. Include some examples of what you want to happen when an
"update' occurs. My email is (e-mail address removed). Remove the
"extra" from this address. HTH Otto