Generating a master spreadsheet from 9 sub sheets

[icsvortex912]

I'm an Excel amateur in need of some professional help. Here's the situation:

I have a workbook set up that's going to be on a shared drive so that people from several different locations can add information to it. Each person will add information only to his or her own location (different sheets for different locations). I have all of the individual tabs for the different locations set up; what I want now is for a master sheet to automatically populate itself with all of the information from the various locations, and update itself as the different sheets are modified.

The names of the other sheets, if somebody would be willing to draw up a macro for me, are Cedar Rapids, Dallas, Denver, Kansas City, Langhorne, Lincoln, Milwaukee, Minneapolis and Omaha.

Thank you in advance for any help and suggestions!

 

[Smitty]

Welcome to the Board!

Why not just use direct referencing? E.G. =Dallas!A1

Unfortunately, shared workbooks suck and there's a bunch that they won't support in terms of programmability. You might want to look at the Microsoft Excel Blog for collaborating with the Excel Web app.

HTH,

 

[icsvortex912]

I want the master sheet to list all Cedar Rapids, then all Dallas, then all Denver, etc. If I just use direct referencing, and there are not always the same amount of lines of information from each of the sheets...how can I end up with a clean master sheet?

Maybe I'm missing something -- can you layer direct references on top of one another so that it checks cedar rapids cells until there is no more information, and then goes from there to Dallas, then to Denver, etc.?

 

[Smitty]

So you're going to have the data from each sheet aggregate in the Master? Can you post an example of what you've got now and what you want the master to look like when it's done? (See the link beneath my sig for how to post a shot of your sheet).

 

[icsvortex912]

I'm having problems posting shots of my sheets from your instructions. I'll try to put it into words...

There are 9 sub sheets, one each for the 9 different locations. Each of these sheets is identical to the next. User-entered information will always fall into the range A2:M100.

The master spreadsheet will also be identical to each of the sub sheets; the only difference is that the master will display all of the information from every location.

I could use direct referencing, but the number of populated rows in each of the 9 locations is going to fluctuate on a day-to-day basis. So if I allowed 20 rows for each of the locations, there might be a day when there are only 4 populated rows in the Cedar Rapids sheet, and I am then stuck with 16 #N/A rows on my master sheet between Cedar Rapids and Dallas.

Does that help at all?