Excel help - Work ordering from suppliers

Adam123

Adam123

Adam123

Associate
Joined
11 Jan 2009
Posts
884
Hello,

When we place orders at work we have a very long spreadsheet that the supplier sends us (it contains around 500 or so items in a dirty long list broken up into slightly smaller categories (list contains product number, description, cost and units required))...
We usually only order around 30-50 items and we do this by phone, so it takes longer than it should to scroll through the list looking for items that we need.
Is it possible to some how...

Have a different sheet/area on the page that will display all the items that we want to order in a nice neat list?

Its been a very long time since I played around with Excel so I am requesting the help of a few knowledgeable people :-)

Thanks

Adam
 
Ok just to clarify are the orders (quantities) already in the spreadsheet when someone phones up and as such has to scroll through the list looking for them?(search quantity cell for greater than 0 and display).

Or you just want the normal things you order in a seperate list? (Copy, paste to second workbook).

If anything just using "ctrl+f" would speed it up no end.
 
Last edited:
without seeing samples it's harder to understand how the system works.

You could just select all the columns, click HOME on the ribbon then "Sort & filter" on the right. Then just use the filter on "product number" or "description", select the items you like and it will only show those items.
 
Cheers for the replies already :-)

Inserted a basic mock version ha.

So imagine this is the order sheet (but 100 times smaller)

24o8u86.jpg


How could I make it so that anything with a quantity of 1 or higher is displayed in a pretty self populating list (for example column G being product code, H description, I price, J quantity of just the items we need).

With the end result being a neat and tidy list on just the items we want.

My idea is that it will form a nice template that I can send out to use as a standard ordering sheet both inhouse and possibly to the suppliers.

Thanks

Adam
 
So yeah like the way applying a filter displaying anything with >=1 is displayed, but if it could do it by its self that would be splendid :)

...

I think that if I were to have 2 sheets set up, sheet one as the original order list and sheet 2 as a duplicate with the filter applied, that may actually do what I am after :-)
 
Adam123 said:
So yeah like the way applying a filter displaying anything with >=1 is displayed, but if it could do it by its self that would be splendid :)

...

I think that if I were to have 2 sheets set up, sheet one as the original order list and sheet 2 as a duplicate with the filter applied, that may actually do what I am after :-)
Click to expand...

Edit:

Doesn't seem to auto-update if I value is changed on sheet 1 after the filter has been applied...
 
http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

Have a look at that. Insert onto second sheet using a button in cell and just point to the first sheet.

So on button press it would perform a vlookup against the first sheet checking all column "quantity" for =>1 and update accordingly.

How I would do it anyway (bit of a noob).

Or to make a easier auto process use a change event.

http://msdn.microsoft.com/en-us/library/office/ff839775.aspx

On change if cell >=1 copy row to new sheet.

Edit: apply the change event I mentioned above to your current formula you should be good to go.
 
Last edited:
Adam123 said:
So yeah like the way applying a filter displaying anything with >=1 is displayed, but if it could do it by its self that would be splendid :)
Click to expand...

There are a few ways to do this.

You can sort the list in order of quantity and set out a print area for the items that need to be ordered (if printing a list out is what is required).

Another option is to activate filtering (I'm not sure if it would work with the one row gap seen in the example) and filter the quantity column so that only items with the selected quantity are listed.

(I'm taking an IT course which includes doing stuff like this in the excel exam).
 
what you need its create a drop down list, then for it create an lookup formula, and then times the price bythe quantity and them add up the sum

example made by in in the school
http://sdrv.ms/1aWKEoJ

for the dropdown list tutorial:
http://office.microsoft.com/en-001/excel-help/create-a-drop-down-list-HA102809802.aspx
make sure its alphabetical order before doing this, as it wont work afterwards on the lookup
to put in alphabetical order:
http://www.computerforall.ca/microsoft-office/87-office-2013/excel-2013/200-how-to-sort-data-in-microsoft-excel-2013

to create an look up formula:
http://blog.laptopmag.com/excel-2013-vlookup

then times by price by the quantity and add them all up (assuming that you know that already)

mega
 
You must log in or register to reply here.
Back
Top Bottom