Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



"change Order Form"

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Does anyone have a change order template that I can review?


View Answers     

Similar Excel Tutorials

Use a Form to Enter Data into a Table in Excel
You can enter data into a table in Excel using a form; here I'll show you how to do that. This is a great feature ...
Input Form to Get Data and Store it in Another Tab in Excel
How to make a user input form in Excel and have the data stored on another worksheet at the click of a button. This ...
Change the Order of Tabs in Excel
How to change the order of the tabs in your spreadsheet. This is rather easy to do and makes your spreadsheets much ...
Display Numbers, Dates, & Times in the Correct Format in Excel
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...

Helpful Excel Macros

Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
- This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Sort Worksheet Tabs - Ascending or Descending Order
- This macro will sort all of the worksheets in the current workbook. It can sort in ascending or descending order. This
Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a

Similar Topics









Hi I have the below piece of VBA code which works very well. What it does it calls a image from a worksheet called "Pictures" based on a selection make from a drop down list in cell A9 on worksheet "Sale Order Form". It does all of that perfectly, but I need to change the code so the image is display on both the "Sales Order Form" worksheet like it does now, and on another worksheet called "Customer Sales Order Form" in the same position.

To sum up I don't want to change what this code is doing right now, all I want is to add on to this code to have it display the image both on the current sheet and a second sheet called "Customer Sales Order Form".


Is there anyone out there that can please help me.

Thanks in advance Dave

Example Workbook

example spreadsheet.xlsm

The Code



Please Login or Register  to view this content.



I want to create a template in Excel for a change order system. Every time I have a new change order I want it to be numbered. I want Excel to automatically keep a log of all the changes orders to date with change order number, date, title, etc.

Please help.

Thanks!


Hi guys,
I hope you can help. I use excel to retrieve information from Synergy through ODBC. I have several sheets for each day of the month. What I do at the begining of every month is to edit every query for every day of the month and update the retrieval date and times. It is quite tedious. I've been doing it for 2 years and I would like to know if there any macro/script that can help me have the dates entered dynamically or with little user interaction. Here is the SELECT statement from the query ran

Code:

SELECT "NO:Release Order"."RO #", "NO:Release Order"."RO Type", "NO:Release Order".Category, "NO:Release Order".Status, "NO:Release Order"."Creation Date", "NO:Release Order"."Date & Time of Work", "NO:Release Order".Function, "NO:Release Order"."Job Type"
FROM "NO:Release Order" "NO:Release Order"
WHERE ("NO:Release Order".Category='SF') AND ("NO:Release Order".Severity='Critical') AND ("NO:Release Order".Status='Approved') AND ("NO:Release Order"."Creation Date">={ts '2009-07-22 00:00:59'} And "NO:Release Order"."Creation Date"<={ts '2009-07-22 23:59:59'}) OR ("NO:Release Order".Category='TC') AND ("NO:Release Order".Severity='Critical') AND ("NO:Release Order".Status='Approved') AND ("NO:Release Order"."Creation Date">={ts '2009-07-22 00:00:59'} And "NO:Release Order"."Creation Date"<={ts '2009-07-22 23:59:59'}) OR ("NO:Release Order".Category='SF') AND ("NO:Release Order".Severity='Critical') AND ("NO:Release Order".Status='New') AND ("NO:Release Order"."Creation Date">={ts '2009-07-22 00:00:59'} And "NO:Release Order"."Creation Date"<={ts '2009-07-22 23:59:59'}) OR ("NO:Release Order".Category='TC') AND ("NO:Release Order".Severity='Critical') AND ("NO:Release Order".Status='New') AND ("NO:Release Order"."Creation Date">={ts '2009-07-22 00:00:59'} And "NO:Release Order"."Creation Date"<={ts '2009-07-22 23:59:59'}) OR ("NO:Release Order".Category='SF') AND ("NO:Release Order".Severity='Critical') AND ("NO:Release Order".Status='Re-Scheduled') AND ("NO:Release Order"."Creation Date">={ts '2009-07-22 00:00:59'} And "NO:Release Order"."Creation Date"<={ts '2009-07-22 23:59:59'})
ORDER BY "NO:Release Order".Category


Thanks in advanced,

JP


I have a word document with numerous standard fields that do not change. These fields are in a template format and have been bookmarked. I want to write a macro that allows a user that is in the Word doc to click an 'update' button and it retrieves the latest data from an Excel file. The Excel file will never change name and will always have the same three columns. But the data order will change (row order only) will change over time. The three columns in Excel are as follows:

Column A: Company Name
Column B: Data Field Description (ties back to the bookmarked fields in Word)
Column C: Data Field (this has the value / text that I want to be pulled into Word)

The Excel file is saved on a network drive and will not change location and the name will not change either.

What is the VBA code I need to allow these fields to be updated in Word. I am happy to 'hard code' the company name in the VBA code and then change it for each new template I create. It will only ever need to be done once per company.

This is what I have started with and it's really not working:

Code:

Private Sub CommandButton1_Click()

Dim myWB As Excel.Workbook


Set myWB = GetObject("H:\QPR\Sep 2009\Import Template.xls")

Selection.GoTo What:=wdGoToBookmark, Name:="Text5"


If (myWB.Sheets("DataDump").Cells(A1,A999) = "Company1" Then if myWB.Sheets("DataDump").Cells(B1:999) = "Senior Limit") Then
   
   'Return the value in column 3
   
   'Else leave the field as is
        
    End If

Next

Set myWB = Nothing

End Sub


Is this at all possible?


I have an order form template at work in which I have =TODAY() to give me the date an order was created.
Opening the form subsequently gives that days date of course and the original order date is lost which is a bit of a nuisance.
Is there a formula to give a "one time only creation date" that does not change when re-opened?
Any help on this would be appreciated,
Many thanks,
papillon


Hi,

not sure if this is possible but here goes.

I have created a stock sheet using excel, and so far everything is ok. But i would like to change the cell colour based on the result.
e.g. my if statement is this:

=IF(F11<=3,"Need to Order","Ok for Stock")

Basically, if the stock level goes below 3 on any item, it shows "Need to order" and when this is shown, i would like the cell to change to Red, or any different colour which makes is stand out. As we have hundreds of stock items, and it would be helpful if we could just look at the sheet and know exactly what needs ordering straight away, without having to fish out the "need to order" text!

Hope this is possible, but if not, are there other ways around this?

Thanks in advance

Rob




Hi, I'm about to finalize my monthly billing report but am left with 1 more challenge...

On my report, column A conatins an order number.
I am am allowed to charge a fee for services provided, but this is 1x fee of (let's say 100 USD) per order. This fee is included on column B.

The problem is that some order numbers are showing up more than once on that report,
and I am currently manually skimming through the report to add the '100' USD fee to each new order.

The file is already sorted by order number, so I am hoping someone knows the code to 'detect' a change in order number, and then for each 1st line of the order number that is detected to change a 100 USD fee.
Any additional lines should be marked with a value of 0 USD.

Thanks

I have an order log and want to add a status and colour code.

Currently I do this manually.

Columns A-H include general information like customer, country, order number ect.

I = Due Date
J = Despatch Date
K = Invoice Date

When the order is outstanding but not overdue the colour of the whole row (A-L) should stay white.

When it is overdue (or due today) the colour should be orange,

When it is despatched it should change yellow

and when it is invoiced it turns green.

I can't find the right way of doing all this in one forumla. I can get it to change orange automatically when it is over due using

Code:

=IF(L2<>"","",IF(J2<>"","",IF(A2="","",IF(I2="","",IF(I4507<$N$1,"A","")))))

N1 = today()


in M

The conditional format then looks for A in M and changes the colour of the row based on this.

I realise I can right similar If statements in N, O & P and then get the conditional format to monitor these as well but there must be an easier way.


I have a spreadsheet that the company uses, that when opened, inserts today's date in a cell. When we are done filling out the form and save it to our server, I reopen the file and run a macro that creates the order form and sends the pertinent information to our shipping and receiving department, including the date of the order.

The problem is that when I reopen the file to create the order form, it updates today's date, instead of leaving the original order date.

Is there a way to copy the original date to the order form (same workbook) so that it doesn't update?

Currently, I use a macro that creates the order form automatically behind the scenes every time the user collapses the sheet to clean up any blank rows for the customer to approve. Maybe the solution is to have another button that says "Finalize Order" or something and then puts a non-volatile date on the order form.


This seems simple but i'm stumped.
I have a change order document with a summary page on the first sheet and subsequent change order sheets.
I've linked the sheets so that data entered in the summary sheets appears in the change order sheets and vice versa.
On the summary sheet, I want the date from each active change order sheet to appear in a specific cell.
The easy way to do it is to link the date cell on a change order sheet with the cell in which I want the date to appear on the summary sheet. This works fine when there is a date in the change order sheet. However, if I have only activated some of the change order sheets, the summary sheet returns a date of "January 1, 1904" in all the cells that correspond to inactive (blank) change order sheets.
Question, how do I get the summary sheet to display a blank cell when there is no date in the target cell?
Thank you,
Tim


We have two price lists. One is our base price list and then we have +25%, +40%, etc for different buying levels. The base price list, let's call "Platinum Pricing" and the other price let's call "contractor pricing". Currently I have the documents residing on our server so all we have to do is update platinum pricing and it updates the other two automatically. We did this by using the =ROUND('E:\Price Lists\Order Form\[Platinum Order Form 6-2010.xls]CUSTOMER ORDER FORM'!J32*0.65,1)+'E:\Price Lists\Order Form\[Platinum Order Form 6-2010.xls]CUSTOMER ORDER FORM'!J32

We have an FTP or online folder that is password protected. I obviously don't want to give the password to our customers since there is other data in there they don't need to see, nor do we need someone at "Contractor Pricing" seeing what "Platinum Pricing" is. So if I substitute =ROUND(http://website.com/orderform/[Platinum Order Form 6-2010.xls]CUSTOMER ORDER FORM'!J32*0.65,1)+http://website.com/orderform/[Platinum Order Form 6-2010.xls]CUSTOMER ORDER FORM'!J32

It works however it is asking for a username and password to the online folder selected. How do I add a username and password to the file (vb script?? or other route) that will make this seamless for the user without giving them the username and password?

This will save us from having to email out a new order form when we update pricing or add new products.

Using excel 2003

THANK YOU, THANK YOU, THANK YOU.


I have a purchase order template that I would like to automate, but am too novice at macros.

Firstly, when I create a new document from the template, I would like the purchase order number to be automatically created, this cell is a named cell called PO. Do I need to have a separate file for the purchase order numbers? If so, does it have to reside in a shared file, or can it reside on a local machine (the template is located in a shared file).

Secondly, when saving, I would like the FileName to be taken from named cells "PO", "Supplier" & "JobNo" and save in the following directory:

\\Server\EBL\Purchase Orders\

So the path for the document would be \\Server\EBL\Purchase Orders\PO-Supplier-JobNo

Thank you Gurus in advance


Hi,

I have a split window in order to keep the top 3 cells at the top of the page for headings all the time. I have a userform which, once completed, returns the range and effects the split window. Is there any solution for this?

Also i like to use the tab button on my keyboard to manoeuvre around the form but it seems to be in a sporadic order, can i change this?


I am a novice with VB and Excel!!

Many thanks!!

-The form is run from the "qualify" sheet and then click on Qualify at the top.


How do I setup a Purchase Order Template to have a numbering system?

I would like to setup a purchase order template with a numbering system that
when each time we open new file the Purchase Order will change number. i.e.,
start at: W400001, next number W400002 etc.

Excel 2003 SP2





Hi all,

New to the forum so bear with me here.

I have a Purchase Order template that I have created in Excel.

I would like to generate a new purchase order number in cell A1 of the purchase order template every time I open up it up.

The purchase order numbers must run from 1 to 999.

If I have used a puchase order number before (i.e. when I have previously opened up the purchase order template and the number has been genrated) the same number cannot appear in cell A1 again.

Does anyone have any bright ideas on how to go about this?

Any help would be greatly appreaciated.

Many Thanks


Hello again! I was wondering if I might pick your brains with a problem I'm having. I'm trying to figure out a way to use exported order entry notes to give a simple indication why part of an order was done at no charge.


- Each row is for a different order and each column contains a different detail of that order.
- The first row contains column headings

- Relevant columns for what I'm trying to accomplish:
"M" - Quanity at No Charge
"N" - Blank currently but would like this to be where the simplified keyword is placed.
"O" - Notes (rather lengthy string of text put in by order entry personnel - This string contains certain keywords and variations of what I would like to put in column "N".

WHAT I WANT TO DO:
- Put "Sample" in "N" if the following words are found:
"Sample"
"Samples"

- Put "Replacement" in "N" if the following words are found in "O":
"Replacement"
"Replace"
"Replacing"
"Redo"
"Re-do"
"Re do"

- Put "Reorder" in "N" if the following words are found in "O":
"Re-Order"
"Reorder"
"Re order"

- Put "Giveaways" in "N" if the following are found in "O":
"Giveaway"
"Give away"

- Put "Donation" in "N" if the following are found in "O":
"Donation"

- If "M" is > 0 and "O" has nothing in it, then put "No Notes" in "N".


***I'm still new to VBA programming, but I'm trying to learn. To my knowledge, all of this can be done with some rather extensive IF statements, but I'm trying to explore the options and see if perhaps there is an easier solution?




Sorry if this is the wrong section, wasn't sure what this question falls under.

I have a stock/order form that I created..simple form that shows the items I order weekly for my store. I have two columns in this form that have my current stock and then the amount I order to. I do it all manually because we don't want to use scanners and point of sale software etc

Once I get my order in I reset everything back to my order to amount.

So I want to include a button that just resets that column to match.

So for example:

Column A is my "Order To" Column B is my "Current Stock" and then I have a Column C that is the difference (tells me what to order)

I want the button to reset column B to match Column A but ONLY when Column C is not a negative number (if that makes sense? Some items I have more stock than I want to carry so column C might show that I need to order a negative amount..I don't want to reset those lines)

I am an Excel newbie. I have made many things but I always get people to help as I have no training in Excel.

Thanks in advance.

Hi,

I'm a beginner when it comes to Macros and I don't know anything about programming.

I need to extract info from one spreadsheet to an other and would like to find a way to do this automatically. See attachments.

The problem is that I would like to populate several spreadsheets ("Order sheet") from the spreadsheet called "QB Open Order".

The attached spreadsheet ("QB Open Order") contains 2 different Names (column D) and 2 different Item Descriptions (column H) per Name.

I would therefore like four different "Order Sheets". Or preferably, 2 "Order Sheets" per Name.

Is this possible?
Is there anyone that would like to help me?


Thank You & Regards,
Sanne




So if i set a "for each cell in range" loop up with range A1 to ?# the loop checks cells in the order A1,B1,C1..?1,A2,B2,C2...?2...A#,B#,C#...?# is there a way to change the order to A1,A2,A3...A#,B1,B2,B3...B#...?1,?2,?3...?#
I am trying to get a macro to look for three in a three consecutive values in a row or column without having to write a unique loop for each row or column.

Hello all,
My small business needs some sort of automatic form-filling software and we figure it can be done with Excel.

What we're looking for is a way that a client can fill in the appropriate blanks on a provided order form and when we receive the completed file back, it automatically fills in the template with the given info.

Thanks in advance,
Ryan

I have attached an example client order form:


I am creating a sales order template. I want to be able to "tab" to the empty
cells in order to enter information. How can I make this happen? I am using
Microsoft Excel 2003 version.



Hello,

I need some help with "locking cells", to me locking means "no one can change the content of a cell or cells"

to understand more... review this file named, " College GPA Calculator". here only semester name, subjects, and grades could be inserted, rest is "locked"...
i need to do the same with..." ATM balancing template"...with the cells wherever formulas is present.
i tried much with "goolging it" and searching it on youtube, i find answers but they are too complicated, it is tough for me to absorb it.

Thanks.


Hello,

I have the following situation:

2 workbooks, named "01" and "02"

In "01" i have a time formated cell "F5" witch contains a substraction of time and outputs something like this: "1:25"

In "02" i also have time formated cells "L1" and "D10":
L1 = 1:00
D10 =IF('01'!$F$5>$L$1; '01'!$F$5; "-")

D10 will either output "-" if 01!F5 is under one hour (F5<L1) or 01!F5 if the time is above 1 hour (example "1:25")

How should the formula:
=IF('01'!$F$5>$L$1; '01'!$F$5; "-")
be modified in order for the time to be rounded up or down to the hour (up 1:29 will be floored to 1:00, above 1:30 will ceiled to 2:00)?

example:
1:20 -> 1:00
1:29 -> 1:00
1:30 -> 2:00
1:59 -> 2:00

2:10 -> 2:00
2:29 -> 2:00
2:30 -> 3:00
...

Can anyone help me change this formula =IF('01'!$F$5>$L$1; '01'!$F$5; "-") in order to make the round up or down to the hour?

Thank you.

p.s: I use excel 2003


Hi people,

I'm creating a Purchase Order template in excel and would like it to have a new order number appear everytime you start a new order. Any ideas how I may go about this? I will be storing it as a template on a shared area so that users can open it from anywhere and then save the order to a central folder with all the purchase orders in. Is there a way of having it so when you click save it will automatically take you to a folder depending on the order number? Could be tricky i guess.

Many thanks for your help.

cmf


=INDEX($BJ$4:$BJ$33,LARGE($BJ$4:$BP$33,3))

I have the above formula for a form I've made and it works as long as the info in BP33 stays in order from 1 - 33. When I change the order I get a "REF" error. Any idea why?

I need excel to find the 3rd largest value in an array. The way its set up above, it works as long as the numbers are in order. What if they are out of order? Thanks