Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Updating Several Worksheets at One Time
When I update my master worksheet, I want to save time by updating several
tabs at the same time. I am using the following formula/function: =VLOOKUP("BOS",Master!A$3:B$26,2,FALSE) I have several dealers within the BOS region. I want in column A (BOS "region"), column B (dealer code), column c (dealer name), etc. The above function is pulling over the Region, but not separating the dealer info (dealer code, name, etc), how do I keep it from duplicating, but pull over ALL dealer info under the BOS region? Or is there a better formula that might work? Thanks in advance for your help, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Updating Several Worksheets at One Time
On Dec 23, 12:03*pm, daydd wrote:
When I update my master worksheet, I want to save time by updating several tabs at the same time. I am using the following formula/function: =VLOOKUP("BOS",Master!A$3:B$26,2,FALSE) I have several dealers within the BOS region. I want in column A (BOS "region"), column B (dealer code), column c (dealer name), etc. The above function is pulling over the Region, but not separating the dealer info (dealer code, name, etc), how do I keep it from duplicating, but pull over ALL dealer info under the BOS region? *Or is there a better formula that might work? Thanks in advance for your help, VLOOKUP returns only one answer. You are trying to have VLOOKUP search on the region, which has several items, so it can't work. You need to search on a choice that is unique. Are the dealer codes unique? You could search on those instead, but then they would have to be entered in the secondary sheets. Otherwise you need a different method. Advanced Filter might be a good choice. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Updating Several Worksheets at One Time
Here's an easy set-up which automates
the exact functional deliveries you seek using non-array formulas .. Illustrated in this sample: http://www.savefile.com/files/1945932 Parent to Child AutoCopy Model based on KeyCol Values.xls Construct: In sheet: WS1 (the "master"/"parent" sheet) Source data in cols A to C, from row2 down, with the key col = col A (as per spec) List the key col values (col A's unique values) in M1 across, eg: BOS, Reg2, etc (list can be in any order, but must match exactly with what's on the tabs, except for case) Put in M2: =IF($A2="","",IF($A2=M$1,ROW(),"")) Copy M2 across & fill down to cover the max expected extent of source data in the key col A Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1") ))+1,32) Click OK The above defines WSN as a name we can use to refer to the sheetname in formulas It will auto-extract the sheetname implicitly Technique came from a post by Harlan In a new sheet named: BOS With the same col labels pasted into A1:C1 Put in A2: =IF(ROWS($1:1)COUNT(OFFSET(WS1!$L:$L,,MATCH(WSN,W S1!$M$1:$IV$1,0))),"",INDEX(WS1!A:A,MATCH(SMALL(OF FSET(WS1!$L:$L,,MATCH(WSN,WS1!$M$1:$IV$1,0)),ROWS( $1:1)),OFFSET(WS1!$L:$L,,MATCH(WSN,WS1!$M$1:$IV$1, 0)),0))) Copy A2 across to C2, fill down to say, C10 (copy down by the smallest possible range sufficient to cover the max expected extent for any key col value. Here, I've assumed that 9 rows (rows 2 to 10) is sufficient) Cols A to C will return only the lines for the key col value: BOS from "WS1", with all lines neatly packed at the top Dress this sheet up nicely to taste, then just make copies of it, rename as the other key col values: Reg2, etc to get corresponding returns. Adapt to suit .. Updates in the master/parent sheet: WS1 will be automatically copied into the respective child (ie your "Region") sheets (Just in time for Christmas, too!) -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "daydd" wrote: When I update my master worksheet, I want to save time by updating several tabs at the same time. I am using the following formula/function: =VLOOKUP("BOS",Master!A$3:B$26,2,FALSE) I have several dealers within the BOS region. I want in column A (BOS "region"), column B (dealer code), column c (dealer name), etc. The above function is pulling over the Region, but not separating the dealer info (dealer code, name, etc), how do I keep it from duplicating, but pull over ALL dealer info under the BOS region? Or is there a better formula that might work? Thanks in advance for your help, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Updating Several Worksheets at One Time
Here's an easy set-up which automates
the exact functional deliveries you seek using non-array formulas .. Illustrated in this sample: http://www.savefile.com/files/1945932 Parent to Child AutoCopy Model based on KeyCol Values.xls Construct: In sheet: WS1 (the "master"/"parent" sheet) Source data in cols A to C, from row2 down, with the key col = col A (as per spec) List the key col values (col A's unique values) in M1 across, eg: BOS, Reg2, etc (list can be in any order, but must match exactly with what's on the tabs, except for case) Put in M2: =IF($A2="","",IF($A2=M$1,ROW(),"")) Copy M2 across & fill down to cover the max expected extent of source data in the key col A Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1") ))+1,32) Click OK The above defines WSN as a name we can use to refer to the sheetname in formulas It will auto-extract the sheetname implicitly Technique came from a post by Harlan In a new sheet named: BOS With the same col labels pasted into A1:C1 Put in A2: =IF(ROWS($1:1)COUNT(OFFSET(WS1!$L:$L,,MATCH(WSN,W S1!$M$1:$IV$1,0))),"",INDEX(WS1!A:A,MATCH(SMALL(OF FSET(WS1!$L:$L,,MATCH(WSN,WS1!$M$1:$IV$1,0)),ROWS( $1:1)),OFFSET(WS1!$L:$L,,MATCH(WSN,WS1!$M$1:$IV$1, 0)),0))) Copy A2 across to C2, fill down to say, C10 (copy down by the smallest possible range sufficient to cover the max expected extent for any key col value. Here, I've assumed that 9 rows (rows 2 to 10) is sufficient) Cols A to C will return only the lines for the key col value: BOS from "WS1", with all lines neatly packed at the top Dress this sheet up nicely to taste, then just make copies of it, rename as the other key col values: Reg2, etc to get corresponding returns. Adapt to suit .. Updates in the master/parent sheet: WS1 will be automatically copied into the respective child (ie your "Region") sheets (Just in time for Christmas, too!) -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "daydd" wrote: When I update my master worksheet, I want to save time by updating several tabs at the same time. I am using the following formula/function: =VLOOKUP("BOS",Master!A$3:B$26,2,FALSE) I have several dealers within the BOS region. I want in column A (BOS "region"), column B (dealer code), column c (dealer name), etc. The above function is pulling over the Region, but not separating the dealer info (dealer code, name, etc), how do I keep it from duplicating, but pull over ALL dealer info under the BOS region? Or is there a better formula that might work? Thanks in advance for your help, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Updating Several Worksheets at One Time
I would suggest using a pivot table in that case. Highlight Master A:B and go
to Data - Pivot Table and Pivot Chart.. Drop your columns into the row fields. Right-click and select Field Options to remove subtotals if you don't want to see those. "daydd" wrote: When I update my master worksheet, I want to save time by updating several tabs at the same time. I am using the following formula/function: =VLOOKUP("BOS",Master!A$3:B$26,2,FALSE) I have several dealers within the BOS region. I want in column A (BOS "region"), column B (dealer code), column c (dealer name), etc. The above function is pulling over the Region, but not separating the dealer info (dealer code, name, etc), how do I keep it from duplicating, but pull over ALL dealer info under the BOS region? Or is there a better formula that might work? Thanks in advance for your help, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum worksheets - w/o updating formula for new worksheets which are | Excel Worksheet Functions | |||
Updating cells on different worksheets | Excel Worksheet Functions | |||
Updating multiple graphs in multiple worksheets at the same time | Excel Discussion (Misc queries) | |||
updating multiple worksheets | Excel Discussion (Misc queries) | |||
Updating worksheets from another file | Links and Linking in Excel |