How can I automatically create a master list from 5 worksheets?

[Guest]

Hello,
I have been posting frequently, so I apologize if this is redundant. I just thought of a simpler way to solve my problem and was looking for some help to get there.

I was wondering if there is a way for excel to pull information from a workbook with 5 sheets. I do not want to filter it for criteria - I can do that with a macro once I have the master list together.

What I have is 5 sheets that are not in the exact same format. They do all contain 7 fields of similar information which I would like to extract and put into a master list in a separate file. I would like this file to update itself everytime it opens so that this master list is up-to-date whenever someone makes an entry in one of the 5 worksheets.

Is there a way to do this with a wizard or built-in feature? I really am not that experienced with VB or macros and can only create basic ones with the record feature.

Thanks for your help!

 

[Guest]

Seto:

Not too bad. Let me offer some examples of sheets you might have, to help illustrate how it works. Let's say that the first file is called "California," and has sheets "Sacramento","Los Angeles", "San Diego", "San Francisco", and "Fresno."

Then, your second file is called "USA."

What you do in the "USA" file is this:
1) Make sure the other file (hereafter referred to as "California") is open.
2) In the "USA" file, in the cell where you want the data to show up (let's say, A1), type your equals (=) sign.
3) Click on the "California" workbook in the taskbar.
4) Click on the worksheet (say, "Sacramento") that has the data you want.
5) Click on the cell in the sheet (say, A1) that has the data you want.
6) Hit "Enter."
7) What that'll do is populate the USA book's A1 cell with the value from the California workbook, sheet Sacramento, cell A1's value. The formula will look like this:

[California]Sacramento!$A$1

The file name, California, is in brackets. The worksheet name, Sacramento, has an exclamation point to the right of it. The $A$1 is the cell reference.

When you close and re-open the USA file, you will have your wish; the cells will be updated automatically based on data entered by other folks in the California workbook's sheets.

Hope that helps. Have a good one.

Geoff Lilley
(e-mail address removed)
MCDST, Microsoft Office Master Instructor (XP/2000),
Microsoft Office Specialist Master (XP/2000)