I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies View RelatedI have put together the following code (with help from this and another forum):
View 5 Replies View RelatedWe receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
I want to create a macro that will copy and paste a couple seperate collumns but the problem is that each month, and each invoice, contain a different amount of rows so I can't "record" a macro. Lets say I start on A4 which is the heading of Column D..below it are a bunch of records at the end of the records is a space. I need it to stop there. Then do the same for Column G, I, etc. I would like it to copy each column and paste in a new workbook.
View 9 Replies View RelatedI have a what I believe to be a consistant formula on lines S12-S18 for gathering the sumproducts for dates on column O. Only problem is that the sumproduct formula is not working on line S18 correctly. I keep looking it over and I can't figure out why it is not returning the correct amount for that cell.
Here is the formula I am using:
=SUMPRODUCT((TEXT($O$12:$O$500,"dddd")=R18)*1)
Please see attached line S18 for the error I am getting.
correct formula related to dates;
Assume cell A1 contains a date in DD-MMM-YYYY format. I would like to have a formula that looks in A1 and compares to the current date (TODAYS Date) and if the value in A1 is todays date or greater return "YES" and if not return "No".
I'm making a budget in excel.
I have generated some dates that appear in column a. I want to make a macro that copies lines from sheet 2 and inserts them between the appropriate dates in column a sheet 1.
eg:
A
1/1/01
23/1/01
4/2/01
5/2/01
7/2/01
sheet2:
2/1/01
23/1/01
6/2/01
outcome:
A
1/1/01
2/1/01
23/1/01
23/1/01
4/2/01
5/2/01
6/2/01
7/2/01
the lines will also have 2 other fields for copying (description and amount).
I will also need to copy formulas to the inserted rows in some columns (eg balance) but not all (not the date column).
When I import data , the dates appear as follows in Col G sheets "Imported Data"
If I select Col G and use Text to columns and select mdy, it gives me the dates in the correct format. However, If I use the macro recorder to do this and then run the macro, it does not work
I have attached the dates after using text to Columns wjhich is the correct format.
Imported Assets *G207/01/2006302/01/2007402/01/2007511/01/2008601/01/1985720/11/1990801/01/1985901/01/19851001/01/19851101/01/19851225/09/19921302/01/1993
Imported Assets  G201/07/2006301/02/2007401/02/2007501/11/2008601/01/1985720/11/1990801/01/1985901/01/19851001/01/19851101/01/19851225/09/19921301/02/19931401/05/19931501/06/19931601/08/19931701/01/19941801/09/1995
I have a form on which I select a date (using a calendar form) and on the form it works perfectly. The date is in the correct dd/mm/yyyy format for example (03/04/2013 - 3rd April 2013), I then insert this date into a worksheet and it comes out in the correct format, but it is actually turning the dates around so where it should be putting 3rd of April (03/04/2013), it's inserting 4th March (04/03/2013) instead. It looks very much like the form sees the date in dd/mm/yyyy format and the worksheet when taking the data from the form thinks it in US format (mm/dd/yyyy).
[Code] .....
trying to write a formula in a VB to some cells:
View 3 Replies View RelatedI want to add some to a macro have which asks before performing the rest of the macro... "Are all details correct?" with a yes and no button. yes, carries on with the macro and no ends the macro.
View 2 Replies View RelatedI use a lot of VLOOKUP formulae in the sheets i put together and, despite both the lookup value and table array being in the same format (usually text format), i often get an #N/A being returned.
Here's the fun part... if i then do something simple like access the lookup value cell (either by double clicking or pressing F2) and then hit return, then the vlookup calculation suddenly returns the correct value.
I'm convinced it's something to do with the way the cells are formatted but can't work out what.
What would be the correct mathematical terminology for the following:
I have a pool of 100 people and I am tracking their hours worked.
I want to present the % of people that fall in to the 3 largest categories
E.G.:
00:00 - 25:00 = 5
25:01 - 50:00 = 10
50:01 - 75:00 = 15
75:01 - 100:00 = 25
100:01 - 125:00 = 15
125:01 - 150:00 = 10
150:01 - 175:00 = 10
175:01 - 200:00 = 20
In this case the largest groups are:
50:01 - 75:00 = 15
75:01 - 100:00 = 25
100:01 - 125:00 = 15
55% fall in to this bracket.
What we are targeting as a business is that the majority of worker fall in to a similar range each month. So the challenge for my team is to increase the % value month on month. the close to 100% the better they are doing.
Equally what in Excel could I use to automate this calculation.
I want to superscript the text with red color. But now only a active cell is change to superscript with red color after running the macro.
View 2 Replies View RelatedLooking for some help to figure out what is causing our problem. My friend has created the attached excel sheet in Excel 2003. The issue is that the a vlookup does not seem to be finding the correct line for the value from the lookup table when it is returned twice.
Let me explain - The details are contained in the attached workbook. There are three sheets in the workbook - NH3Curve, Samples and Qvalue table.
On the Samples sheet, in cell C12 and C13 you can enter varying values. Go across to F12 and F13 and you will see that they both have the value 22.1. This is where the issue occurs - in cells G12 and G13 the lookup value should be the same - but it is not. G12 is actually returning the value for 22.0 not 22.1. (lines 173 and 174 on the Qvalue sheet). We cannot ferret out why this is happening.
Any help would be greatly appreciated. Sometimes when you look at something you can't see the obvious, so I hope it is that simple.
How can I correct this Time formula which says in the Tag that it produces a
negative Result and hence displays in cell as ####
=(INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440)
What I am doing is selecting the field in A which equates to the lowest
value in C (C= hourly sales), then I am subtracting 60 mins from the value
selected in A. This in effect will give me my closing Times i.e. find the
cell with Zero Sales then subtract 1 hour to find what must be the closing
time (assuming of course that there is at least ?1 of sales per hour while
open).
My cells in A are formatted as h:mm AM/PM, as is the format in the formula
cell
I have an attached spreadsheet, where I have an IF statement within "O8". Bascially, the result in that cell should equal "50", however displays as "0".
I think this might be an excel glitch - as the same formula worked for "M8".
I have been working on a Spreadsheet for GVWR (Gross Vehicle Weight Ratings). I'm having a problem with the Formulas in Excel, I thought you may know what's going on and why it's not giving me the right data. I'm pretty good with Excel, I've been using it for about 17 years for calculating Tank formulas, etc, but have never run in to this before.
I have three columns to the right of the sheet. One is Stock Vehicle weight of an item____(E) The next column is the Aftermarket weight of the item______ (F) Then the third column (G) is the total weight minus the stock weight using a very simple formula =SUM(E6-F6) gives the correct weight in the third column, but at the end of each area (Topic) i have a Total Weight area in the third column (G), using the formula of =SUM(G6:G26) which should just add up everything in column G, but it doesn’t. I have re-made the spreadsheet three times,
it returns an error when I try to enter it. Is there a brave soul which a good eye for errors that might be able to shine some light on the error. Currently it appears the error centers around the MONTH( ) portions of the function. But each embedded function is able to stand alone and I can't imagine why they won't work together.
View 4 Replies View RelatedI am trying to count how many entries in a worksheet say "yes" depending on a value in another column - formula is:
=IF('PM Tracker'!$D$3:$D$1000=C15,COUNTIF('PM Tracker'!$V:$V,"Yes"),"None")
Value in cell C15 is equal to "43-11" (week number and year)
This keeps returning "none" as the answer but I know 3 entries satisfy the "yes" criteria in Column V for week 43-11.
I have put together the below macro to take data from an Excel spreadsheet and place in a word documents (using bookmarks.
What should happen is it should open up the correct word template depending on what data is in column E (Servicecode) and fill in the bookmarks with the data from excel.
It all works fine and goes through the loop and saves, inputting the correct data into the bookmarks but after looking at the saved templates and scrolling through using F8 the IF ELSE does not seem to work. It CALLS theBearSSR macro rather than the relevant macro e.g. word in E2 is Cele, the macro should call the CeleSSR macro (which opens the correct template).
I don't think i have declared my DIM Servicecode correctly... I can't seem to figure out how to make the IF ELSE condition look at the Servicecode and then go to the correct sub routine (the difference between them is the template that is opened).
HTML Code:
Sub Celebration()
Dim departuredate As String[code]......
I need identifying correct title and corresponding company name. For this I need a UDF which looks for first occurrence of title having event or marketing or meeting word in data and if found the remove other titles and company names already separated with "/".
Sample Data
Producer, Target BTC@Periscope/Events Manager, Recognition Events@Minneapolis Park & Recreation Board/Event Producer@Events by JLS
Required Result
Events Manager, Recognition Events@Minneapolis Park & Recreation Board
Sample Data 2
Sales Associate@Teavana/Event Assistant@City of Saint Paul
Required Result 2
Event Assistant@City of Saint Paul
Sample Data 3
Sales and Catering Manager@Bunker Hills Golf Course at Town & Country Caterers/Marketing Coordinator@Town & Country Caterers
Required Result 3
Marketing Coordinator@Town & Country Caterers
I have a userform with several fields located in it. For the most part, it looks like a user information form, Name, Date,, stuff like that. There is a field that the user enters some information, and using the afterUpdate function for the field, it searches to see if the value is unique. The problem is that when I tab from that field, if the value is not unique, I want the cursor placed back in that field (textbox). SetFocus doesn't seem to do what I want, unless I am using it incorrectly.
View 9 Replies View RelatedI have a question regarding Do While loops. I'm trying to write a do while loop to search through an array for a particular value and return the row number. This value is in the first column of the array and there are 211 of each value chronologically sorted. So, the first column from top to bottom reads 1,1,1,1..211 times, then 2,2,22,..211 times and so on. For this case, I want to return the first row where a particular value is found.
The loop I wrote is as follows: ....
i've been having for quite some time now. I need to input quantity of items into the existing database that holds all the items names which is sorted by category. Below is a sample of the problem:
Table 1 - Items with quantity that is waiting for input:
Quantity
Product
5
Button A White
7
Ribbon B Blue
8
Thread A Black
10
Cloth A White
3
Button B Blue
4Button C White
9Ribbon A Pink15Button A White
Above is just a part of it, the list goes on to over 200 rows long.
Table 2 - Existing database of all products:
Products
Quantity
I have a barcode scanner I have setup with Excel to scan PC barcodes at the office.
Previously what used to happen is I would get a list of all PC's on level 6, under data validation I had an error alert setup. The source code was =$A:$A (A:A being the list of PC's). So basically If I scanned a PC that wasn't on the list a error alert would popup saying STOP - Take down PC details. This worked Fine.
I now need to use it for the opposite.
I have a list of PC's that are missing in column A, If I scan a PC on level 6 or wherever and its in that list I want the Error Alert to popup to advise me to take down the details. So the =$A:$A won't work anymore.
I have a spreadsheet I've been using for some time with hundreds of VBA macros. For some unexplained reason, is has changed all my
Selection.Copy
to
selection.copy
which then interrupts the running of the macro as it can't decipher this command.
What I want to do is make a simple text link inside of a cell so when it's clicked on it executes the CommandButton(x) VBA script.
I'm guessing =HYPERLINK is going to be the best method.
Like so:
=HYPERLINK("Forms.CommandButton1","E-Mail")
I have a macro which copies one cell into another on a different sheet, provided it has content.
I want to change it so that it copies to the different sheet but puts the value in the correct row of the other sheet.
For instance sheet 2, A1 =TEST, A2= TEST2, A3= TEST3
Sheet1. B7 = TEST2, A15= This is a test
I want the macro to copy the value in A15 and copy it to B2 on Sheet2, as this is the matching row
This is what I had originally:
VB:
If Range("A15") = "" Then
Exit Sub
Else
Range("A15").Select
Selection.Copy
[Code .....