Line 50 data export to Excel

Line 50 data export to Excel

Didn't find your answer?

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.

John Stokdyk, AccountingWEB head of insight
By John Stokdyk
11th Jun 2009 13:51

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

Thanks (0)
avatar
By MatthewMulgrew
27th May 2009 16:00

Job Done
Richard

One word -WOW. This is the first time in 4 years that I have been able to compile a single list that gives me the answers. My internal business street cred just went up dramatically!
As a (simple) bookeeper, it is great to know that there are people like you around willing to help. I will post this e-mail at accountingweb to give you a public thank you.

Thanks again

Matthew

Thanks (0)
avatar
By MatthewMulgrew
27th May 2009 13:52

Vlookup
Richard

On the complete stock list, my first stock code is, for example, 10212. On the list of customers orders, the first item is CLI. Doing a vlookup on line 1 therefore brings up #N/A. This continues until the complete stock list finds CLI OK. Thereafter, Vlookup finds the nearest equivalent, so all of the data from theron is wrong. I am e-mailing you the spreadsheet separately with eternal thanks for at least having a look at it.

Matthew

Thanks (0)
avatar
By Richard Willis
27th May 2009 13:12

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] .

Thanks (0)