I know I'm a bit late in this thread, but I was thinking you might be able to
use MS Query to consolidate the data from yourtwo wkshts.

This example uses 2 named ranges in the same workbook.

Assumptions:
The data in each list is structured like a table:
---Col headings (Dept, PartNum, Desc, Price)
---Columns are in the same order.

The data in each sheet must be in named ranges.
---I used rngEastData for East's data, rngWestData for West.

Save your file before continuing.

(Note: In the next steps, MS Query may display warnings about it's ability
to show the query ...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query
Databases: Excel Files

Browse to the file, pick the one of the data ranges to import.
---Accept defaults until the next step.

At The last screen select The View data/Edit The Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT * FROM `C:\MyWkbk`.rngEastData
UNION ALL
SELECT * FROM `C:\MyWkbk`.rngWestData

(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )
Return the data to Excel.

Once that is done....to get the latest data just click in the data range
then DataRefresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)

I like this method because it avoids the kind of workbook bloat that a
complicated formulaic approach can sometimes inflict. Also, there no complex
formulas to interpret and maintain.

Something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


" wrote:

This is, I realize, a database situation, but I have to work with Excel
for certain reasons.

Q?: How do I reference one worksheet to another?

My example:
I have three worksheets.
"East", "West", "All"(master wksheet).

They all have the same headers, which live in A$1:$V$2

I want worksheet All to contain the contents of East & West. As I
update East/West, I would like All to reflect those changes. Rows may
be added/deleted from East/West, so All would need to expand/contract
appropriately.

Using the information below the column headers, is there a way to post
all data from East & West into All? I don't want to post individual
cell references, especially since cells won't stay the same on
East/West as rows are added/deleted.

This may be a VBA macro, which is unfortunately beyond my skill set
these days, though I'm learning.