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,

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.

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,

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,

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,