Delete Formula If Cell Value Meets Criteria?
Feb 18, 2014
I'm using the following on the Worksheet_Activate event to update rows with the date, (Cell AD1 contains the current date):
Code:
Range("X2:X2000").Formula = "=IF(IF(ISBLANK(W2),,$AD$1)=0,"""",IF(ISBLANK(W2),,$AD$1))"
Is there a way I can amend it so it only updates those cells which currently have no data?
At the moment it is changing every cell with the date which overwrites existing dates - I want it to update the new stuff only.
View 3 Replies
ADVERTISEMENT
May 21, 2008
I want to scan all sheets in a workbook and to delete a first row where a value in cell(1,1) is "table".
Sub DeleteFirstRowInWorksheet()
Dim SheetName As Worksheet
Dim i As Integer
For Each SheetName In Sheets
If Range("A1") = "table" Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If
Next SheetName
End Sub
It delete only in an active sheet. What's wrong?
View 5 Replies
View Related
Mar 5, 2014
Say I have a column of cells filled with numbers that are ascending. What I would like to do is to pick out the first cell that meets my condition. For instance, these cells contain irrational numbers that increase from 0 to 100. I would like a formula to pick out the first cell that exceeds 10.2, and to return the row number of that cell.
In the case of descending numbers, if I would like to pick out the first cell that goes below 10.2, would the formula be the same?
View 3 Replies
View Related
Apr 5, 2009
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
View 2 Replies
View Related
Sep 17, 2007
I need create a macro to perform the following - I don't know if it needs to be an Excel macro or via VB. This is to speed a process up and eliminate any spreadsheet messing around for our "admin" staff - they are not good with excel at all!
So, I have a daily/weekly down load of a spreadsheet (exported from access).
* I want to copy the values of column AK to A
* Then delete all entries in column D that equal 19, 20 and 6
* Then delete all entries in column AL that equal "A","B","C" or "D" but not "E","F" or "G"
* Then delete entire rows where the value in column A is a duplicate within the list
I will call this spreadsheet, for the purpose of this exercise, the "Master". I now have another spreadsheet with values in that should relate to values in either (or both) columns A and B in the "Master". I need Spreadsheet 2 values to be highlighted as cross checked in the "Master" and then all those in the "Master" that do not appear in the 2nd S/S will be normal white cells thus showing that they need to be chased by our Admin staff.
View 6 Replies
View Related
Feb 15, 2010
I want to create a sumif formula that will sum the data if it meets five different criteria. I tied to do an “Or” statement in the formula, but it doesn’t work. For example, I want to sum all the rows that contain: Apples, Bananas, Cherries, Pears, and Plums. How do I write the sumif formula so that it will do this?
View 9 Replies
View Related
Mar 27, 2014
I'm trying to find the correct formula to sum text that meets two criteria. If C2:C1023 is Equal to TEXT from J8 and F2:F1023 is equal to TEXT J4 add up the total times J4 shows up in C2:1023
=SUMPRODUCT(Sheet3!C2:C1023=J8,Sheet3!F2:F1023=J4,Sheet3!C2:C1023)
View 11 Replies
View Related
Apr 27, 2014
I have a worksheet (Data) that lists when pupils are in for Nursery sessions during the week. If they are in they have a 3 (hours) by their name in the relevant columns.
In the AM worksheet I now need to pull through a "register" so under each daily heading I need to pull through everyone that has a 3 next to their name under Monday AM / Tuesday AM / Wednesday AM etc. from the Data sheet. However, I don't want it to copy any blank cells. I then need to do the same for the PM sheet.
View 2 Replies
View Related
Nov 6, 2013
I have been using this function to count the number of unique text values in a data set:
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))
It works great if I want to count number of unique text values overall. However, I want to count the occurrences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).
View 7 Replies
View Related
Sep 29, 2011
Need to create a macro?
Delete rows where multiple column meets multiple criteria.
detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)
Those 2 criteria have to occur at the same time..
View 1 Replies
View Related
May 12, 2009
i am trying to get my excel formula to say if 2 date ranges in my excel workbook are the same (in seperate worksheets) to put a value from a certain cell into another cell in another worksheet. This is all in the same excel application...
The first step i have taken are:
1) if the 2 date ranges are the same to say 'true' if not 'false'
I am trying to get a forumula to say "if it is true to put a value from a different cell into another cell (both in different worksheets) but the same excel application...? but if its false to move on to the next date
View 9 Replies
View Related
Apr 12, 2012
I am trying to come up with a simple formula to count a single cell if it contains either a 2 or 3.
The cell can contain numbers ranging from 0-8.
Even better would be if I could some how evaluate single cells based on the contents and then count the number of cells where the criteria for contents is true. The problem is the criteria differs from cell to cell (i.e. D2 could = 2 or 3, but E2 needs to be counted only if it contains a 4 or 5).
I know COUNTIFS only evaluate a range but it would be perfect if I could somehow get it to work for single cells. COUNTIFS(D2,2,D2,3,E2,3,E2,4,F2,2)
View 3 Replies
View Related
Jan 14, 2009
We were so close!. But it appears that the assumed correct answer only works if there are no repeating N. The repeating N gets the same count as the last Y and it throws off the sum ....
View 14 Replies
View Related
Nov 21, 2007
I´m trying to do a macro that changes the color of all fonts in a row if the cell in the column "R" has the date lower than a specific one, something like this:
Dim Data2 As Date
Data2 = Sheets("Sheet1").Range("today")
Range("R12").Select
Do While ActiveCell <> ""
If ActiveCell < Data2 Then
ActiveCell.EntireRow.Select
Selection.Font.ColorIndex = 3
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
But it just don´t work. The macro does that in the first row, than it stops. Do you have another code for this, or another way to do?
View 7 Replies
View Related
Apr 30, 2014
In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.
Capture.PNG
At the moment I have a formula as below:
=SUMPRODUCT(--(B:B=$D2),--(A:A<>""))
this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.
Modifying my formula. I have attached the sample workbook below.
Book1.xlsx
View 7 Replies
View Related
Jul 17, 2013
I'm attempting to clear the contents from a range of cells on rows where a cell string may equal R, X, XX, Y, Z, ZX, #N/A.
The macro runs fine until it gets to a cell that contains #N/A. How to get this to work?
Sub Recalculate()
Dim r As Integer
r = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For Row = 13 To r
[Code] ........
View 2 Replies
View Related
Dec 26, 2008
I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0.
=IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)
View 5 Replies
View Related
Apr 21, 2014
I am looking for a formula that either accepts the number in a cell if the numbers on two other cells are "approved". In other words, I have on cell A1 my value to be tested. On cells A2 and A3 I have two numbers. On cell A4 I'm looking for a formula that copies the number on A1 if A2 is bigger then 0.25 and A3 is smaller then 0.35. In not, there's no copy or a N/A appears.
View 3 Replies
View Related
Mar 9, 2007
I am trying to make a simple program that will go down column A deleting the rows until it meets a cell with the word "Date" in the A column. When it has found that row I want it to delete that row also and then stop.
This is what I have so far
Sub FindAndDelete()
Dim row As Integer
Dim col As Integer
ActiveSheet.Range("a1").Select
If Range("a1") "date" Then Rows("1:1").Select
Selection.Delete
End Sub
I tried defining row =1
col = 1
and then going if range ("rowcol") but no joy.
View 9 Replies
View Related
Jul 15, 2014
I am having problems getting a formula to calculate the max if it meets 2 criteria. MAX of time listed by 1/past a certain date. 2/are of a specific client.
Here is my formula...
{=MAX(IF(All_Tickets_07012014!$D$2:$D$155685>1/1/2014,IF(All_Tickets_07012014!$O$2:$O$155685=$A2,All_Tickets_07012014!$G$2:$G$155685)))}
The data...
$D$2:$D$155685 - is the list of dates per ticket.
$O$2:$O$155685=$A2 - Are a variety of clients, where $A2 is a name of one of the clients.
$G$2:$G$155685 - Being a range of time stamps.
The formula runs, however it only shows the MAX of all entries, not within the specific date range...
View 2 Replies
View Related
Jun 19, 2008
I have a spreadsheet with about 225 sheets or so containing data. The date is in column J. I am needing to delete all the Rows that have a date greater than or equal to 06/06/08 and loop through all of the sheets.
I recorded a macro to find the date 6/6/2008, then highlight the row, then select the next 450 rows and delete them and it worked ok, but I don't know how to get it to loop through the sheets to the end.
View 2 Replies
View Related
Jul 8, 2014
In theory I know what I should do, but I don't know the syntax. So here it is:
There are 1450 unique records in my XLS, every record contains 45 different rows(these are the phases) with their position(1....45). Every row has a status (Not Started, In Progress, Complete)
COLA(uniqueid)....COLi(it is a number, it is the position).....COLN(status)
id1....1......status
id1....2......status
id1....3......status
.
. .
. .
id1 ....45.....status
id2.....1.....status
.
.
.id(n)
basically I would like to get the last "in progress", If not found, the last "Complete, If not found then the first "Not Started". and put a "Y" right next to the row to a new column for all the groups(45 rows)
View 12 Replies
View Related
Feb 28, 2009
s/s has 325501 rows. Column C contains names of people (whether present or not -I enclose small attachment to illustrate).
Column J contains scores (if present). I need column N to list the last row number where each column C name scored points (not just when heshe was last present). I think I need macros which I can fill down both columns (??).
View 2 Replies
View Related
Mar 24, 2008
In the attached Exel work book I have work sheets named
Material Usage – Usage of materials
Estimate - calculate the Total material cost for a job
Material Cost – defines the material cost for each material type
In “Estimate” worksheet Job number is repeated but sub jobs falling under a particular job number is unique. Materials used for each sub job is different.
Once the job number is selected from the list box , I need to calculate the total material cost for each job. I tried sumif function but I don’t know how to get it to look up for each material type and get the sum .
View 6 Replies
View Related
Aug 26, 2013
I am trying to create a macro to accomplish the following task. Delete if the value belongs to queue "ab cd ef" and is also a part of category "xxx xxx".
I wrote the following code, but it seems it is doing some mistake:
Code:
LastRow = Range("Z2").End(xlDown).Row
For i = 2 To LastRow
tempQueue = Range("C" & i).Value
tempRange = Range("Z" & i).Value
If (tempQueue = "ab cd ef" And tempRange = "0 - 1 day") Then
Range("C" & i).EntireRow.Delete
[Code] ........
The above code works fine but it deletes only 120 rows out of 200 or sometimes 150 out of 200. Can't get it to work all 200 rows.
View 4 Replies
View Related
Feb 7, 2009
here is a sample of the data
150
21
352
422
53
69
755
852
1224
13268
119
242
30
444
543
67.................
I know if I use dmax for only where first column equals 13 I get 460 but how do I get the second highest value for only those rows that have 13 in the first column (expect the answer to be 268). Then I want to do the same for 3rd, 4th highest etc.
I know large does it for one column and not only when the first column matches a designated criteria.
View 3 Replies
View Related
Feb 28, 2009
s/s has 325501 rows.
Column C contains names of people (whether present or not -I enclose small attachment to illustrate).
Column J contains scores (when present).
I need column K to list last row where column C name scored and column L to list last score.
I think I need macros which I can fill down both columns (??).
View 2 Replies
View Related
Jan 24, 2013
I have gotten a code. It goes into Sheet 1 and picks up all the Rows (entire row) in which Column A = 1.
Instead of copying the entire row, I need to just copy the row but only specific columns (i.e. Row 2, Column B,C,D,E,G).
Is there a way to specify where it pastes values in Sheet 2 (say from column D) because Column A-C have pre-filled data which do not change. And when it pastes, it has no gaps (despite a gap in the copied columns (i.e. Column F in sheet 1 was skipped)).
VB:
Sub cond_copy()
'assuming the data is in sheet1
Sheets("Sheet1").Select
Sheet2.Range("A2:G5000").ClearContents
[Code] .....
View 1 Replies
View Related
Apr 24, 2014
IN column J(on sheet 1) i want it to return text (OB) if Sheet 1 column A1 equals Sheet2 Columns A1:A500. And if Sheet 1 column A1 do not equal Sheet2 Columns A1:A500 return text(IB).
View 1 Replies
View Related
Feb 25, 2009
I am trying to add the tuition to be reimbursed if the claim date is on or after a certain date and then deducting the total from a set amount. I have included a simple example. I tried a formula from the pierson examples but Im doing something wrong.
View 4 Replies
View Related
