Copy subrange without macros

[Tony Starr]

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

 

[squenson via OfficeKB.com]

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

 

[Max]

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

 

[Tony Starr]

Max,

Brilliant! Does the job nicely.

Thank you very much.
Regards
Tony

 

[Tony Starr]

Thanks Stephanie
But I have the answer now courtesy of Max

Regards
Tony

 

[Max]

You're welcome, Tony.
Glad it helped.