Copy subrange without macros
 

I need to create an Excel Template that will be stored as xml.

The template will consist of four worksheets:
Master Data
Department A
Department B
Department C

A third party program (over which I have no control) will open the workbook
and dump company data into the Master Data worksheet.
I need to somehow populate each of the Department worksheets with only the
data relevant to that particular department.

The Master Data worksheet will have the following columns
Dept Code:
Transaction No:
Transaction Type:
Date:
Amount:

I need to somehow get any rows that have a Dept Code of A into the
Department A worksheet, rows with a Dept Code of B into the Department B
worksheet etc.

I cannot have any macros as this document needs to be stored as xml (this is
the client's pre requisite).
There cannot be any blank lines in the middle of the Department A,
Department B, Department C worksheets.
There must be no user intervention required.

I had thought of populating the first 1000 rows of so of each of the
department worksheets with a formula to display the corresponding rows of
Master Data if the Dept Code equalled A but this would leave blank rows
everywhere.

Can it be done? Any ideas?

Thanks
Tony

Copy subrange without macros
 

Tony,

I believe that I have found a solution that does not require macros, as your
client requires. If you would consider making a small donation to my PayPal
account I will gladly give you my solution. Please write on my private e-mail:
stephane [dot] quenson [at] gmail [dot] com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200708/1

Copy subrange without macros
 

I don't know / I'm not sure about the xml part of your post. That aside,
think here's one formulas set-up which could deliver the required
functionalities ..

Take this sample construct from my archives:
http://www.savefile.com/files/430142
AutoCopy Lines to Resp Sht Non Array.xls
(Full details inside, nicely rendered. Easy to adapt ..)

Data is continuously entered in a master ("parent") sheet, with lines neatly
auto-copied to each individual ("child") sheet based on the values within a
key col.

In the sample, the key col in the master sheet is the "State" col, which may
contain eg: NY, CA, NV, SD, AZ, etc. All lines with "NY" in the key col will
be auto-copied to the sheet named: NY, and appear neatly bunched at the top,
w/o any intervening blank lines. Ditto for lines with "CA", "NV", etc which
will be copied into their respective sheets.

Propagation of the "child" sheet is as simple as making a copy of the
initial one, then renaming it accordingly as the next key col value. Eg we
first formulate one child sheet for "NY", dress it up nicely, then just make
copies of the "NY" sheet, and rename these as: CA, NV, SD, etc.

In your case, the key col would be your "Dept Code" col, eg: DeptCode1,
DeptCode2, etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tony Starr" wrote in message
...

I need to create an Excel Template that will be stored as xml.

The template will consist of four worksheets:
Master Data
Department A
Department B
Department C

A third party program (over which I have no control) will open the
workbook and dump company data into the Master Data worksheet.
I need to somehow populate each of the Department worksheets with only the
data relevant to that particular department.

The Master Data worksheet will have the following columns
Dept Code:
Transaction No:
Transaction Type:
Date:
Amount:

I need to somehow get any rows that have a Dept Code of A into the
Department A worksheet, rows with a Dept Code of B into the Department B
worksheet etc.

I cannot have any macros as this document needs to be stored as xml (this
is the client's pre requisite).
There cannot be any blank lines in the middle of the Department A,
Department B, Department C worksheets.
There must be no user intervention required.

I had thought of populating the first 1000 rows of so of each of the
department worksheets with a formula to display the corresponding rows of
Master Data if the Dept Code equalled A but this would leave blank rows
everywhere.

Can it be done? Any ideas?

Thanks
Tony

Copy subrange without macros
 

Max,

Brilliant! Does the job nicely.

Thank you very much.
Regards
Tony


"Max" wrote in message
...
I don't know / I'm not sure about the xml part of your post. That aside,
think here's one formulas set-up which could deliver the required
functionalities ..

Take this sample construct from my archives:
http://www.savefile.com/files/430142
AutoCopy Lines to Resp Sht Non Array.xls
(Full details inside, nicely rendered. Easy to adapt ..)

Data is continuously entered in a master ("parent") sheet, with lines
neatly auto-copied to each individual ("child") sheet based on the values
within a key col.

In the sample, the key col in the master sheet is the "State" col, which
may contain eg: NY, CA, NV, SD, AZ, etc. All lines with "NY" in the key
col will be auto-copied to the sheet named: NY, and appear neatly bunched
at the top, w/o any intervening blank lines. Ditto for lines with "CA",
"NV", etc which will be copied into their respective sheets.

Propagation of the "child" sheet is as simple as making a copy of the
initial one, then renaming it accordingly as the next key col value. Eg we
first formulate one child sheet for "NY", dress it up nicely, then just
make copies of the "NY" sheet, and rename these as: CA, NV, SD, etc.

In your case, the key col would be your "Dept Code" col, eg: DeptCode1,
DeptCode2, etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tony Starr" wrote in message
...

I need to create an Excel Template that will be stored as xml.

The template will consist of four worksheets:
Master Data
Department A
Department B
Department C

A third party program (over which I have no control) will open the
workbook and dump company data into the Master Data worksheet.
I need to somehow populate each of the Department worksheets with only
the data relevant to that particular department.

The Master Data worksheet will have the following columns
Dept Code:
Transaction No:
Transaction Type:
Date:
Amount:

I need to somehow get any rows that have a Dept Code of A into the
Department A worksheet, rows with a Dept Code of B into the Department B
worksheet etc.

I cannot have any macros as this document needs to be stored as xml (this
is the client's pre requisite).
There cannot be any blank lines in the middle of the Department A,
Department B, Department C worksheets.
There must be no user intervention required.

I had thought of populating the first 1000 rows of so of each of the
department worksheets with a formula to display the corresponding rows of
Master Data if the Dept Code equalled A but this would leave blank rows
everywhere.

Can it be done? Any ideas?

Thanks
Tony

Copy subrange without macros
 

Thanks Stephanie
But I have the answer now courtesy of Max

Regards
Tony

"squenson via OfficeKB.com" <u36146@uwe wrote in message
news:76942394c0ce1@uwe...
Tony,

I believe that I have found a solution that does not require macros, as
your
client requires. If you would consider making a small donation to my
PayPal
account I will gladly give you my solution. Please write on my private
e-mail:
stephane [dot] quenson [at] gmail [dot] com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200708/1

Copy subrange without macros
 

You're welcome, Tony.
Glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tony Starr" wrote in message
...
Max,

Brilliant! Does the job nicely.

Thank you very much.
Regards
Tony