Master Worksheet reflecting sub-worksheets

  • Thread starter Thread starter sloan.fader
  • Start date Start date
S

sloan.fader

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.
 
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
 
Very interesting. I'm really impressed.

Where does the 10 row maximum come from, your example or is this an
actual limitation of the function?
 
Two things.
1.
The second INDEX function within your =IF
INDEX(East!A:A,MATCH(SMALL($W:$W,ROW(A1)),$W:$W,0)))
This seems to be missing a ' ( ' or have an extra ' ) ' I think it
should end ' $W,0)) ' no?

2.
Could I continue this worksheet merge with additional sheets by adding
more INDEX function lines to the overall IF function? If so, what would
be the additional format?
Example: Add sheet "North" and "South".

<clipped>
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))
INDEX(North!A:A,MATCH(SMALL($W:$W,ROW(A1)),$W:$W,0))
INDEX(South!A:A,MATCH(SMALL($W:$W,ROW(A1)),$W:$W,0))
<continues>
 
.. Could I continue this worksheet merge with additional sheets
by adding more INDEX function lines to the overall IF function?
If so, what would be the additional format?
Example: Add sheet "North" and "South".
Click to expand...

Here's a revised/extended play
to cater for 4 sheets: North, South, East, West

A sample construct available at:
http://www.savefile.com/files/6920182
AutoMerge_Rows_from_4_sheets_to_summ_sheet_KeyCol_A_populated_v2.zip

Assume source data in identically structured sheets: North, South, East,
West, cols A to V, data from row2 down (Key col = col A, if populated, i.e.
not empty)

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))),
IF(ISERROR(SMALL($Y:$Y,ROW(A1)-COUNT($X:$X)-COUNT($W:$W))),
IF(ISERROR(SMALL($Z:$Z,ROW(A1)-COUNT($Y:$Y)-COUNT($X:$X)-COUNT($W:$W))),"",
INDEX(OFFSET(INDIRECT("'"&$Z$2&"'!A:A"),,COLUMN(A1)-1),SMALL($Z:$Z,ROW(A1)-C
OUNT($Y:$Y)-COUNT($X:$X)-COUNT($W:$W)))),
INDEX(OFFSET(INDIRECT("'"&$Y$2&"'!A:A"),,COLUMN(A1)-1),SMALL($Y:$Y,ROW(A1)-C
OUNT($X:$X)-COUNT($W:$W)))),
INDEX(OFFSET(INDIRECT("'"&$X$2&"'!A:A"),,COLUMN(A1)-1),SMALL($X:$X,ROW(A1)-C
OUNT($W:$W)))),
INDEX(OFFSET(INDIRECT("'"&$W$2&"'!A:A"),,COLUMN(A1)-1),MATCH(SMALL($W:$W,ROW
(A1)),$W:$W,0)))
Copy A3 to V3

Enter the sheet names into W2:Z2, say: North, South, East, West
Note:
1. Enter names in the desired "stacking" sequence from W2 to Z2
(Lines will be stacked in the order: W2's, then X2's, then Y2's, then Z2's.
So we have some flexibility here <g>.)
2. Names entered must match *exactly* what's on the tabs

Put in W3: =IF(TRIM(INDIRECT("'"& W$2 & "'!A"&ROW(A1)+2))="","",ROW())
Copy W3 to Z3

Select A3:Z3, copy down to cover the aggregated max expected extent of data
in all the 4 sheets (eg: if each sheet is expected to contain a max of 10
rows each, copy down by 10 rows x 4 sheets = 40 rows to Z42)

The above will automatically return the non-empty data lines from the 4
sheets: North, South, East and West (i.e. where col A <>""), stacked per
order of the sheetnames in W2:Z2, viz.: North's lines, then South's, then
East's, then West's, all lines neatly bunched at the top. It'll also cater
for row insertions* / deletions made in each sheet's data lines.
*within the assumed max 10 rows per sheet extent above

To maintain tolerable performance, if there's quite a fair bit lines to
bring over from each sheet, set the calc mode to Manual (via: Tools >
Options > Calculation tab). Then press F9 to update/calc when ready.

---
 
Very interesting. I'm really impressed.
Click to expand...

You're welcome !
Where does the 10 row maximum come from,
your example or is this an actual limitation of the function?
Click to expand...

10 rows was the assumed max expected extent in each sheet to be catered for.
It's not a limitation of the non-array formulas used in "All". Of course,
if we have to cater for more lines per sheet, then the aggregated coverage
(formula lines to be copied down) will increase in "All". And this would
impact calc performance (we can manage this to an extent by setting the calc
mode to Manual). See my response to your other post (Link to a revised
sample to merge 4 sheets to play with).

---
 
... a slight tweak to this "last" line for the formula in A3:

... INDEX(OFFSET(INDIRECT("'"&$W$2&"'!A:A"),,COLUMN(A1)-1),
MATCH(SMALL($W:$W,ROW (A1)),$W:$W,0)))

Amended to:
.... INDEX(OFFSET(INDIRECT("'"&$W$2&"'!A:A"),,COLUMN(A1)-1),
SMALL($W:$W,ROW(A1))))

Amended sample uploaded at the same link:
http://savefile.com/files/6920182
AutoMerge_4_sheets_to_summ_sheet_KeyCol_A_populated_v2a.zip

---
 
This is on another topic, but is there a way to make the formula bar
not cover the content below? The large formulas in this situation cause
the cells in view below to be obscured by the expanded formula.
 
.. This is on another topic, but is there a way to make the formula bar
not cover the content below? The large formulas in this situation
cause the cells in view below to be obscured by the expanded formula.
Click to expand...

Just switch off/toggle the display via: View > Formula bar

Note: The 7 line breaks made in the formula were intentional, for clarity.
Removing all of these line breaks (just Backspace at each new line) will
also improve the display to an extent.

---
 
1.
The second INDEX function within your =IF
INDEX(East!A:A,MATCH(SMALL($W:$W,ROW(A1)),$W:$W,0)))
This seems to be missing a ' ( ' or have an extra ' ) ' I think it
should end ' $W,0)) ' no? ..
Click to expand...

Not sure there's anything missing/extra as per your point above. But do
note that I've since revised this last line of the formula (.. MATCH(...)
part is done away with ) as responded in the other post.

---
 
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)Data>Import External Data>New Database Query
Databases: Excel Files
Click to expand...

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 Data>Refresh 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
 
Back
Top