Free Microsoft Excel 2013 Quick Reference

Dynamic Order Form

A month ago I started a project, and with all your help and of course my own knowledge I got the project recognized by the VP of our other location and everyone wants to jump on board and make this bigger than expected.

So first, I would like to say thanks.

Now here is the problem, we have a list of products on a spreadsheet like so

form_code collection_name function finish style_title item_number Lever Bergen Passage Polished Chrome Bergen Lever Passage 6-1 33-D006234PC Lever Bergen Passage Satin Chrome Bergen Lever Passage 6-1 33-D006234SC Lever Bergen Passage Satin Nickel Bergen Lever Passage 6-1 33-D006234SN Lever Bergen Privacy Polished Chrome Bergen Lever Privacy 6-1 AR 33-D006224PC Lever Bergen Privacy Satin Chrome Bergen Lever Privacy 6-1 AR 33-D006224SC Lever Bergen Privacy Satin Nickel Bergen Lever Privacy 6-1 AR 33-D006224SN Lever Bergen Dummy Polished Chrome Bergen Lever Dummy 33-624PC Lever Bergen Dummy Satin Chrome Bergen Lever Dummy 33-624SC Lever Bergen Dummy Satin Nickel Bergen Lever Dummy 33-624SN

So basically, they want me to make a form with drop downs:
first they choose the lever, which gives them the list of available collection names, where they choose what type (privacy, dummy, passage) then choose the finish, lastly it verifies that they indeed want that with the full item name/description and from there when they select that drop down it just shows them the item number.

The thing that makes this huge is that the product list can really go on for forever and change every day so i cant hard code the selections. it needs to dynamically read all my columns, not produce duplicates, and still sort and filter accordingly all the way to the item number. And not everything is a lever, there are 100's of options for almost every category, and potentially 20 000 entries in item numbers in total.

Please help =( i dont want to have to outsource this.


Post your answer or comment

comments powered by Disqus
I'm looking to create a form that would draw product data from one of a few hidden worksheets and place it on a user form, the user would then select quantities and choose submit. The collected data would get added to another worksheet that has the actual order form. Some of this I can do and some not, mainly the form, but I was thinking that someone may be able to point me towards some existing code so I can see how it was done.

Thanks.

Hi all:

This is my first post here. I have extensive experiance in Access, but I'm trying to do something with an Excel workbook that I can use some help with.

I'm trying to create a book that will start out with a master list of parts, which obviously is not a problem.

When a salesman sells a job, he selects what parts are necessary for this job, which would give a cost figure on what materials would cost, and the allotted labor to instal them. Still no problem.

When the job is sold, (here's where the problem begins), I'd like to have a button on the page which would select only the items on the master page which are sold, and move them to another page, by vendor, to create an Excel sheet that I can email to each vendor. This will create a separate "order forms" for each vendor

Of course, I need the resulting forms to not list all the items in the master sheet, only the items needed,

Thanks!

ok. here's a project i'm working on to price up the cost of products for a business.

Currently

On an order form the customer will be able to enter the width and projection of several products that they want to order. The way these products are priced are in a Matrix of Width over projection. The current system i have designed is:

1. The pricing Matrix's have all been put into one big table and given a unique id per product matrix.
2. An advanced filter has then been run and and it extracts the appropriate matrix and copies it onto the process sheet.
3. An Index/Match formula is used to find the price for the inputted width and projection. It does this by finding the intersect point of the width and projection on the table.

Currently this will be put into a macro and assigned to a button.

The Problem

The problem is that up to 15 (or possibly more) products need to be able to be ordered in one order form. With the current system it means there will be a lot of Advanced Filters and there will be a macro button that will need to be clicked after every product order (and they're could be 15 or more). Obviously this isn't very professional, it is time consuming and must be the hard way of doing it.

I was wondering what over systems that could be used for this sort of thing. The more solutions there are to this problem the better. Attached is the file. If you do post a solution it would be preferred if you could also post a file with a working example in. I find it much easier to learn the solution if i can see it working.

Thank you all in advanced.

My wife is need of a form where she can select a product from a drop down menu (or any other way to select one type of product out hundreds), but when the product is selected the number of those items in the inventory is reduced by one, and the item is placed onto an order form or invoice. Can anyone help me out or point me in the right direction? Thanks!

Greetings all,

this is my first visit to MrExcel. The reason being i know next to nothing about excel but find myself in a position where i need to acquire some knowledge to increase my productivity @ work.

the situation is thus:

When ordering products from a regular supplier, we would copy/paste or just type and item description onto a blank 'form' template, then either copy/paste the corrisponding item code, or go look it up in the price book then manually enter it.

i've managed to take all the previous order data from the last 3 years (100+ seperate order forms)

i've gone through and deleted all the duplicate entries, and compiled it into one long list comprised of 2 columns.

Column A contains the Product Codes e.g. "W30752" or "001697"
Column B contains the product Name e.g. "40mm Pipe" or "Cleaner 2.5kg"

what i want to do, but can't seem to figure out how, is to have the blank order form with 3 columns, 'Product Code' Product Name' and 'Qty"
in the 'Product Name' Column, in each row, have a drop down list the links to the list of products on another sheet.

I then want to be able to scroll through the list and select a product and have the product code appear next to the name in column A. i will then manually enter the qty required.

any help would be appreciated, i think i need to use either vlookups or some form of data validation but i'm not sure.

thanks

Hi all,
I am wanting to create an order form so we can order all of our stock and keep track of it all.
The information the spread sheet needs on it is
Order Number > Supplier > Customer > Items required > Product Code > Date Ordered > Date Received > Items on back order.

I would like to have the Order number and Date generate automatically.
I would also like this information to be able to go straight through to a word document which would ideally have a fax header and have all the required information automatically on the fax header such as Order number, supplier, items requires, product code and date ordered.

Any help on this would be much appreciated!
Thanks,
Paul.

I need to create a Sales Order form that will be put on the company network. I assume I should create the form in Excel, unless any one advises a better application. I will probably be using data validation, drop down lists, conditional formatting, lookups, macros and VBA.

Currently we have different versions of the order form depending on the product. I want to combine all of these into one file.

Some portions of the form ("boiler plate") will be common to all orders: customer name, sales terms, etc.

Some portions of the form ("details") will be different depending on user inputs. I want only the needed portions to be visible.

In the end, I would like to have a one-page print out.

Does any one have suggestions, ideas, advice, or examples to offer before I begin this project?

Does it make more sense to break the form into parts across different sheets or different ranges on the same sheet, in order to show or hide parts depending on user input?

Thanks to all,

GL

I have an order management system that allows me to import xml documents.

I have a spreadsheet I am using as an order form for my salespeople.

I was wondering if there is a way to format my "order form spreadsheet" I
get back from my salesperson and save that as an xml document by applying
some type of mapping.

If this is doable, how to I map the document to know what/how to export the
data into logical xml format.

Here is sort of what I am looking for:

Cell A1 is the Customer Name
Cell A2 is the Customer Street Address
Cell A3 is the City
Cell B3 is the State
Cell C3 is the Zip Coce
Cell A5 is Item No. 123
Cell B5 is the Qty Ordered
Cell A6 is Item No. 124
Cell B6 is the Qty Ordered

I would like something like this.

Joe Smith
123 Main St
Atlanta
GA
30303

123
1

124
2

Thank you for your time.
Regards,
Diane

I have been working on a product order process, The first worksheet (input
sheet)has a listing of products down column C (Aprox 200 rows of products),
column D has a yes/no and/or other criteria for each product.

The next sheet is where I am trying to create an order form that we can
print out and send to our clients. On this form I want to only print the
products that were selected "Yes" on the first sheet or meet certain criteria
set on that input sheet.

Ex:
Product Family 1
Product 1 NO
NO OTHER PRODUCTS SELECTED IN THIS FAMILY
Product Family 2
Product 1 Yes
Product 2 Network
Product 3 NO
Product 4 20

what I want on my output would be to just display Product family 2 (because
no products we selected yes in family 1) and then only display the relevant
ordered products. so it would look something like this

Product Family 2
Product 1
Product 2
Product 3
Product 4

Sorry for the long explanation but don't want to waste your time on the
wrong question or process, I'm open to just about any suggestion.

I would like to create a sales order form that calculates a total of the unit
price, sales tax and S&H. S&H varies according to the number of units
purchased. I am using excel 2003.

I am attempting to create an order form with incremental order numbers. When
I print a number of pages I would like each page to have a new number,
increased by 1, from the page before.

I can't get back to my thread for some reason, and since it's your order
form Rag, I am hoping you or someone can help me again...

Here's the things I need to do and can't seem to figure it out...

1. On the drop down list, some of the description list are very long,
they have to sometimes choose 100 items, I would like the customer to
be able to type in a few letters and have the closest item come up...i
thought it was auto complete but must not be because it doesn't work
for me.

2. How do I make a column recognize the next pattern and complete it. I
haven't done this in ages and I have tried several ways but it wants to
go to the next cells as =A2 =A3 =A4 instead of F1 G1 H1 etc.

=A1
=B1
=C1
=D1
=E1

3. How can I make it where Column 1 if changed will clear out column 2
on the order form to start over, because if I select a different
category than previously selected, it will let me keep the wrong
description for that category unless i tab over and change it too.

An Example...

Selecting Category "pens" then description retractible, then change my
mind and decide to enter markers, it keeps retractable until I change
it, even though retractible is not an item for markers.

Thanks in advance! The order form looks awesome!

KL

--
KatyLady
------------------------------------------------------------------------
KatyLady's Profile: http://www.excelforum.com/member.php...o&userid=23841
View this thread: http://www.excelforum.com/showthread...hreadid=375089

I have generated an order form in Excel 2003. Each time the form is opened I
would like for the cell entitled, "order number", to be filled in
automatically with the next number in the sequence so that we do not
duplicate numbers. Any ideas?

I'm not exactly a novice with Excel, but there are quite a lot of
functions I haven't yet needed to use in the various positions I've
held. What I'm trying to do now is very, very different from anything
I've done before, so I hope this is the proper forum.

I have been handed the opportunity to redesign the processes by which
our Traffic office runs. What I would like to do is create an Excel
order form, which could then be used to automatically generate a Bill
of Lading. I will need to pull specific data entered by the customer in
the form to a template worksheet in the same file. I'm not sure how to
request that Excel retrieve this data to populate specific cells, nor
am I sure how best to go about designing the form to allow this to be
done. I'm really not familiar with the limitations of Excel in this
regard, but I feel this is the best way to go about this project and
will make future transitions to better software easier.

Does anyone have any suggestions or ideas? I'd greatly appreciate the
help. Thanks.

J

--
jpletting
------------------------------------------------------------------------
jpletting's Profile: http://www.excelforum.com/member.php...o&userid=27108
View this thread: http://www.excelforum.com/showthread...hreadid=466244

So I can search for the customers name once found insert it into my order
form- Is this possible? I can do mearges but am unsure how to do this

i want an order form for the public to fill in at a trade fair so they can
order thier own goods from me

I have been racking my brains with this all afternoon, and have searched
to no avail... I'm sure there is an easy solution!

I am trying to find a formula for an order form, so I can enter the
code in column A, and then have the name of the actual product appear
in column B. So for example if I enter "BL" in column A, I'd like "Blue
Loop" to appear in column B.

Thanks

--
lolligirl
------------------------------------------------------------------------
lolligirl's Profile: http://www.excelforum.com/member.php...o&userid=19987
View this thread: http://www.excelforum.com/showthread...hreadid=345760

help please....

i am trying to create an order form. normally this would be easy
enough...item price x quanity etc, but there are some other variables.
allow me to explain....

1. let's say we are selling products 1 2 3 etc all the way to 250.

2. let's say that products 1 through 50 only should display options (styles)
of a b c d e f and g, while products 51 -100 should display options (styles)
of h i j k l m etc.

I was hoping the two things above could be in drop down boxes... so..for
example..a customer orders product 22, only the a to g options or styles are
shown.

Now..to complicate things a buit further, each style would have a different
price associated with it.

I was thinking somehow to have the products listed in a drop down box...and
depending on the product selected, would evoke some sort of condition (If
product = 22, display options a-g..where the options or styles are maybe in
an external file?

Does this make sense?

Hope so

Any ideas would be greatly appreciated. And please be gentle...I'm a
newbie...but then you probably know that already by the way I asked

Thaks in advance,
Abe

I have a custom/dynamically generated form with X numbers of textboxes where X is number if elements from WorkSheet1. It also generates an Update button and a Cancel button.

The form is generating fine. For instance if the number of cells with value in WorkSheet1 is 10, then the form generates 10 textboxes and it puts cell value as the value of the textboxes.
Dim MilestoneSheet As Worksheet
Set MilestoneSheet = Worksheets("MIilestoneDates")
Set MilestoneDate = milestoneSheet.Range("A1")
        
While (MilestoneDate.Value <> "")
Dim textbox As Control
Set textbox = MilestoneForm.Controls.Add("Forms.TextBox.1")
Dim MilestoneDate As Date

'Set textbox properties
With textbox.Height = 20
.Left = 2
.Top = 6
.Width = 90
'Set the name and value of the Textbox
.Name = "TextBox" & MilestoneDate.Row
.Value = MilestoneDateEnd With

Set MilestoneDate = MilestoneDate.EntireColumn.Rows(MilestoneDate.Row + 1)Wend
It starts with A1 of WorkSheet1 and goes down one cell a time and adds that cell value to the textbox value till it reaches an empty cell.

What I need to do now is on the form the value of any textbox can be changed by the user and once when they click the Update button it should then change only those cells in WorkSheet1 that had its textbox value changed by the user.

For instances if there are 10 cell with values and the form generated 10 textboxes, it should then change the value of cell 3 and 7 when the value in textbox 3 and 7 are changed by the user in the form.

Can I get some in solving this issue. Thanks in advance.

hi all,

I am trying to use two drop down lists in an attempt to populate an order form -

the drop down boxes are called

SUPPLIER - this contains all the names of the suppliers I use

STOCK DESIGNATION - this contains two phrases CONSIGNMENT STOCK and BOUGHT STOCK

I want to be able to select a supplier and a stock designation and then for all matching items in the data sheet to auto fill an order form -

Example

in supplier i select PLUMB CENTER and in stock designation i select CONSIGNMENT

the fields in the order form are headed ITEM DESCRIPTION, STOCK CODE and RE-ORDER QTY - i would need all the instances where PLUMB CENTER and correspondes with CONSIGNMENT STOCK to then fill the 25 or so lines of the order form automatically

please help - desperate to get this working

[Update: I have mentioned in the file not to use macro, but since I query can only be solved through use of macros, kindly ignore that comment and suggest solution even if there are macros involved. - thanks.]

Dear All,

Hope everyone is fine.

I have an decided to work on an office project where I could automate the process of creating orders for myself and my team. The reason I want to do this is because a lot of times we have issues such as the discounts are quoted wrong and the pricing and the delivery terms are different for the same customer sending repeat orders as they get handled by more than one sales person.

I have attached my excel file as a design reference to show you what the final file would look like. Just a note that I'm not an IT expert and have done the basics only. If someone could direct me to the right source or a similar example where I could download the file and make the changes as per my requirements.

Some of my office laptops are still on Excel 2003 & Windows XP.

My File: Automated Order form - 20.02.2012.xls

Thanks & regards
-----------------
Ecronic

I have a good basic knowledge of Excel, but I'm having problem with my order form. I need different width's in my form. Not one width all the way down my form. Is there a way to have two Excel forms in one or how do I implement this?

I have been racking my brains with this all afternoon, and have searched to no avail... I'm sure there is an easy solution!

I am trying to find a formula for an order form, so I can enter the code in column A, and then have the name of the actual product appear in column B. So for example if I enter "BL" in column A, I'd like "Blue Loop" to appear in column B.

Thanks

Hello,

I have some data that I want to autogenerate onto and order form.

I am unsure how to do it. I am a complete newbie to the VBA code so please be patient with me.

I have attached a file so you can see what Im trying to acheive.

I would like to have it skip over any cells that show a zero value

to autofill the "order form".

Hope Im making sense here.

Thanks in advance.

Kelly

Here is the attachment. test.xlsx


No luck finding an answer? You could always try Google.