Hello all,

I'm a newbie on this site and thought it would be easier to bounce some ideas off some knowledgeable folk rather than trying to second guess what I should do as I have been doing for probably a year now!!!!

I am, dare I say, reasonably experienced in Excel 2003 (use it on a PC) and have Excel 2011 (on a mac). I have not yet ventured into 2011 because 2003 serves its purpose well and I know my way around the menus.

I've never liked the layout of any Microsoft Office product since 2007 but thought it was time to upgrade on the Mac even though I daren't use it. We are trying to move everything across to Mac as I got tired of crashes with Microsoft Windows Vista. I'm currently running Windows 7 Ultimate on my PC and my partner is using Windows Vista. Windows 7 is very stable but it was too little too late for me hence the imminent move over to Mac.

I'm quite sure there will be some staunch defenders on here of the layout of Office Products since 2007 and I'm sure it probably would benefit me to use it but the leap scares me. I'm getting OLD!

I have never used Access and cannot understand it for the life of me. I've opened it and played around but feel I am too old to learn anything useful now.

Now that I have given you all a little insight I can get to my problem.

I have an annual stocktake coming up at the end of November but want to do a preliminary one in the next week.

Previously I have used a workbook called Vlookup Barcodes to store the barcodes and the names of items we sell (but this never included any reference to condition) and a workbook for the actual stocktake linked to the Vlookup Barcodes workbook.

So when I scan a barcode into Sheet 1 of say file Stocktake December 2009, it places the name of the item alongside in Column B but that's it.

I then manually value the stock using past invoices and this is my main issue. We probably have around 5000 pieces of stock (differing quantities, over many lines, differing conditions / locations / ages). It is very time-consuming as you can no doubt imagine.

We buy stock from various sources in lots. Last week, I sat down to try and think of the best way to address all our stock valuation issues.

I created a barcode label in Dymo software which links an item to the place of purchase, the lot number and the value of the item. This is fine when there is only ONE item in a lot.

There are various issues as I see it. A lot can contain more than one item and of varying conditions (new, used, faulty, some parts missing, etc.). Some lots contain miscellaneous but mostly a lot will contain either one item or a number of the same item. AND here's one major problem - not all items may have a barcode or a tag on it as the stock can be customer returns, end of line stock or otherwise.

Even if they all had barcodes, would it be sensible to use the barcode as a reference if the condition is variable?

Generally speaking, we pay more for an item that is new than for the same item which is used but in some of the lines we deal with supply and demand drastically changes and it has been known of late for us to pay more for used / returns stock than what we have paid for the same item brand new in the past!!!

Perhaps each item should have a unique number for stocktaking. I would welcome thoughts on this statement. I am sure there are a few people on here that have or have had the same problem

What I would like to be able to do is create a workbook which:-

a) ultimately, values the stock using present and past pricing data.
b) I can print off labels (from a Microsoft programme using the data from another Microsoft programme - possibly both Excel) for sticking to new stock (and existing stock) for future scanning / valuation purposes.

This will mean I have to go through every past invoice manually and enter it somewhere. Label every item we still have in stock. This in itself will be a nightmare as a lot of the stock will not have its original lot number on.

There is a lot of faulty stock which needs special consideration too. Sometimes stock gets removed from its original retail packaging and sits lying around waiting to be tested / listed / sold.

I think some kind of database / excel / vba workbook would clearly do the job. Having regard for my opening statement, how do I go about setting this up.

Maybe I have to create a unique SKU for each product (bearing in mind, it can be new, used, faulty). I don't know. I'm really open to ideas / thoughts.

We trade on Amazon and Ebay and each system operates differently.

We don't want to be able to control stock (at this point) on a daily basis. Perhaps this should be incorporate somehow but bearing in mind this would be manual adjustment as I really wouldn't know where to start with API access to Amazon / Ebay, XML and all that goobledy-gook.

Amazon fulfil some of our stock and warehouse it for us and we also warehouse any remaining stock ourselves. Therefore, we cannot label / scan what we have sent to Amazon at any time. All we can do there is record stock that has gone in and download a report of what has sold and what exists in their warehouse. Ideally, I would like to be able to send every last piece of stock to Amazon but that has its own problems.

When we send stock to Amazon, it uses what is called an FNSKU (Fulfilment Network Stock Keeping Unit) and their system allows us to apply our own Merchant SKU or it creates one for us if we allow it to.

For the same item, we could use the Custom Label in Ebay to link it to the same item / type/ condition for sale.

I have a lot of thoughts / ideas but I think it is best to let someone else have a say so I can deal with one idea at a time.

Looking forward to some thoughts.

Rhothgar