Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
wrote
.. 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". 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
wrote
Very interesting. I'm really impressed. You're welcome ! Where does the 10 row maximum come from, your example or is this an actual limitation of the function? 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). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
... 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_populate d_v2a.zip -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
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. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
wrote
.. 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. 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
wrote
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? .. 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Master Worksheet reflecting sub-worksheets
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining like worksheets into a single worksheet | Excel Worksheet Functions | |||
Insertion of existing worksheets in current worksheet | Excel Discussion (Misc queries) | |||
separate worksheet into multiple worksheets by grouping | Excel Worksheet Functions | |||
Master worksheet automatically enters data into sub worksheets | Excel Discussion (Misc queries) | |||
Linking cells in a worksheet to other worksheets in a workbook | Excel Discussion (Misc queries) |