I use the If - Then statement in VBA to determine a condition which works perfectly fine for the particular cell I reference to, but i need it to apply to all the cells i.e. cells A1:A10?
Sub MACRO1()
If Range("A1") = "PAYE" Then
Range("B1").Formula = "=C2 * .128"
End If
If Range("A1") = "LTD" Then
Range("B1").Formula = "=(C2 - 100)* .128"
End If
End Sub
Sam
I am trying to update a project tracking sheet at work and I am having trouble. I have a list of construction projects (approx 130) along with details on the project, one detail being % completed. I am trying to create a summary of the projects at the bottom of the page, showing the total value of all projects, according to the % completed.
<10% is ANTICIPATED
= 10% is AWARDED
>10% <90% is IN PROGRESS
>90% is COMPLETED
[code]....
I want to apply a simple formula to a number of cells in an existing workbook that already contain values E.g.
My column contains the values
5200
1600
4376
in separate cells
I want to divide each value by 1.2 - without having to change every individual cell - can it be done?
I am using two separate workbooks. I am tranferring dates into a new spreadsheet to track projects dates for milestones.
The formula I am using is this: =IF('[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$CQ$4="", VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 94, FALSE), VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 95, FALSE)).
I would like the cell to turn green if the last part of the formula is true and stay clear if the first part of the formula is true.
I also need to add color beyond just that. I was attempting to apply conditional formatting but am a bit stumped. I want the green to be maintained regardless of other formatting. I would also like to apply to cells that are not color coded green:
Red - if the date is overdue
Yellow - if the date is within 7 days
Otherwise, leave the cell color as clear
Is this possible with conditional formatting? I am not at all proficient in VBA...
Is it possible to apply Filter utility on Column Cells that are 'coloured'?
View 9 Replies View RelatedI have thousands of path names in a spreadsheet that were converted to hyperlinks, but my client wants to see only the filename in the hyperlink, not the whole path.
I created the hyperlinks by applying the following to the column containing the file paths: ....
Supposing you would like to find the median of numbers in column D, but only for rows that have the word "Jones" in column A? Is there a way to do this? Obviously I know I could do a sort and simply specify the range myself, but we're dealing with nearly 2000 rows and a LOT of different values in column A
It occurs to me that it would be even better if I could ask excel, in effect, to identify every separate text string in column a and then find the median of the corresponding numbers in column D. In other words I wouldn't have to type any strings from column A at all.
If you have a range of cells with values in them (Q2:AZ2), you can use conditional formatting to identify the top x% of the group and it would essentially treat each cell in comparison to the group. I need to identify if a cell in the group is greater than 3*Stdev(Q2:AZ2). Do I have to create conditional formatting for each individual cell or is there a way to apply a function to the group that would recognize each cell the same way that the canned conditional formatting rules work?
View 1 Replies View Relatedhow can i do the following using VBA
making each cell in column A added to each cell in column B and the result will be in the column C
for example
c1=A1+B1
c2=A2+B2
c3=A3+B3
... etc
i know i can do that simply without vba code but I just want to use this method to implement more complex formulas .
how do I apply a formula I entered into one cell to all the cells in that column, without going through the tedious process of typing the formula in again in each cell ? I've done it before but don't remember how.
View 2 Replies View RelatedI was messing around with some Macros, as an issue came up where clients were pasting over Data Validated cells (thus removing the validation). Somehow though, the feature where you can select a cell, then pull the active cell down through the column (and then can copy, fill series, etc)has become disabled. I deleted all the macros, and turned them off in the Trust Center, but when I click on a cell the box and plus sign still won't display for me to pull the cell down the column.
View 2 Replies View RelatedSo I would like to automate the following, as doing it by hand takes a lot of time. I have searched the whole internet and finished empty handed.
Start from cell E10
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Apply double entry conditional formatting
Move 3 cells down
Repeat above until cell E5000
I'm trying to find ways of applying a formula in condition formatting to find entries which don't follow a certain format.
to be clear, this is for a reg plate of a car. There are two styles of formats. The X's represent A-Z and # represents numerical values
1. XX##XXX
2. XX####
find a way of applying two test cases (non-simultaneously) so that cells highlight if they don't follow either of the above conditions?
How do I count the number of cells that have a value greater than 0 in a range of cells?
View 2 Replies View RelatedI am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
I am trying to put an "If statement formula" within cells using VBA but it errors at the word "error".
Basically what I am trying to do is put similar to the following example into a range of cells.. for example:
=IF(LEN(Verbatim_LU!B2)>100,"ERROR","OK")
...with B2 being worked out by the for..with and the value (in this instance 100) being put in by VerbLen.
NB: Column A contains a reference number hence not having this actioned on it and counts start from 2
How I might be able to create an IF statement that would look at various cells and if empty, would go to the next indicated cell, ect, until it found data and then conduct the sum analysis indicated in my formula below.
The cells identified in my formula, which look for "", I want to skip to the next as stated above if empty.
=IF(J4="","",IF(N4="","",IF(S4="","",IF(AB4="","",IF(AF4="","",SUM(J4,N4,S4,AF4))))))
The problem with this formula is that if J4 is empty, it stops and does not continue on...
I have a statement that runs to check for empty cells but i want it to only run when cells in column A contain data and this can change from time to time.
Eg sometimes column A will contain 10 items, sometimes more sometimes less, sometimes none.
Need a statement that will only run my statement when these cells contain data.
HI can't seem to get this last scenario laid into this if statement. I am having trouble with the second statement. I need the three cells to equal 100, but only when all three are filled. If only two as in this scenario I would like % Error in cell G2.
Sheet3 *DEFG250%50%*O.K.Spreadsheet FormulasCellFormulaG2=IF(SUM(D2:F2)=0,"",IF(AND(OR(ISBLANK(D2),ISBLANK(E2),ISBLANK(F2)),SUM(D2:F2))=1,"% Error",IF(OR(D2>E2,F2>E2,SUM(D2:F2)1),"% Error",IF(SUM(D2:F2)=1,"O.K.",IF(AND(E2>D2,E2> Excel Jeanie HTML 4
I have a worksheet that has 5 rows and 5 columns. The five rows are Highschool subjects. Math, English, French, Phys. Ed, History. The Five columns are Monday, Tuesday, Wednesday, Thursday, Friday. I want to device a worksheet that say for Monday it will have YES for math, no for english, yes for French, yes for phys. ed, no for history. Tuesday would be say no for math, yes for english, no for french, etc, etc. I then want to use an if statement to convert the yes & no entries to a numeric value. I.E I have this right now. =If(B6="YES",1,0)
This simply puts a 1 for a yes and 0 for a no into cell M6. What I want tho is each subject to add the amount of yes's for the week and put it into cell M6. So if math is taken 3 times a week it would put a 3 into cell m6. if History is taken 4 times a week it would put 4 into cell m7, etc, etc.
I have a ss that has item descriptions, quantities and pricing.
Item descriptions are identified by a letter (a, b, c, etc) and in cells C20:c32. These are selected by drop down box. Item quantities are in cells E20:E32.
I want to evaluate cells C20:C32 and determine what letter is chosen. If A is selected in any cell C20:c32 I want to count the quantities for A in cells E20:E32. I can't quite figure out how to do this.
I have a sheet with thousands of rows and 45 columns and I need to delete a range of cells (G:Y) if the cell in column V = the cell in column X, and I need to do this by row. I think an if/then statement would work.
View 4 Replies View RelatedI'm after a formula that will look at a number of cells(that are not in a range) with the word "C" and if they all = "C" then I want the cell to come back with the result "yes" otherwise "no".
View 12 Replies View Relatedtask of inserting a block of cells into a worksheet.
I have saved 4 different blocks of cells and depending on an option chosen from a drop down menu i want to select one of more of these sets of data and paste them into a worksheet.
I want to conditional format cells with an if statement (something to that nature). I attached an excel file to aid in my question. Basically I want it to be color coded to alert users Red if no action is required because info is left out Yellow if info is available so take action Green if all is complete and nothing needs to be done.
If G2 does NOT have data then cell H2 has red fill
If G2 does have data then cell H2 has yellow fill
And finally if I2 is filled out cells A2:I2 are green filled
I have two cells with the following equations:
H3=IFERROR(VLOOKUP(A3&TEXT($A$1,"m/d/yyyy"),'CLABSI_Raw'!$A$1:$M$300,13,FALSE)," ")
I3=IFERROR(VLOOKUP(A3&TEXT($A$1,"m/d/yyyy"),'CLABSI_Raw'!$A$1:$M$300,10,FALSE)," ")
In an adjacent cell, I am trying to use an if statement
J3=if(and(H3<1,I3<1),"yes","no")
I am not getting a return in cell J3.
Would like to write an IF statement where it reads if any cells in a column are highlighted and gives the sum of those highlighted while ignoring the ones unhighlighted.
Is there any way to do this or an alternate method that would possibly work?
if it's possible to create a statement from cells in a workbook perhaps by using the hyperlink option or if there is any other alternative way.
In brief, the statement should recognise what has been inserted into each cell in the row and then generate a statement.
I hope this is clear. See attached a copy of the excel sheet from which I would like to create the statement.
I've got four columns of data. The first column contains pricing for a bunch of products from our company. The other three columns contain pricing for three other competitors. So for example:
Product | Our Price | Comp1 Price | Comp2 Price | Comp3 Price
Gloves | $4.59 | $5.00 |$6.00 | $3.56
Hats | $5.00 | | | $4.59
In column G right after Comp3 Price I have an IF statement that says if Comp1 Price is less than Comp2 Price, Comp3 Price, and Our Price, to print "Comp3" in that cell. If it is not less than the comparable data, then check Comp2 Price then Comp3 price in the same fashion. If Comp1, Comp2, or Comp3 is not less than Our Price then print "My Company Name." This will allow me to see who has the lowest price for that product and also tell me if that lowest price is lower than our price.
My problem is that I can't get it to work out so Excel ignores blank cells. So for example, Comp3 has the lowest price amongst our competitors for hats and is also lower than ours. But when I check for Comp3 price being lower than Comp1 and Comp2, it comes back as negative because Excel sees those cells as zeroes even though they're blank. My formula is:
=IF(AND(C2<D2,C2<E2,C2<B2),"COMP1",IF(AND(D2<E2,D2<C2,D2<B2),"COMP2",IF(AND(E2<C2,E2<D2,E2<B2),"COMP 3","MYCOMPANY")))
This would do exactly what I need Excel to do if it would ignore the blank cells. The problem is that Comp3 has pricing for hats because they offer hats, but comp1 and Comp2 don't offer hats at all, so naturally Comp3 has the lowest price; however, according to Excel Comp1 and Comp2 have the lowest price because the cells are blank and counted as zeroes.
So, does anyone know how to work this out so Comp3 will be counted as the lowest price for hats, ignoring the blank cells of Comp1 and Comp2?
I have been given a spreadsheet with the sales data for each of our salesmen. Each job has its state listed and what I need to do is break down the information by region. The catch is that the regions are not given - just the states. So I need to assign the states to their corresponding regions. To do this, I tried writing an "IF" statement like this:
=IF((B2="AL"),"SOUTHEAST",IF((B2="CA"),"WEST",IF((B2="NJ"),"NORTHEAST",IF((B2="TX"),"SOUTH CENTRAL", ... and so on.
So I was doing this for all 50 states. The problem is that the statement got too long. So how do I write this? There has to be a shorter/easier way.