I have random "X"s in a column of cells in a range called "Won". I wish to count these if the adjacent cell in another range alled "Valu" is greater than a value determined in the cell E3. The result to be entered in Cell G4. Can use Excel formulas or VBA.
I need a macro that will delete a range with some certain conditions. I have a set of dates in column B. The dates are in the format of 2009-01-31 09:15:00.
I need to search this column to find all the dates from the day before the computers date to the first day of the month before the previous days date. For example my computer date right now is August 10, 2009. So i need the macro to find all the dates from August 09, 2009 back to July 01, 2009 and select the entire rows that meet this criteria. Then I need to delete those rows.
Tomorrow when I run the macro it would choose all the data from July 01 - Aug 10, and this will go on each day until the end of december.
I have a spreadsheet which has data in rows. I need to sum the first 5 values from the left of the range which are greater than zero. BUT if there are less than 5 non zero values present I want to sum from the right of the range.
First condition: 1 2 3 4 5 6 7 8 9 10 The function would return: 1+2+3+4+5 = 15
Second condition: 0 0 0 4 5 6 7 8 9 10 Function would return: 4 + 5 + 6 + 7 + 8 = 30
Third condition: 0 0 0 0 0 0 0 8 9 10 The function would return: 10 + 9 + 8 = 27
I have had success with: =B4+SUM(SMALL(IF(I4:O4<>0,I4:O4),ROW(1:5))) (using ctrl shift enter) BUT it crashes when faced with the third condition.
I would also like to avoid ctrl-shift-enter functions if I can as I don't have much success copying them around the spreadsheet.
I'm creating a summary sheet that adds up all the data on sheets between start and finish where A2-A25 is SU and B2-B25 is 004*04. Background info: SU stands for SetUp, and 004*04 is a workcenter number (its not 004 times 04) I'm trying to calculate the total number of Setups for each workcenter.
In the screen shot I'm trying to find the row number where a particular price of an order has been reached. In this case, for the first order, my execution price is 1.8859, my stop loss is 1.8834 and take profit is 1.8884. I need to look and the future prices to determine which event had occured first (either the take profit or the stop loss). I though by using row numbers I would compare and which ever is smallest would mean that it occured first - the profit/loss is then calculated.
The other caveat is that an exact match may not always be available - for example, the second trade is stoped out because the highest price for the 12:35 timeframe exceeds the value I'm looking for. Still it would have triggered a stop loss.
******** ******************** ************************************************************************>Microsoft Excel - Misc.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6I6J6M6H7I7J7M7H8I8J8M8I9J9M9I10J10M10I11J11M11I12J12M12= ABCDEFGHIJKLM3DateTimeOpenHighLow*Order*PlacedOrder*PriceStop*LossTake*ProfitStop*Loss*Row*#Take*Profit*Row*#Profit/Loss42006.11.1512:001.88651.88661.8863*N***these*are*the*cells*that*need*the*formula*52006.11.1512:051.88651.88661.8856*N******62006.11.1512:101.88591.88591.8857*Buy1.88591.88341.88841080.002572006.11.1512:151.88581.88591.8853*N*...............
I have this macro to copy a range to another worksheet:
Sub transpose_UPCID() ' Hace el copy y paste transposed en BAUCS de los numeros de UPC"s y ' las cajas ATR Dim rng1 As Range Dim rng2 As Range Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown)) Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown)) rng1.Copy Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True rng2.Copy Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Application.CutCopyMode = True End Sub
The only problem is that this code copies all the items and I need this code to look into Column A in the Sales-Inventory worksheet. There I have a data validation with a total of six options. I also have six differents worksheets where I need to copy the values based on that data validation. To give you and example, all items containing XX in column A copy to XX worksheet. Something like that.
I am trying to find out how to write a formula that will sum the values in one column if the value in a corresponding column is within a range. For example, if the item number in the first column is greater than 2 and less than 7, the sum would be 179. I believe this would be some type of SUMIF but I cannot write it.
I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached. This has been driving me nuts for a few weeks now.
I am trying to return a cell address range based on values in different columns. Attached is an example.
In Column A1 I have "Range" and below that a set of numbers from 1 to 31.
In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.
What I want to do is as follows:-
I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.
I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.
I'm trying to populate a dropdown Combo Box by using conditions.
In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
what would be the most effective and easiest formula to use if I want to sum a range of numbers meeting 2 conditions (or more).. eg.. sum all the unpaid invoices (condition 1) due on March (condition 2).
I have the following code: =SUMPRODUCT(--($A$2:$A$400="OKC"),--($C$2:$C$400="AF33"),$G$2:$G$400)
It works great, but I am needing to now add a 3rd condition, or filter... In addition to it looking for "OKC" and "AF33" I also need it to sort by month. example; "March". I need it to be in code that works on old versions of excel as well...
where: K22:K169 are user entered categories from a drop down list G22:G169 are user entered dates F9 is a user entered update date column R are monetary values with first row being a revenue$ second row margin$
I need to summarize every other row (one row is revenue, one is margin) if the two conditions are met.
I'm (and you are too!) still helping to streamline a charity's spreadsheet. There are two worksheets that this question refers to: "DD Tally" and "List of DD Donors" (where DD stands for Direct Debit). In the DD Tally worksheet we are trying to summarise the information contained within the List of DD Donors. We would like to be able to estimate how much, in direct debit donation, we will recieve in the financial year (currently, April 06 to March 07). We would like this to be seperated into our monthly, quarterly, semi-annual and annual donors.
The challenge is knowing how to search through a column to find the, for example, "Monthly" entries and once these have been found to find the quantity of the donation(s) in the rows with "Monthly" in them, and then, to determine the number of months they have been paying for during the financial year (their first payment dates are listed as 01/MM/YY) - i.e, the difference between the end of the financial year (03/07) and the start (04/06) or between the time they joined during the financial year and the end. Then to multiply the number of months the direct debit has been active during the year by the amount they donate.
I've been writing a piece of VBA code to process a timesheet for my office.
THe sub routine has a number of variables passed to it, two of which are called CFhrs and Limit. The first contains in integer representing a number of hours. THe latter is a number of hours that can be claimed. In short if CFhrs is less than Limit, the CFhrs should be left alone, but if CFhrs is more than limit then CFhrs should then equal Limit.
First I tried this:
If CFhrs = Limit Or CFhrs > Limit Then CFhrs = Limit CFmins = 0 End If
This seemed to be totally ignored when run, and even when CFhrs was greater than Limit, CFhrs was left just the same. So I tried: