excel order form

[rdvarona]

I have an order form that I create but it is too lengthy when it is printed out. Everything on the list prints out including the items not ordered. I want to be able to do the following. This order form contains warehouse supplies used for shipping purposes. There are over 50 items on this list however we do not always order everything on this list so if I can create a formula that will only display the item ordered when a value is entered.

Example:
Description PRODUCT CODE/On Hand Inventory/Inventory/Qty to Order
1/2" METAL STRAPS (ROLLS) 4 10 6

I would like to create an "OK" command button. If a value is entered in the onhand inventory column I would click "OK" and this form will open in another window only displaying that items that was selected. Also I would like to have an option to print.

Can anyone help???

[Andy Pope]

We really need to see your form. Is it a worksheet or a userform?

[rdvarona]

Yes, I have it saved in Excel. How can I foward it to you ??

[rdvarona]

see attached file

[Andy Pope]

To me you are mixing 2 things.
Your order form is actually your data base. You should have the items on a separate sheet and via data validation allow the user to select the item(s) that need ordering.

If you stay with what you have you can use Autofilter to only display rows with >0 on hand inventory.

Please Login or Register  to view this content.

[rdvarona]

I am not sure I understand. Can you explain a little ??

[rdvarona]

Can anyone else help me on this

[Andy Pope]

What I mean is you are mixing data with presentation.

You could reduce the page size of the current form so it fits a single printed page.

Or you can filter before printing using autofilter.

Or you could move the Data off of the presentation layer and use data validation to fill in a set number of lines on a form. See attached.

[rdvarona]

Good morning to all, I was wondering if anyone can help me with excel work sheet. It is a supply order form for a shipping warehouse. I am trying to get the order form do the following. When a value is entered then is will display that item. If value equals zero then that item will not display. Does any know how to get this done?? May be have a command button on the bottm of the page and when you have selected all of the items you want then you click on a commend button OK which will take you to another page showing only those items selected...

[Andy Pope]

Is this a continuation question?

Normally you enter product and display the price rather than the otherway around.
What do you do when more than 1 product has the same value?

[rdvarona]

Good morning Andy, Thank-you for the spread sheet. I looks great. Looks like I can use it. However how do you check the value on column that says Inventory ? When you select an item from column A then it displays a value on the column that says inventory. If i need to check that value hoe do I go abount in doing that ??
Thanks
Richard

[Andy Pope]

The Inventory column has a formula in it

G7: =IF(A7<>"",INDEX(PROD_INVENTORY,MATCH(A7,PRODS,0),1),"")

This takes the chosen description in A7 and matches it against the named range PRODS (Sheet2!A2:A42). This returned value is then used as the row index to return the PROD_INVENRTORY (Sheet2!C2:C42) value.

[rdvarona]

How did you create the drop down list to select items from another sheet?

[Andy Pope]

That's data validation using a named range as the list source.

Select the cell and use menu,

Data > Validation...

[rdvarona]

I got to the validation but how do I save the information to a file name? You have the items saved on a drop down file called PRODS. How did you do that?

[Andy Pope]

PRODS is a named range.

Check the names in the drop down Name box, next to the formula bar.
Or via menus
Insert > Names > Define

[rdvarona]

Thank-you very much. This solves all of my problems. I could not have done it with our you. God Bless