Using Line 50 V15. I am trying to get a definitive re-order list (either within Line 50 or exported to Excel) for up to 900 products which tells me how many I have in stock after all supplier (PO) and customers (SO) orders which I have on the system have been taken into account . Sage are unable to create this report. I am able to do a data query in Excel which gives me stock code, qty in stock, qty on order from suppliers, re-order level and re-order qty. However, I cannot create a combined report which also tells me what products have been ordered by customers - I can only get what has been allocated (not the same thing if I don't have enough actual stock to allocate fully). I can also run a report - which is then saved as a .csv file - of the products on order from customers. So I have, from my data export, a complete list of my products with the above information and a separate page in the worksheet with a list of the products ordered by customers. However, this list only lists those products which have actually been ordered, not the complete list including those that have not been ordered (so vlookup doesn't work). I need to tie the two lists up, preferably automatically.
I hope I've given enough information to enable someone (anyone!) to help me do this, but if not, let me know and I will explain more separately.
Matthew Mulgrew
Replies (4)
Please login or register to join the discussion.
Thanks to you both, too from us at ExcelZone
Matthew and Richard,
This is a really heartening exchange, and it's great to see the exchange of information via this site make such a dramatic impact on someone's working life.
As chance would have it, Simon Hurst has just compiled an ExcelZone Compendium digest on working with accounts data.
If you're interested in learning more about Excel query and reporting techniques, you could spend a bit of time with some of the material Simon has pulled together, Matthew.
Kind regards,
John Stokdyk
AccountingWEB.co.uk
Why can't you VLOOKUP?
Hi Matthew
Why can't you do VLOOKUP in your main stock sheet to look for sales in the shorter Sales sheet? This would then give you a neg. fig to aggregate with your other figures.
Otherwise it's Pivot table by the sound of it. If you want to e-mail your workbook I'll have a look if you like; no charge for a look and if it's simple I'll fix it FOC as well. e-mail [email protected] .