Auto-adjusting Stock when Selling an Item and auto-fill values related to that item

Hi Guys,

First time writing a post here, first of all let me say sorry in advance for my rudimental English.

I run a very small business, online retail shop.

All my database is done on excel and i'm trying to improve it to make it more reliable and easy to use.

What i'm looking for it's a solution to the following problems:

Every time i sell an item i copy/past from inventory Sheet the data related to that item: EAN,ITEM,Selling Price,Shipping Costs and Profit.

The solution i'm looking here would be for example define and link all this cells(EAN,ITEM,Selling Price,Shipping Costs and Profit) to the EAN code, so every time i want to add a new order to my database i would just need to paste the EAN(A12122 in the example) in the first cell(A4) and the cells F3 to I3 would be automatically filled with the linked data to the EAN Code(A12122).

I tried with the "define name" tools, selecting the data ITEM,Selling Price,Shipping Costs and Profit and naming it A12122, then tried to paste in other cell =A12122 but didn't succeed.

Thank you guys in advance for help, i'll upload a very simple example.

Thank you etaf for help.

What i want to do it's somehow link the inventory!B10:G10 to the item code A12122 so every time i want to insert a new sell i would just write A12122 and have Sales!F3:I3(or some other location) automatically loaded with inventory!B10:G10 data. Now i'm using CMD+F and manually copy/pasting inventory!B10:G10 to Sales!F3:I3.

Is it Possible?

Thank you once again.

Thanks's man!!! Work's like a charm! :D You are a pro!

Just one more thing to be perfect:

I would like it to every time i sell A12122 item, automatically would remove 1 stock from Inventory!G10 and so on for other items.

So would show on the example 34 items in stock.


Thank you for this great help.

templateSales 2014.xlsxTemplateInventory2014.xlsxtemplateSales 2014.xlsxTemplateInventory2014.xlsx



Hi again.

Sure i'll contribute for your reputation, you deserve it



All your tips work very good, now i'm having just 2 issues:

I will upload here the 2 workbooks i'm working with, one for orders and inventory and other for sales.

So the problem now it's that i can't link the stock sold on the workbook Templatesales Cell J5 to all months in the workbook TemplateInventory row I (where i'll write the sold item code.

Now i have it linked to =COUNTIF([TemplateInventory2014.xlsx]January!$I$4:$I$235;A5) and it works but i can't link it to all the sheets on that workbook, i get an error. I tried like this:


=COUNTIF([templatesales2014.xlsx]January!$I$4:$I$235:[templatesales2014.xlsx]February!$I$4:$I$235:[templatesales2014.xlsx]March!$I$4:$I$235:[templatesales2014.xlsx]April!$I$4:$I$235:[templatesales2014.xlsx]May!$I$4:$I$235:[templatesales2014.xlsx]June!$I$4:$I$235:[templatesales2014.xlsx]Jully!$I$4:$I$235:[templatesales2014.xlsx]August!$I$4:$I$235:[templatesales2014.xlsx]September!$I$4:$I$235:[templatesales2014.xlsx]October!$I$4:$I$235:[templatesales2014.xlsx]November!$I$4:$I$235:[templatesales2014.xlsx]December!$I$4:$I$235;A5)

Or like this:

=COUNTIF([TemplateInventory2014.xlsx]January:December!;A5)


I guess this is a simple one for you!



Other thing it's how i'm adding new stock and orders to the inventory workbook.

Simple example:

I order today item A12344, 10 pieces at 1€ each.

Next week i have just 5 in stock so i order again item A12344, 10 pieces but now because it's not available with the same supplier and the other one it's more expensive/cheap or because the supplier decreased(or increased) the item price, i'll buy it for 2€ each.

In my Workbook column G, the price shown will be before the second order 1€ but when i add the second one it displays 0,75€ G5.

So this brings me a problem, the profit calculated will never be accurate because i'm not calculating the actual single item price but an average price of all my orders.

Would not be a problem if the prices would be the same on all orders but this is not realistic in a long term.


Thank's for help.

That formula doesn't work Gives me a pop up message saying the formula contains an error.


Right now i have 400 parts nr's in stock.

I got it to work doing the changes manually.

Now i have just the prices issue.

Hi.

This is getting too complicated now for someone who has just basic skills on excel.

I don't have the knowledge to put your tips to work.

And i'm figuring out that this is not gonna work like i have it and not only on the item prices. Example:

I started to colect my sales data to this workbooks since November. January comes discount season and i lower item A57681 50%. When i change the selling price the workbook will update all the cells referring to the selling price cell, (November and December sheet) and not just from the day i change the price on.