Update From Master Sheet

[JR]

Hello,
I am looking for a macro or suggestion to be able to update sheets in a
workbook from the master sheet. All sheets use column A for the id number.
Anyway to use a macro?

Thanks JR

[Max]

Adapting from a recent post by Bob Phillips
here's one play to automate it using array formulas ..

Sample construct at:
http://www.savefile.com/files/7715209
AutoFiltering_Data_To_Resp_Sheet_ArrayFormula_DefinedName.xls

Assume the master list is in sheet: Master
in cols A to D, headers in row1, data from row2 down
(Key ID is in col A, the tech #)

TECH ACCT# STAT ADDRESS
603 162395-7 CP 6844 N DE CHELLY
607 164655-11 CP 10700 N LA RESERVE LOOP # 2106
603 267454-1 CP 7270 S SAND DUNE DR D
609 131976-3 CP 5702 N CAM LAGUNA VLY
etc

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 Grove.)

In a new sheet named: 603
Paste the col headers over into A1:D1

Put in A2, array-enter the formula (press CTRL+SHIFT+ENTER):
=IF(ROW()-ROW(A$2:A$100)+1>ROWS(Master!$A$2:$A$100)-COUNTIF(Master!$A$2:$A$1
00,"<>"&WSN),"",
INDIRECT("Master!"&ADDRESS(SMALL((IF(Master!$A$2:$A$100=--WSN,ROW(Master!$A$
2:$A$100),
ROW()+ROWS(Master!$A$2:$A$100))),ROW()-ROW(A$2:A$100)+1),COLUMN(Master!A$2:A
$100),4)))

Copy A2 across to D2, fill down to say D20
(fill down just enough to cover the max expected extent of data per tech)

Cols A to D will return only the lines for tech: 603 from "Master",
with all lines neatly bunched at the top

For a clean look, suppress the display of extraneous zeros in the sheet via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK

Now, just make a copy of the sheet: 603, rename it as say: 607
and we'd get the results for tech: 607.

Repeat the copy > rename sheet process
to get the rest of the tech sheets as required (a one-time job)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"JR" <[email protected]> wrote in message
news:[email protected]...
> Hello,
> I am looking for a macro or suggestion to be able to update sheets in a
> workbook from the master sheet. All sheets use column A for the id number.
> Anyway to use a macro?
>
> Thanks JR
>
>