
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
I am having trouble writing a function statement in Excel 2007. My unfamiliarity with software is hurting me.
My worksheet contains 3 columns: Date (Date), UID (Alphanumeric String), Work Order (Integer).
There can be multiple unique work orders per UID.
What I would like to do is add a column named "Repeat" that would display a 1 if the Next Work Order per UID is within 7 days.
I have tried some if statements, match, countif, etc. My problem is (think at least) I can find (in multiple ways) which UID have repeat work orders, but I cannot figure out to determine if the next work order occured 7 days later.
Similar Excel Video Tutorials
COUNTIF & Ampersand
 See how to get criteria from four (4) different cells and combine (join) them into one criterion for the COUNTIF function. See how to use the COUNTIF ...
COUNTIF function formula 21 Examples
 See methods for counting: specific words, specific numbers, numbers greater than a hurdle, numbers between two values, values than are NOT equal to an ...
Similar Topics
How could I determine the average of work orders that are received per
business days in a given month? I have two columns: Column B lists a unique
code for each work order and Column C lists the date in which each work order
was created. I don't receive work order every day, but in some days I receive
any number of work orders.
Thank you for your help.
Orlando
We use work order worksheets in excel with basic information: Date in, work order #, account, date out, total # of days, work conducted by, number of samples, description of sample, etc. Each work order has an individual worksheet. The information in these worksheets eventually get entered into a work order log containing the same information in order to compile a quarterly report. We might have 400 work orders in a quarter.
My question is: How do I link the individual worksheet to the work order log in order to get the data to transfer automatically when a new work order is entered into a worksheet? I'm able to get the first work order to link to the log worksheet, but CAN NOT figure out how to get subsequent work orders to enter into the following rows, rather than just replace the first row over and over.
Probably a pretty basic question, but I'm fairly new to any kind of programing. PLEASE HELP! Any help would be greatly appreciated. If my question is unclear, I'll try to clarify.
Thanks in advance.
ltlbt
I am trying to calculate the number of days between a prior date and today, however, I want Excel to return a blank cell if the date is missing. I am calculating the number of days orders have been open (020, 2130, 3160, 6190, and 90+). One order may have multiple items and the way I have the worksheet laid out is one row per part number. Each item on an order will all have the same document date  here is where my problem comes in. When I add the formula it calculates each item instead of consolidating them on the order number level. I want to be able to SUM the values in the aging columns to get a total number of orders and then break that down by how many orders in each aging category. In order to get this my logic says to delete the DOC DATE and DAYS OUT formulas from all but the first line of an order. So far I have been unable to get Excel to calculate the 020 days column correctly. I have attached a copy of the SS.
this is the formula that I am currently using.
=DATEDIF(C179,'[allocation report 2211.xls]Sheet2'!$A$1,"d") < this is the formula in col E to return the number of days since the order was opened.
This is the one currently in col G "021 days " < when the date field is blank this returns a value of 40577 which is causing this formula to put this in the 90+ column.
=IF(OR(E178=0,E178>=21),"","1")
to recap here is what I am wanting to see:
1. if the date cell (C) is blank I want cell (E) {datedif form} to be blank
2. if the date cell (C) is blank I want cell (G) {021 days} to also be blank
3. if col G is SUMMED I want to see how many orders are in that category  same for columns H, I, J, and K
4. I also want to SUM col G:K to give me the total number of open orders
I have a formula in a worksheet that lists equipment in inventory. It looks at another worksheet of service work orders and verifies that, if work was done on a particular unit, do a look up and post the latest meter reading recorded on a work order for that unit.
My formula works fine unless the latest work order doesn't have a meter reading posted yet, so it returns a zero  overwriting any previous meter reading that was stored there.
Code:
=IF(COUNTIF('Work Orders'!$H$3:$H$1500,$C38),LOOKUP(2,1/('Work Orders'!$H$3:$H$1500=$C38),'Work Orders'!$O$3:$O$1500),"")
How do I get the most current reading...as long as it isn't a zero?
I have two separate datasheet queries that are used to build two separate forms. In both forms, I wants users to be able to update data that comes from the Datasheet query.
The first query I built works exactly as intended.
Code:
SELECT [Work Station].[Work Station], [Work Orders].[Work Order], [Work Orders].PartNum, [WO Sequence].Sequence, [WO Sequence].Machine, [WO Sequence].[Order Qty], [WO Sequence].Balance, [WO Sequence].Assignment
FROM [Work Station] INNER JOIN ([Work Orders] INNER JOIN [WO Sequence] ON [Work Orders].[Work Order] = [WO Sequence].[Work Order]) ON [Work Station].[Work Station] = [WO Sequence].[Work Station];
The second query does not. I am able to view the data that I want to see, but cannot update or modify any of the fields.
Code:
SELECT [Shift Work Done].EmpNum, [Shift Work Done].Date, [Shift Work Done].WONum, [Work Orders].PartNum, [Shift Work Done].Seq, [Shift Work Done].[Hours Worked], [Shift Work Done].PartsCompleted, [Shift Work Done].PartsScrapped, [WO Sequence].[Order Qty], [WO Sequence].Balance, [WO Sequence].[Parts Per Hour]
FROM ([Work Orders] INNER JOIN [Shift Work Done] ON [Work Orders].[Work Order] = [Shift Work Done].WONum) INNER JOIN [WO Sequence] ON ([Shift Work Done].Seq = [WO Sequence].Sequence) AND ([Work Orders].[Work Order] = [WO Sequence].[Work Order]);
If anyone could share some thoughts with me on this, it would be greatly appreciated. In order for my forms to function the way I intend them to, I will need this query to allow me to update fields in the query.
Thank You.
Hi, not sure how to go about this, I am trying to build a scheduling calendar. My idea is to have a table with work order information. Each work order will be scheduled with a date. I would like for the days on my userform calandar to display statistics about each day. The calls to be scheduled will be broken up into 4 categories(installs, estimates, repairs and maintenance). so my table will have a field for date of service, and work type. So I would like for the calendar to be able to look up each date, then count and display the number of installs, estimates, repairs and maintenance work orders for each day. Is this possible? I am attaching my workbook...the userform that has the calandar is named "CallForm" which is a tabbed userform, the tab in question is captioned as "Monthly Calandar". Just getting started on this...have not yet made the table for the info to go in, but I will place the table on the sheet named "projects" which is hidden. Thanks in advance for any help.
DispatchMasterCopy.xls
I have been working on some sort of solution for this for a couple hours now and my head hurts :)
Basically we have a scrap report that is made on a daily basis that takes all of the work orders for the day and gives all of the dirt on them.
I have 4 columns of data
WO......SKU.....Scrap %.....Scrap Total

 Anywhere from 80 to 140 rows a day...

WO......SKU.....Scrap %.....Scrap Total Subtotals &date
The problem we face now is we have over two years of work order data that we want work order counts for. I need to find something that will automate the daily work order count to save me from doing it all manually. The date for the work orders is always located down by the subtotals but the amount of works orders for each day is never the same.
I have tried everything I can think of but am stumped at this point.
Please help! :D
I hope I don't confuse you with this question?
Currently I'm using this ARRAY to find a MAX value accoss several columns.
=MAX(FREQUENCY(IF(G6:AH6=1,COLUMN(G6:AH6)),IF(G6:AH6<>1,COLUMN(G6:AH6)))) Example: 0 1 1 1 0 1 1 0 1 1 1 0 1 1 0 1 1 1 0 1 1 0 1 1 1 0 1 1 = 3
0's represent the days a store orders product from a supplier. This could vary by store.
Some stores may order more often or less and on different days. 1's represent the amount of days between orders.New Challenge: The example ARRAY above displays only the days between 1 order. IF a store wants to calucalate the days between 2, 3 or 4 orders using a drop down combo box, how do I write an IF function to look back past the 1st, 2nd and 3rd 0's and still calculate the days between orders? Your assistance would be greatly appreciated.
Apoligies if this is the wrong place, but basically, I need a hand and I'm not certain as to where to look.
I'm an ALevel ICT student and have an Excel related issue regarding the user of "=CountIf" or nested "=Count(IF...)", or alternatives to the two.
Essentially, I'm trying to count the number of an item sold in a sheet in a particular month.
E.g. How many Red Tshirts are sold in January, then in February, and so on.
However, I just can't get my head around how to get it to work, and my teacher has been little/no help.
I've tried "=COUNT(IF(Orders!C12:J22="Red Tshirt", IF(MONTH(Orders!N12:N22)=1)))"
Where "Orders!C12:J22" is the Items sold
And "Orders!N12:N22" are the dates at which each order was placed.
But these clearly don't work.
I apolagise in advance for the horrifically syntaxed formula above.
Thank you for any help.
Hi everyone,
I have been trying to suss this one out for weeks and would really appreciate any help or pointers please.
I have a large spreadsheet for our orders that requires either filtering or somehow to show only the orders that haven't been paid from 30,60 and 90 days from the current date.
To make it easier i have attached a very simplified version of this spreadsheet with todays date entered as Today(). What I' am after for '30 days' is everthing filtered out that has been paid and everything that is over 30 days unpaid  so on the spreadsheet the only lines displayed would be Mrs K and Mrs T.
From then i could change it to 60 days and 90 Days accordingly....i think.
Ideally the other tabs would be the neatest way to display the information (i.e one tab named '30 Days' would display everything from the order sheet unpaid over 30 days).
Any help would be brilliant as at the moment i can filter out all the orders that haven't been paid but have to scroll up and down hundreds of lines to see what the dates are so we can work out who is over 30 days etc.
Thanks for any help
Mike
Hi guys
I'm working on an order log which is relyig on some countifs to keep track of it all. The countif's I'm trying to get sorted a
Countif a cell's date is more than 7 days historic of today's date but anything 7 days or less isn't counted.
Countif of the number of orders in different statuses based on today's date. These need to stay at the last figure when the date changes so I can track the historic movements of the orders.
The final one is to count a blank cell if a cell in another column is not blank.
I know this is a lot to ask but I was wondering if it was possible in Excel 2003.
Many thanks
Chris
Hello,
I am trying to get a loop going. I have a worksheet with a lot of purchase orders with unique order IDs. When a customer orders multiple items through the website, the order is imported in to excel on multiple rows i.e. if a customer orders a black ink cartridge and a color one, the order is imported in to excel on two rows. All the customer information is the same and so is the order number. the only difference is the item name and sku. What I want to be able to do is basically have a macro that goes down the list and counts the number of rows with the same order number and then I have two columns with two different prices. I want to divide those prices by the number of rows counted then move on to the next order number and count the number of rows with that same order number and repeat the process. Let me know if that made any sense at all.
Thanks!
This formula has been working fine for me...until I have the condition where no value >0 has been entered in my LOOKUP range as yet. Or at least that is what I have managed to test so far. I've verified that my values have the same format (General) and I've verified that my ranges are all of the same size.
I'm looking in one worksheet for a work order meter reading that will be entered in another sheet when the Unit # for the equipment matches. I discovered if there is a work order entered for and no meter reading has ever been recorded yet, I get the #N/A error. If I go in and put a fictitious meter reading in of 1, the error goes away. It doesn't like a blank or zero meter reading and I need to know how to get around this.
Here's the current calculation:
Code:
=IF(COUNTIF('Work Orders'!$G$3:$G$1500,$C77),LOOKUP(2,1/(('Work Orders'!$G$3:$G$1500=$C77)*('Work Orders'!$P$3:$P$1500<>0)),'Work Orders'!$P$3:$P$1500),"")
I believe I've been able to narrow down what is probably the cause, I just don't know how to fix it.
I am trying to create a formula using index, match, and/or offset but I just can't get it to work. The lookup value is a single cell in one worksheet, the data are in another. The problem is that I want to look up a production work order number and then go down and over for specific values.
Here's a brief example: Say the work order number is 123.
In the second worksheet I have:
Work Order No. 123
Production StepsMachineDue Date
GenerationGP12/28/07
GrindingGP22/29/07
PolishingGP34/1/07
QAGP44/5/07
Work Order No. 456
Production StepsMachineDue Date
GenerationGP33/10/07
GrindingGP53/15/07
PolishingGP64/5/07
QAGP24/15/07
Work Order No. 789
Work Order No. 789
Production StepsMachineDue Date
GenerationGP23/9/07
GrindingGP123/29/07
PolishingGP44/1/07
QAGP64/5/07
I want to create a work order traveler that is set up with the same items listed above. I want to be able to look up work order no. 123 and then pull in the data that goes with that particular work order. The search should go through ALL of the work orders listed (I've shown three here  there are dozens) and return the data for the work order that I've entered on the traveler.
I've checked out a lot of online ideas but none of them quite fit. Any help will be greatly appreciated!
Hello all!
I am trying to cut down on a lot of manual input. Right now I enter in values everyday by finding specific work orders and copying a few lines of information from each one. Everday the work orders are different and each work order is its own excel file.
I would like, if possible, to use a vlookup whose array covered an entire folder of excel files, not just workbooks within one excel file. I would then only have to type in the work order number, and the equation would go searching for the file title with that number in it. All of our work orders are made from a premade template so everything I need are always in the same spots in every work order.
Any advice would be greatly appreciated.
This is difficult to explain which is why i uploaded my file but basically i have an order book. The orders have classifications  emergency, plan, stock and specified. The order number is listed beside each part in the order as some orders have multiple parts. The dispatch date is then noted down against each part of the order  only when all parts of the order have shipped can i class the order as shipped. I then measure these shipped orders against their classification in cells J62 to 65.
The equation is three fold, it needs to check order classification, identify the unique order numbers, then recognise when all parts of an order have a date noted. It may be a variation on the formula used to count the orders in each class =COUNT(1/FREQUENCY(IF($C$11:$C$44="Emergency",MATCH($J$11:$J$44,$J$11:$J$44,0)),ROW($J$11:$J$44)ROW($J$11)))
After i achieve this i want to count all orders shipped in Jan, Feb, March etc
Cannot anyone help  i'm close to finishing this but as you can see from my sheet the best i have managed so far is classing an order as shipped even when 1 of its parts has shipped. The two results boxes reflects the 2 methods i have used to get my results.
Thanks
I am trying to create a database with due dates for work. For Example.
Due in 7 Days =COUNTIF(A7:A40,"<="&TODAY()+7)COUNTIF(A7:A40,"<"&TODAY())
Due in 30 Days =COUNTIF(A7:A40,"<="&TODAY()+30)COUNTIF(A7:A40,"<"&TODAY())
Due in 60 Days =COUNTIF(A7:A40,"<="&TODAY()+60)COUNTIF(A7:A40,"<"&TODAY())
Due in 90 Days =COUNTIF(A7:A40,"<="&TODAY()+90)COUNTIF(A7:A40,"<"&TODAY())
Lapsed<Today =COUNTIF(A7:A40,"<"&TODAY())
I think these work for me in excel, but I'm not a master at this and for some reason when I read my functions I swear they are contradicting themselves. The reason I feel the functions contradict themselves is because I can remove the countif and it still gets the same answer.
For the date ranges 90, 60, 30 & 7 I do not want dates counted that are less than "today" or ex. = a negative number. They would be considered "Lapsed"
Please see the attached file. Any help would be greatly appreciated!!!!!
Kevin
I am trying to build a template and the data from my Pivot table is being used on another sheet in the workbook.
I work in production scheduling so we have a download with the start date. Problem is that multiple orders start on the same date and the first value must be the work center
So for example, order Z and order X start on the same date and run on the same work center. In the pivot table, it only lists the date once in row 1. I would like it to list the date and work center in each row in which there is a value.
Current:
Column A Column B Column C
Work Center Start Date Sales Order
Row 1 85 4/15 Z
Row 2 X
Ideal:
Column A Column B Column C
Work Center Start Date Sales Order
Row 1 85 4/15 Z
Row 2 85 4/15 X
I work in a printing company where we receive orders from clients daily for print work.
We process the orders by printing the requird documents and finally pack it in a box to be ready for "Collection" by the mailing company we use.
For each box that is packed has a record in excel for:
Order no. Packed Date Packed Time Packed Day
The order can be packed on any day at any time but the Collections from the mail company are only on Tue, Thu, Fri(fixed days) at 16:30pm(fixed time), which is the main problem here for calculation
I need to calculate the collection due date, day from the Packed Date and Time information that I already have.
Example 1
Packed Date Packed Time Packed Day
01/03/09 11:43 Sunday
Then Collection should be on Tuesday as thats the closest collection day from the day packed, so it should look like this
Collection Due Date Collection Time Collection day
03/03/09 16:30 Tuesday
One more thing, If any order is packed later than 16:30 on the day of collection, then it will go on the next collection due day.
Example2
Packed Date Packed Time Packed Day
05/03/09 16:40 Thursday
So collection should be on Friday as it was packed later than 16:30 on Thursday. So it should look like this
Collection Due Date Collection Time Collection day
06/03/09 16:30 Friday
Therefore I am looking for a Function that can calculate the first Collection Date and Day(Tue, Thu, Fri) from the Packed Date and time.
Please help me with this problem, I need it solved urgently for work. I am an intermediate user so would be glad to have a solution not too difficult for my understanding. I would be extremely grateful for all your support.
SAM.
I am using Excel 2002, I have a spreadsheet with ~200 rows and ~75 rows. Column A is sorted to be in numerical order, column b is date field mm/dd/yyyy, column C is a pull down with Y/N/NR/NA values. I need to figure out how to do a count if statement that says any date in column B with value "Y" from column C. Since the dates in Column B aren't in chronological order, sorting it first and then doing a general countif statement wont work in this situation, becuase I already have a countif statement setup based on the fact that column A is sorted in numerical order.
A B C D
1 1/1/2007 Y
2 12/15/2006 N
3 4/5/2006 Y
4 3/3/2007 NR
5 11/20/2007 Y
Desired results  COUNTIF Date range 1/1/200712/31/2007 , values in Column C
Results:
2  Y
1  NR
Thanks for helping
I am preparing chart for sales team and given area to select them the order with clicking the cell and based on their sellect date of order I want to shown the complete credit cycle chart, I have tried but couldn't suceed, can anybody help me to develop this template ??
Details
1. delivery will be dependent on Lead time
2. Payment due based on credit days
LEGEND
Distributor
XYZ
A1
Order given
STL
SDD
A2
Delivery
TM
AAA
A3
Delivery confirmation & Invoice Printing
Monthly volumes (KL)
60
A4
Invoice Received
No. of orders
6
A5
Payment due
Lead Time (days)
5
Credit Days
7
Average order size (Rupees)
2,300,000
Credit Limit required (equivalent orders)
Cash Security required (equivalent orders)
Input area ( Select "YES" from the drilldown) single row per order
July
Aug
Days
M
T
W
T
F
S
S
M
T
W
T
F
S
S
M
T
W
T
F
S
S
M
T
W
T
F
S
S
M
T
W
T
F
S
S
M
T
W
T
F
S
S
M
T
W
T
F
S
S
Date
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Order # 1
Yes
Order # 2
Yes
Order # 3
Yes
Order # 4
Yes
Order # 5
Yes
Order # 6
Yes
Order # 7
Order # 8
Order # 9
Credit cycle chart
July
Aug
Days
M
T
W
T
F
S
S
M
T
W
T
F
S
S
M
T
W
T
F
S
S
M
T
W
T
F
S
S
M
T
W
T
F
S
S
M
T
W
T
F
S
S
M
T
W
T
F
S
S
Date
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Order # 1
A1
A2
A3
A4
A5
Order # 2
A1
A2
A3
A4
A5
Order # 3
A1
A2
A3
A4
A5
Order # 4
A1
A2
A3
A4
A5
Order # 5
A1
A2
A3
A4
A5
Order # 6
A1
A2
A3
A4
A5
Order # 7
Order # 8
Order # 9
example.jpg
The screen shot is a report that i am attempting to manipulate the data to a different format, the 3 colums are work order, codes to add, codes to delete. If there are multiple codes, they are seperated by a comma. I'm looking for a macro to show (output location is not important) row 1: work order; code 1 to add; code 1 to delete
row 2 blank instead of work order; code 2 to add or blank; code 2 to delete or blank
row 3 blank instead of work order; code 3 to add or blank; code 3 to delete or blank
row 4 2nd work order; code 1 to add or blank; code 1 to delete or blank
row 5 blank instead of work order; code 2 to add or blank; code 2 to delete or blank
row 6 3rd work order; code 1 to add or blank; code 1 to delete or blank
ect...
The report can be anywhere from 20 or 500 work orders so i'm trying to have the the loop know how many rows are actually being used in column A for the work orders.
I have a summary spreadsheet, "April Daily Revenue Impact.xls" that has a tab
for each day of the month. Also, for each day of the month there is a
seperate spreadsheet that contains the orders for each day. The tabs on the
summary spreadsheet should look at the daily order log and summarize the
quantity of orders and the revenue impact of the orders by region. The
Sumproduct function works great for the revenues. However, the Countif
function will only function if the corresponding Daily Order Log is open when
the values are updated, otherwise it returns "#VALUE".
The function is entered as: "=COUNTIF([04042006.xls]Master
Worksheet'!$C$3:$C$83,A4)" where Row "C" is the Region on the 04042006.xls
Spreadsheet and Row "A" is the same regions on the summary spreadsheet. When
the corresponding Daily Order Log is open, the function returns the correct
values.
Any help would be greatly appreciated.
I'm working on school project where I need to simulate inventory levels in assembly line. And I have serious problems with my excel model.
I have managed to make a work queue and lots of other stuff for the model, but I can't get it to take orders in the way I want it. Each order has a order number (from 1 to 100) and the orders come in almost randomly e.g. 3, 5, 11, 2, 7, etc. What I want to do is to take the smallest available order that has not been processed in.
The available orders column and processed orders look something like this:
A B C D
Time, Available, Processed, Start processing
5 2 0 2
10 0 0 0
15 0 0 0
20 0 0 0
25 5 0 0
30 0 0 0
35 0 0 0
40 0 0 0
45 4 2 4
50 0 4 0
55 7 0 5
60 6 5 6
Zero means no new orders or no processed orders. Now the Start processing column should select the smallest not processed order if previous order has been processed.
A have, for now at least, all other problems solved, but can't figure out how to get start processing column check for the smallest not processed order line. I have tried combination of Min and Max functions with If, but it soon requires too many Ifs to make any sense out of it. I also tried the Dmin function, but it wasn't up to the task becouse the model requires ~1000 lines and as Dmin only takes criterias vertically I ran out of columns . So how could find minimum from row one until current row excluding values processed so far and only checking orders available so far? Thans in advance and hopefully my explanation made some sense.
I have a summary spreadsheet, "April Daily Revenue Impact.xls" that has a tab
for each day of the month. Also, for each day of the month there is a
seperate spreadsheet that contains the orders for each day. The tabs on the
summary spreadsheet should look at the daily order log and summarize the
quantity of orders and the revenue impact of the orders by region. The
Sumproduct function works great for the revenues. However, the Countif
function will only function if the corresponding Daily Order Log is open when
the values are updated, otherwise it returns "#VALUE".
The function is entered as: "=COUNTIF([04042006.xls]Master
Worksheet'!$C$3:$C$83,A4)" where Row "C" is the Region on the 04042006.xls
Spreadsheet and Row "A" is the same regions on the summary spreadsheet. When
the corresponding Daily Order Log is open, the function returns the correct
values.
Any help would be greatly appreciated.

