Her situation is that she would like to be able to tally all the work orders that are created and either completed or pending. According to what she tells me she usually spends hours tallying the monthly worked on orders manually....
View 15 RepliesI need to count the amount of work orders that my employees have completed.
Review example.
How many work orders did chuck complete? (Column D)
How many work orders did brian complete?
We want to create an excel spreadsheet that will tally the number of votes for a particular artist's work. There will be about 150 artist's works that will all be assigned an individual number from 1 to 150 on an excel sheet.
We will have about an hour to complete the counts to determine winners, so a short time frame.
Is there a way of creating a formula/macro in one cell -to just enter the number of the artist's work that was voted for- and have it placed as a tally against the individual piece?
Maybe it is the layout but I was trying to keep it simple and just put hours into a cell for each employee.
I have 2 employees per day, one in the morning and one in the evening. How to get excel to add the hours for each employee at the bottom per month. Simple right?
Here is an example of what I thought would be so easy (first week in excel spreadsheet format)
sun mon tue wed thu fri sat
emp1 5 emp3 6 emp5 5 emp1 4 emp3 5 emp5 5 emp1 6
emp2 5 emp4 5 emp6 5 emp2 5 emp4 5 emp6 6 emp2 6
Is there a way to associate cells to look at values for each employee and add all the cells for the month?
Using Microsoft Access to pull from a database work orders. Some of those are what we call "dummy" work orders and have a letter in them, real work orders are signified by a 10 digit number. I would like to sort and eliminate the letters to find appropriate data. I am assuming I will need to copy into excel and do some sort of formatting but not sure where to start.
View 1 Replies View RelatedWhat I am looking for is a macro/formula that will look at a unit number, then the type of work done, then the completion date and decide if the same type of work was done on the same unit within a 7 day span of time.
Example
Unit is 11111, work is plumbing completion date is 10/4/07
Unit is 11111, work is plumbing completion date is 10/7/07
This means the work has been done 2 times (obviously showing that it did not get fixed the first time)
Is there any way to mark an X in the square with the formula, or any type of identifier what-so-ever
I have attached a spreadsheet and hope it helps
Again I know this is complex so it is OK if no one feels like messing with it but I wanted to check here first.
I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16.
As a side question, will count work for a range of data or only one column or row at a time?
I am trying to count how many work orders we have created from 3 months (90 days) ago. In an effort to make the input work minimal. I wanted to use the =Today() formula and then subtract 3 months.
So I currently have =Today() in A1 and then (A1-90) in cell B1 and I get the date I want.
However, When I use a countifs formula and my criteria is
I have a macro which:
Opens up a workbook
Presses refresh all
Waits 10 seconds
Selects 1 cell
Saves the workbook
Closes the workbook
At some point in this process a dialog box comes up saying 'this action will cancel a pending refresh command' or words like that. How I can bypass this? It interrupts the macro.
i have the following kind of table
Raw Material ReportDateDetailOrder Given Received Opening Balance25-Oct-07order to Johson50029-Oct-07Receive from jonson10014-Jan-08Receive from jonson 5405Pending sum500505-5
as you can see that the pending order (receivable from johson) is -5 as we reveive more then order given.
i want here that when we receive more than order as in this table the pending must be shown 0.
i can do it by apply the condition when pending is
So I have a quote format with various different products:
B1 to B10 Contains a vlookup with stock codes from another page. C1 TO C20 Contains drop boxes with various different stock items. Some of the stock items are available in singular form and others are in kits. I.e C1 could contain whats in C2. I want to be able to select C1 and have the sheet automatically pull up c2. But I dont want to loose any data that is in c2, in other words I still want to be able to manually select C2.
Column D has a list with the choices OPEN, PENDING, CLOSED. I want Column E to automatically enter the date when someone choose closed in Column D.
So in Cell E6. I wrote, IF(D6="Closed",Today(),"") . . . . .
It works, but wondering if E6 will keep changing the day every day to show the current day (bad) If so, how do it get it keep the closed date?
I am using an excel where I want to send mail to my team members for their trainings pendings,
Few trainings are once in a year and few traings are monthly.
im using spreadsheet works which seems to be very similar to excel. i am making a table full of numbers and i want to count how many times the number 1 appears and for that amount to be displayed.
View 11 Replies View RelatedI have been asked to develop a dashboard for my company. The data source for my dashboard is Tally ERP 9.The problem is I am not getting how to access this data into excel. I have tried MS Query but the imported data was not what I was expecting.
View 1 Replies View Relatedi am currently trying to figure out excel i have to fill this worksheet out daily and then at the end of the month i have to count the total number of items. I was wondering if there was a way to auto talley on a different sheet so that everytime i entered a product name and quantity if on the other sheet it would auto add it in
example:
I ate at mcdonalds (Main sheet)
On 11/22 I had 3 big macs and 4 quarter pounders,
On 11/24 I had 5 Big Macs and 2 quarter pounders
On 11/26 I had 12 Bigmacs and 5 quater pounders
(New Sheet) i want it to look like this:
Product quantity
Big macs (20) <-- this number i want to auto add from the notes made from main sheet)
So on another sheet i want to make a formula where for everytime i type in "big mac" on main sheet that on the other sheet it would auto add or keep a running tally so after i made those 3 entries on the main sheet it would show that i had a total of 20 big macs on the new sheet.
I need to create a sheet probably involving macros where I enter a numerical value into a cell (say B5) I want the sheet to assign a score to the value I enter and then add the score it generates to a table of values. Once ths is done I need it to automatically clear the original value I entered ready for me to enter a new value - rinse repeat..
e.g.
into B5 I enter 15 - on the right in the results table there are headings v w x y and z
say 15 scores one point for w and one point for z I want the sheet to add 0 to the totals under v,x & y and add 1 to the totals under w and z.
Then I want the sheet to clear cell B5 so I can enter a new value without manually deleting the previous entry.
If I can also copy the value I enter into a "history" which simply lists all the values I enter as I go along so much thebetter.
I have 2 workbooks. and i would like to do the following:
Workbook1
- 4 columns (product ID, quantity, Group, Comments)
Product ID, Quantity, Group, Comments
11111, 500, ?,?
11122, 1000, ?,?
11133, 250, ?,?
11144, 250, ?,?
11343, 700, ?,?
12134, 750, ?,?
Workbook2
- 2 columns (group A, Group B)
for group A:
min qty: 250
min increment: 250........................
1) I need to match the product ID in workbook1 with workbook2 - see which group it belongs and put into workbook1 under column group
2) I need to count the no.of quantity and see if it meet the citeria and place Yes/No in workbook1 column under Comments.
- for example:
product ID 11111 in workbook1 can be found in workbook2 group A (put group A into workbook1 under column group)
do a check: prodct ID 11111 has 500 in quantity, since its under group A it meets the min qty of 250. and followed by it meets the min increment too - 500/250 (thus put Yes into workbook1 column under Comments)
I have been working on a little vba project and have almost got there with help from others, but the final part needs to have a tally a number of columns and and produce the tally results to a new worksheets - the code that need proof reading /reviewing is below - it is not working correctly as it is not tallying correctly and at times seems to miss counting the last row - so could you review and suggest or make required changes so that it will work on the attached test file.
WHAT IS NEEDED IS;
1. Column D contains names -strings which are repeated such Fred Flintstone so for Fred and others would like to have a summary tally of how many times each worker has instances of a value appearing in columns I, J, K, L and P, R ,S, T and U.
With the output summary worksheet called "WORKER TALLY" and having the headings for each columns tallied from row 5 being headings for each column tally see attached example.
2. Then do same again for Column E - Names so for example the name Peter Pan and others would like to have a summary tally of how many each worker has instances of a value appearing in columns I, J, K, L and P, R ,S, T and U. With the output summary worksheet called "Names" and having the headings for each columns tallied from row 5 being headings for each column tally see attached example.
Sub workertally()
Dim b() As Variant
Dim NewWs As Worksheet
Dim j As Integer, i As Integer
Dim a As Range, v As Range, r As Range, c As Range
j = 1
Set a = Range("E6", Range("E" & Rows.Count).End(xlUp))
Set c = Range("E5", Cells(5, Columns.Count).End(xlToLeft))
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
I have a list of varying IP addresses in a column. I need to create a formula that will tell me how many times an IP address appears in the column so that I can ultimately determine the most common IP address listed.
View 6 Replies View RelatedI have a large workbook with hundreds of rows of data.
I have sorted the data and ended up with product codes in column A and my data between columns C and V.
I want to do a sumif with a difference if that is possible.
I want to sum all of the data in column C based on the product code in Column A. But I don't want to include any rows of data where Column V is blank.
i.e. If my sumif for a product code covered 3 rows C3:V5
I want to sumif Column C dependent on the product code in column A but I only want to include rows where there is a value in Column V.
So if all of the cells between C3:V3 contain data include count C3.
Moving down to row 4 if this row only has data between C4:M4 don't include C4.
Moving down to row 5 if this row has data C5:V5 include C5 in the sumif.
i.e if there was 100 in C3, 200 in C4 and 300 in C5 the sumif would be 400 because although C4 has a value V4 does not.
I want to roll this out across all of the columns C:V using the same method if possible.
I am looking for a formula or multiple formulas to perform an If/Then statement, adding and dividing. Example listed below.
If O8 = 'Y' then perform XXXX, otherwise, do nothing.
XXXX= in Y8 search for word 'apple' and add tally to AA46, in Y8 search for word 'orange' and add tally to AA47, in Y8 search for word 'banana' and add tally to AA48.
Take AA46/Y46 and put results in AB46.
If this could be put into on cell and perform all the needed actions, otherwise, multiple cell formulas is ok.
see the attached spreadsheet...I have a few notes of what I want to do. I think it is a vlookup, but i can't figure out how to do these.
View 2 Replies View RelatedCan numbers and text be included in the same cell and still have the number be included in the total in a formula in another cell? Or must a cell only have numeric values for it to be seen/included in a formula's total value.
I'm trying to create a database that totals materials for a construction project. I want to display the number of doors for a house in a row of cells and have the all the doors totaled in the last cell. This I have no trouble doing.
The problem arises when I want to add some text information about the style of each door in the same cell that the number of doors is shown. As soon as text information is added to a cell that has numeric information, that cells numeric information is not included in the final total in the last cell in the row.
I resorted to using comments instead, but, when the are made visible on the spreadsheet, they don't seem to lock to a relative position regarding the cell they're attached to. For instance, if I widen columns or make any significant spatial changes to the spreadsheet, the comments don't move with the changes.
There may be a way to lock comments to stay in a relative position regarding the cell they're attached to. And if that's the only way to make comments for the items in each cell stay with the cell, then I'll have to use that method. But I'd rather not have to use the comments function at all.
I'd much rather be able to have numbers and text be in the same cell, and still have the number value of that cell be included in a formula total at the end of a row of numeric information.
Example: (In this example separate cells that include both numeric values and text are indicated by parenthesis. The final cell that has the formula that totals the numeric information in the separate cells is indicated by brackets)
(30, raised panel doors, unpainted) (10, raised panel doors, white)
I have a cell, lets call it A1, w/ an IF statement referencing another cell, which is a function of two values taken from RTD. A1 is either blank or reads "One." I want another cell to tally the # of times that A1 takes on the value "One."
The issue:
I've tried approaching this by using Worksheet_calculate and an IF/Then statement to add 1 each time Range("A1") = "One" however this doesn't work because it keeps adding 1 while A1 reads "One." The calculations seem to fire off every milisecond, so if "One" flashes for 0.5 seconds, I get 500, and if it flashes for 3 seconds, I get 3000. For both cases, I'd like it to just add 1.
I am trying to make a more useful excel extracted report – what I have to start with is muck like the attached sample file. With at the bottom is what I would like to be auto done.
There is a column of names – with most names repeating so the first thing needed is to create a new summary list – can be in the same worksheet or in a new worksheet. The new list with be just a list of each name but only display each name once rather that the multiple of times as source list.
The second requirement is than once have a list of each name that appears then in columns next to each a tally /count of each non blank cell under each respective column heading - now it has to be a count of non blank cells as the content of each cell will chance – now need to understand it is not a count of numerals as such but a count of non blank cells so it is not 1 + 3 + 5 = 9 but should be 1 +3 + 5 = 3 (3 non blanks).
A idea of what I want is at bottom of sample worksheet attached
I am trying to set up a grid to count the number of orders each rep writes for each month of the year. I have tried three different formulas without success.
=COUNTIFS('Daily Compliance'!$B:$B,$C$7,'Daily Compliance'!$O:$O,">39447")-J7
In which J7 would count all orders written past Jan 31, minus K7, etc.
I also tried:
=COUNTIFS('Daily Compliance'!$B:$B,$C$7,'Daily Compliance'!$O:$O,">39447",'Daily Compliance'!$O:$O,"<39479"))
and:
=SUMPRODUCT(--('Daily Compliance'!O:O>=E274),--('Daily Compliance'!O:O<=F274),COUNTIF('Daily Compliance'!B:B,C7))
Daily Compliance is the sheet that lists all the orders, with column "B" being the rep, and column "O" being the date.
At this point, I'm not sure what I'm doing wrong.
I need a macro that looks at the earlier orders first and assigns inventory to them. Keep assigning inventory until either there are no more orders, or the inventory is depleted. I have tried to figure this out using formulas but it never seems to work out correctly. Here is a little sample I made to show what I am trying to do.
Item# Date Quantity Order Quantity Committed
Item# Quantity in Inventory 1 1/1/2008 18
1 21 2 1/5/2008 23
2 300 3 1/10/2008 10
I have been trying to create a macro button on my new orders page for my spreadsheet.
What I am hoping to achieve is a marco that when I click place it, it will take the information needed from the new orders page and paste it into the existing orders, but it does not end there the tricky bit im finding is how to get it to go to my stock spreadsheet and whatever part was ordered it would lower the stock by the quantity that was ordered so in effect the spreadsheet would lower my stock level as a items were purchased. I have attached the spreadsheet for you all to have a look at. I only have very basic VB skill but eager to learn. If anyone can solve this for me I defiantly owe them a pint
We have a spreadsheet in use at the moment which has various lines of information, each line has a customer name, id, request and various other bits of information which needs looking into.
The infomation is arranged onto weekly sheets and is managed manually. Once the customer request is complete, we grey out the background colour. Some outstanding issues background colours are either left blank, yellow or red, depending on whos doing them.
I was wondering if there is a search criteria that we could use that would search through all the sheets in the workbook and find any customer queries that are not greyed out and display them on the results page.