Creating A Purchase Order Form That Will Reduce Time Spent Adding?
Apr 18, 2013
I'm creating a Purchase Order Form that will reduce time spent adding in contact details.What I'm looking to achieve is a form that will be printed with a few formulas allowing sections of the form to be filled out automatically once a singular company name (chosen from a drop-down list I created, currently with a 'Combo box ActiveX') then the rest of the form is filled out accordingly.
At the moment it's a bit of a mess, not too sure where I'm meant to put the ranges.There are currently 2 sheets - Sheet 1 with the form, Sheet 2 with all contact information.
I'm trying to create a very simple order form. It's been a while since I've done this in Excel, and I couldn't find an answer when I searched. (I may be a lousy searcher, though.)
I want the user to enter a quantity in Column E of the "Common Items" worksheet, and have all the rows with quantities copied to the "Order" worksheet. (On the "Order" copy below, I just did a copy/paste to show the desired effect.)
I'm pretty sure this can be accomplished with an If/Then, but I'm lost! Optimally, they'd enter their quantities, click on the Order sheet and hit print.
I have a Purchase Order template that I created in Excel and an Excel Customer spreadsheet. I am trying to get the Customer Name, Address, City/State/Zip (all found in different columns on the customer spreadsheet) to autopopulate on the Purchase Order when the matching phone number is entered. New to advanced formulas but looking for something like: if the phone number on the purchase order equals the phone number on the customer database, bring over the Customer info above to the appropriate cells on the purchase order.
I have been sent a price list from a supplier and need to create an order form that can be populated from the price lists. I have to pages of price list as well. I will also be adding to these as I start getting other supplied product and need my client to be able to generate a order from these in the simplest way possible. Again I hardly use excel and these docs are all sent in excel format.
firstly i have attached said spreadsheet 1st sheet is named purchase order 2nd sheet is named purchase order numbers
upon opening the spreadsheet i would like on sheet purchase order to automatically input the next available number from column a in sheet purchase orders ( in this case next available is st010 and put that into cell k12 on sheet purchase order
i then want to enter the relevant data in sheet purchase order cells b16 , b23 , g23 , i23 and d28
i then want to click a button with macro attached to transfer that data in to sheet purchase order numbers
I trade the stock market and am trying to get excel to automatically calculate the required quantity of shares to be purchased in order to achieve a particular breakeven point. Question: How many shares required at a price of 37.30 to achieve a breakeven of 36.24 when I already have 8000 shares at a price of 34.73? I'm looking for the formula to solve this not just the answer.
I need to create a purchase order page whereby each item is then transposed into seperate sheets detailing the product along with price etc. I need the po number to be automatically generated to follow on from the previous therefore giving us a continuous po thread. I also need the purchase order as typed to be saved into a seperate sheet / file each time so that the po is always blank when someone else creates a new post
I am reposting this because I do not think I did a very good job explaining what I am trying to do.
I am at a total loss on how (if it is even possible) of how to do this. So what I have done is tryed to break down each step. If someone could even get me started in the right direction many of the steps are redundant and I could work on that part myself.
I am attempt to create system to match parts that are alike in a single project, so I can create a material ordering list. This is just one step in the process (the hardest one) I will take the returned data and use it further in the process to create the actual material list. I have 2 worksheet of Data "PartsNeeded" and "PartsAvailable" with a 3rd sheet "PartsFilled" as my report Sheet.
Attach is the sample data along with a "notes tab"
I need to find out the total time an issue was 'open' (not resolved) during business hours. Issues come in and resolution time is based off of a priority. Priority 1 should be resolved in one hour, Priority 2 should be resolved in 4 hours, Priority 3 should be resolved in 1 business day (8 hrs), Priority 4 should be resolved in 2 business days (16 hrs), Priority 5 should be resolved in 5 business days (40 hrs), Priority 6 should be resolved in 10 business days (80 hours).
I have start dates/times field in a column (mm/dd/yyyy hh:mm) , a resolution dates/times field in another column (mm/dd/yyyy hh:mm) and a priority in another column (1-6). I need to calculate the time an issue is in open status (not resolved) based on it's priority but I need for it to take into consideration business hours (Monday thru Friday...7 am to 7 pm)...and not calculate time outside of 'business hours'.
We have a specific time frame for WIP and to purchase new supplies before they run out.
I've setup a worksheet with the various materials and time for each. With the help from past post, I was able to return the time frame before our next purchase.
Have a file of 7 mb having 100 columns and unlimited rows (user defined- approx 500). At all these cells in that area, I refresh (copy/paste) the same formula for all cells, read as:
Refresh is necessary bcs I insert/delete/sort rows.
In order to reduce file size and instead of saving all these formulas into the approx 5000 cells, I would prefer if I could have a function in VBA and call it (with copy/paste) with a smaller formula at all these 5000 cells as: [= myfunction], so as to have this simple formula in each of 5000 cells, hoping that this will reduce size.
How can I write the long formula above to a function in VBA?
Currently i have in two columns the arrive time and then the depart time. i need to work out how long was spent at the place e.g(10:00am arrive - 11:25 depart time which gives me 1 hour 25 minutes at the point) from there i then need to subtract 1 hour from the 1 hour 25 minutes and then multiply the remaining time (25minutes) by 65
My team and I are attempting to track how much time we are spending in each Excel workbook we create (all workbooks are created from a single template). The problem is that we are often jumping back and forth between different workbooks throughout the day so manual entry methods are neither accurate or practical. Is there a way, with macros or formulas, to embed a time tracker in the template workbook that would only record active (when workbook is selected) time in the workbook?
I am trying to add a leading zero to sum up the total time spent on a project. the data came back in this format :00:00 which does not allow me to sum up. I changed the format to HH:MM:SS but that did not add the zero.
I have a requirement of calculating time spent on ticket, basically a difference between the time a ticket was logged and when it was resolved.
First, working days are Saturday through Wednesday, i.e weekends are Thursday & Friday.
Second, there are different resolver groups or teams which work on a ticket. These teams have different working hours, some work from 07:30 to 15:30 on weekdays, and others work 24/7. Each ticket will be assigned only to one resolver group.I also need to consider holidays.
So, my requirement is to check the resolver group for each ticket, and decide the working hours based on it. After this check has to be done for holidays, and then difference between logged time and resolved time needs to caluculated in minutes.
Tickets can be logged at any time of the day / week, it may not always be logged during working hours only. in such cases calculate should consider next business day / hour as the start time.
Raw data for tickets is available in =Sheet1!$A$1:$E$21
Is it possible to create a drop down menu where additional information can be added then tallied up in a separate table?
I would like to create a menu representing "tasks" where an amount of "time" can be designated per menu for my employees to select and fill. I would like this information to then be tallied elsewhere so I can keep track of our efficiency.
I've attached the excel file to give a better idea of what I'm trying to accomplish.
I have a spreadsheet I use to keep track of weekly sales patterns and use for estimating the amount of a product I would need to order taking into account what I would expect to sell in a given week and what stock I have at present. On the example I've attached, I show where I enter my storeroom count figures, which are organised by supplier and the position in which a particular product appears on the supplier's order form. I have a page which lists the orders by suppliers and which are used to place the orders by e-mail or telephone.
At present I have each supplier section of the order form directly linked to a cell on the storeroom count as per columns K to M on the attached file. However, this means that as products are de-listed by suppliers and extra products become available, I have to edit the formula in each cell as the products now appear in a different position on the storeroom count and may otherwise end up on the order form for a different supplier. I would like to set it up so that I just have to select the supplier name and the table below will automatically fill with the required info, in order of the position they appear on the supplier's form. I'm struggling to combine vlookup and hlookup. Is there a way to do it or do I need to rethink?
I am a big fan of huge files (30MB+) all interlinked with array formulas ...and lots of them. The problem is...one calculation take literally 5 hours. (I have one file I try to calculate since cca 4 hours now...it's at 63%)
I keep getting extra memory with no visible improvement on the processing time. Is there a trick out there that I can use? Something is clearly wrong.
I feel I will soon have to give up Excel, or my formulas, or my job ...
I need to create a form that if you were to enter a name in the text box it would fill all the information across that row into other boxes on the form. Basicly if I type John Doe in the text box it would look in col A for John Doe and then put whats in the cells on that row into different boxes on my form. I have tried searching but I have had no luck. Im very new to forms so this is a great experience.
I have been building a spread sheet data base which I think is pretty simple.
It now has quite a few formula's and is taking time to calculate when I add data.
I have tried to simplify some formula's and get rid of complex one's that can be replaced with simple ones... Is there any advice or suggestions for reducing the calculating time..?
I have a problem where I extended a formula down to over 40,000 records which has increased the file size substantially. I only need it to scroll down to a few thousand rows now that I realized that there is alot less data to populate the worksheet. Is there any way to get it back to a scroll range that is more modest in size?
I have a list of some 200 odd items on one sheet that is my price list; what I would like to do is have another person - who may only have very basic, or virtually no excel skills at all, be able to select items from the price list and have each of those items go to the top of an order form on another page.
It needs to be automatic, and anybody that has only basic excel should be able to use it.
I have a list with 3 columns, p/n, discription & amount what i want to do is when i enter a amount in the amount column, the p/n, discription and amount are filled in, in the order screen.
see attachment for a small version of what i want.
I created a user form to get information to be added to a spreadsheet, and then later needed to add a few more fields. Now when you tab through the fields, they are out of order. For isntance it will tab down through the first three fields, then flips to the second column of fields, then back to the first again.
Is there a way to setup the order in which fields are tabbed? (Note: I am refering to the tab key on the keyboard being used to move between fields, not tabbing a form to create multiple pages.). How do I add a horizontal rule to the form? I dont see it in the toolbox...
I have started to create a sales order. I have three columns that I will be working with. I have three lists in a separate worksheet (worksheet 2) in the same workbook. Column A has part numbers. Column B has descriptions of the part numbers in Column A. Column C has prices for the parts in Column A.
I have created a drop down list in Worksheet 1 from the list in Column A in worksheet 2. What I want to do is somehow link column B and C to Column A so that when an item is chosen from the drop down list, the information will pull through.
I have some VBA (using Excel 2007) that deletes and re-creates a bunch (20-ish) checkboxes in a spreadsheet. Since i'm using the controls-based checkboxes, Excel defaults them to object names of "CheckBox1, CheckBox2, CheckBox3, etc" as I create them the first time around.
Later on, i clear all checkboxes and implement the SAME checkbox-creating code to re-create all or some of the checkboxes, but Excel doesn't number them in order the second time around! I end up with something like "CheckBox2, CheckBox19, CheckBox3, CheckBox12, etc."
What could be causing my code to create things out of order the second time around? Is there a way to reset Excel's Checkbox-numbering iterator?
The code i'm using to create checkboxes is:
Dim x As Long Dim BoxCell As String
BoxCell = "" x = 0 'j gets passed into the sub as a "Long" somewhere between the values of 5 and 35
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 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.
