One play .. Assume source data in identically structured sheets: East, West
cols A to V, data from row3 down (Key col = col A, if populated, i.e. not
empty)
Sample construct at:
http://cjoint.com/?cxdC01caEA
AutoMerge Rows from 2 sheets to summ sheet_KeyCol A populated.xls
In sheet: All
With the same headers pasted into A1:V2
Put in A3:
=IF(ISERROR(SMALL($W:$W,ROW(A1))),
IF(ISERROR(SMALL($X:$X,ROW(A1)-COUNT($W:$W))),"",
INDEX(West!A:A,SMALL($X:$X,ROW(A1)-COUNT($W:$W)))),
INDEX(East!A:A,MATCH(SMALL($W:$W,ROW(A1)),$W:$W,0) ))
Copy A3 to V3
Enter the sheet names into W2:X2 : East, West
Put in W3:
=IF(TRIM(INDIRECT("'"& W$2 & "'!A"&ROW(A1)+2))="","",ROW())
Copy W3 to X3
Select A3:X3, copy down to cover the aggregated max expected extent of data
in East and West (if East & West is expected to contain a max of 10 rows
each, copy down by 20 rows to X22)
The above will automatically return the non-empty data lines from East and
West (i.e. where col A <""), with East's lines stacked above West's, all
lines neatly bunched at the top. It'll also cater for row insertions* /
deletions made in East's / West's data lines
*within the max 10 rows per sheet extent above
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
oups.com...
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.