N
Nehvun
Hi all, am hoping someone can help me out, I am at a dead end here..
Also hope this is the right place to post this?
I am working on a stock order planning project using Excel 2003, and have
run into difficulties (a.k.a lack of expertise/knowledge)
The data I am using for manipulation consist of three files, exported from
our database - they are stock.xls, po.xls (purchase orders), and so.xls
(sales orders).
Stock.xls contains info on itemcode, qty in stock, and which warehouse(s)
the stock is in. One item in two warehouses will result in two rows of
information. There are only three warehouses.
po.xls contains info on itemcode, est time of arrival, qty arriving, and PO
number. Will have multiple items on one PO, and multiple PO's per item.
so.xls contains info on itemcode, est time of delivery, qty shipping, SO
number and customer name. Again, multple items per SO, multiple SO's for
items, but also multiple SO's per customer (of which there are also only
three)
My requirements are to have a spreadsheet (call it planning.xls) with
multiple sheets, each sheet relating to a particular group of unique items.
The sheets and items are pretty static, so any changes/additions/deletions
to these can be done manually.
For each item, I then need an initial indication of current stock at three
different warehouses, followed by the sum of these stocks for each item.
So far no problem..- have split the stock.xls file up into one sheet per
warehouse, each warehouse column in the planning.xls file then makes a lookup
based on item code to that warehouses sheet in the stock file - results are
correct.
The next set of requirements is where I fall over - I then need to display,
by year and month and then PO number, all incoming stock, followed by All
SO's, arranged by year, month, customer and SO number, with running total of
anticipated stock at the end of each SO month. Multiple PO's or SO's per
month/customer need to be 'groupable' and have total for month, expand to see
each individual PO or Customer and SO.
Getting month() and year() from est time of arrival/delivery enabled me to
create the pivottables I mention below, but my real problem lies with (or so
I think, correct me if there's another way) dynamically creating
columns/headers based on whether or not there is a PO or SO for a particular
item? And how to do it?
How to do, or is there an easier way - the pivottables I created from the PO
and SO files (which gives me the info as I need it displayed - row fields are
itemcodes, column fields for PO.xls are arranged by year/month/PO number with
qty arriving as data section, for SO.xls row fields are also itemcodes,
column fields are arranged by year/month/customer/SO number, with qty
shipping as data, but it does for ALL items, not split as I need them)
How to lookup on both itemcode , year and month (and customer for SO's) and
return and insert PO or SO number and qty's of any items, IF they actually
exist on any current orders?
Many thanks for any pointers/assistance offered,
Andy
Also hope this is the right place to post this?
I am working on a stock order planning project using Excel 2003, and have
run into difficulties (a.k.a lack of expertise/knowledge)
The data I am using for manipulation consist of three files, exported from
our database - they are stock.xls, po.xls (purchase orders), and so.xls
(sales orders).
Stock.xls contains info on itemcode, qty in stock, and which warehouse(s)
the stock is in. One item in two warehouses will result in two rows of
information. There are only three warehouses.
po.xls contains info on itemcode, est time of arrival, qty arriving, and PO
number. Will have multiple items on one PO, and multiple PO's per item.
so.xls contains info on itemcode, est time of delivery, qty shipping, SO
number and customer name. Again, multple items per SO, multiple SO's for
items, but also multiple SO's per customer (of which there are also only
three)
My requirements are to have a spreadsheet (call it planning.xls) with
multiple sheets, each sheet relating to a particular group of unique items.
The sheets and items are pretty static, so any changes/additions/deletions
to these can be done manually.
For each item, I then need an initial indication of current stock at three
different warehouses, followed by the sum of these stocks for each item.
So far no problem..- have split the stock.xls file up into one sheet per
warehouse, each warehouse column in the planning.xls file then makes a lookup
based on item code to that warehouses sheet in the stock file - results are
correct.
The next set of requirements is where I fall over - I then need to display,
by year and month and then PO number, all incoming stock, followed by All
SO's, arranged by year, month, customer and SO number, with running total of
anticipated stock at the end of each SO month. Multiple PO's or SO's per
month/customer need to be 'groupable' and have total for month, expand to see
each individual PO or Customer and SO.
Getting month() and year() from est time of arrival/delivery enabled me to
create the pivottables I mention below, but my real problem lies with (or so
I think, correct me if there's another way) dynamically creating
columns/headers based on whether or not there is a PO or SO for a particular
item? And how to do it?
How to do, or is there an easier way - the pivottables I created from the PO
and SO files (which gives me the info as I need it displayed - row fields are
itemcodes, column fields for PO.xls are arranged by year/month/PO number with
qty arriving as data section, for SO.xls row fields are also itemcodes,
column fields are arranged by year/month/customer/SO number, with qty
shipping as data, but it does for ALL items, not split as I need them)
How to lookup on both itemcode , year and month (and customer for SO's) and
return and insert PO or SO number and qty's of any items, IF they actually
exist on any current orders?
Many thanks for any pointers/assistance offered,
Andy