I am trying to have a value returned in cell C3 if two conditions are met. In Cell C3 (Sheet 1), if the value in Cell A3 (Sheet 1) is listed in column A on sheet 2, and the 12031 is listed in column B on sheet 2, I need the value of Open to be returned.
View 3 RepliesI have a spreadsheet that I am working on for an estimating tool for engineers in my office. I created an in cell pulldown that contains all of our pipe specs. The theory is that on another worksheet I have all the information for the labor costs, and by changing the spec in the pulldown will automatically change the numbers in the estimation table. Below is the if statement that I created. the only cell that I have the completed "if" statement in is C9, because it is adding almost double to the actual quantity that is supposed to be returned.
Code:
=IF(Sheet2!M2="SS1",VLOOKUP(A9,Sheet2!A3:J19,3),IF(Sheet2!M2="SS2",VLOOKUP(A9,Sheet2!A23:J39,3),
IF(Sheet2!M2="SS3",VLOOKUP(A9,Sheet2!A43:J59,3),IF(Sheet2!M2="CS1",VLOOKUP(A9,Sheet2!A43:J59,3),
IF(Sheet2!M2="CS2",VLOOKUP(A9,Sheet2!A43:J59,3),IF(Sheet2!M2="CS3",VLOOKUP(A9,Sheet2!A43:J59,3),
[Code] ......
I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.
View 2 Replies View RelatedDim b As Long
Dim last_rowB As Long
last_rowB = Range("K65536").End(xlUp).Row
For b = last_rowB To 2 Step -1
If Sheets("Main").Cells(b, 11).Value = Sheets("Main").Cells(b + 1, 11).Value & Sheets("Main").Cells(b, 11).Value <> "" Then
Sheets("Main").Cells(b + 1, 11).EntireRow.Delete
End If
Next b
If 2 cells are the same i want to delete the row of the 2nd cell, this works. But i dont want 2 delete cells that are blank, this doesnt work. For some reason it deletes everything in the table.
I need creating a formula that combines the following if statements in cell C107:
=IF(AND(B107
I have IDs in the first column of an excel chart. After that I have three more columns, being date of test, type of test (start, 3 months, 6 months, 9 months, finish), and lastly the result for the test.
Right now, the same IDs are listed multiple times for different results, so for example:
ID | Date | Type | Result
27 | 3/27 | Start |8.3
27 | 6/27 |3 Mon |7.9
27 | 9/27 |6 Mon |7.4
27 | 12/3 |9 Mon |7.2
27 | 3/27 | FINISH |6.5
What I need is the following layout:
ID | Start | Date | 3 Months | Date | 6 Months | Date | 9 Months | Date | Finish | Date
ID is only shown at left, and the values for the test result and corresponding dates are shown in their respective columns.
I tried to do an IF function with a LOOKUP inside, and it worked originally, but when I add more values for the same ID to the original column, it only shows the latest date, and only gives that result.
I have a table where the rows are conditions and the columns are experiment numbers, as an example below:
Experiment 1
Experiment 2
Experiment 3
Hardness
X
X
Solubility
X
X
Density
X
X
The table is fairly large. What I would like to be able to do is use a lookup/formula that will return all the experiment numbers a given condition is tested in. In other words, for a given condition (i.e., Hardness), which columns have an "X". Not sure if that can be done with a lookup or not or if there is another function necessary. I would like to be able to do it without macros though and also have it automatically update if I move the "X" around to different experiments.
I am trying to create an IF statement that will double the unit value of a certain box (J15) if the number of I15 is less than or equal to 160 AND either "A,B,C, or D" is selected from a list in H15. If it does not qualify with both, it would just be the single value of J15, not doubled.
So far I can only get it to work with one item from the list in the IF statement:
=IF(AND(H15="A",I15<=160),J15*2,J15)
However, I don't know how to add in the other list options B-D and get it to work.
Basically I need these four statements combined into one:
=IF(AND(H15="A",I15<=160),J15*2,J15)
=IF(AND(H15="B",I15<=160),J15*2,J15)
=IF(AND(H15="C",I15<=160),J15*2,J15)
=IF(AND(H15="D",I15<=160),J15*2,J15)
I am working on a search and replace macro. It's purpose is that within a region that varies only in row size, it's suppose to compare two values, A and B. A is only placed in one cell in the region, while B is a one column range in the region.
If A <> B, A should replace all B's in the region.
These regions are placed on top of each other in a long table. Please see the sample workbook, SampleDRW.xlsm
The code I am using is this:
[Code] ......
The problem is if the B value in the next region is the same as the one before but the A value change, my code don't pick up on it, because it only looks for changing B!
How can I expand the code to also pick up on this problem?
In the table I've created, I have a column (column A) that contains "Y" for the first 20 rows. I'd like to use an if statement in another column (column B) to create a numbered list starting with the first row (from column A) that is blank. For example, if column A contains "Y" in the first 20 rows, I'd like starting in row 21 (in column B) to return a value of "1" and then have the subsequent rows increase in value by 1, so row 22 would be "2", row 23 would be "3" etc. The catch is, if for some reason I make a change in column, let's say extend the "Y"s down to row 25, I'd like column B to reflect those changes by starting the list in row 26.
View 3 Replies View RelatedI'm tring to put data in empty cells using an formula. =IF((LEFT(A4,5)),"Class",(RIGHT(A4,5)))
View 3 Replies View RelatedFormula I've been labouring over which is only bringing back partial results - I don't normally have much call in my role to build nested formulas...
The following is what I have so far:
=IF(AND(AQ2="MortgageSession",OR(J2="LeedsCounselling",J2="Glasgow",J2="Limavady")),
"Mortgage",IF(AND(AQ2="SelfEmployedSession",J2="LeedsCounselling"),"Mortgage","Other"))
The formula is made up of two conditions:
1.
IF AQ is 'MortgageSession'
AND
J2 is 'LeedsCounselling'
OR Glasgow
OR Limavady
I want the cell to return 'Mortgage'
2.
IF AQ is 'SelfEmployedSession'
AND
J2 is 'LeedsCounselling'
I want the cell to also return 'Mortgage'
Everything else 'Other'
I've managed to get the formula to work to a point where it will happily bring something back without error boxes, however it is only bringing back the second condition and ignoring the first - something to do with the join in between these?
I am trying to include two conditions into this IF statement.
=IF(ISBLANK('SHEET1'!W8),0,1)
I want to include the cell X8 in this aswell.
I have multiple anded conditions both Boolean and numerical. For testing purposes, I wanted to be able to switch off individual numerical conditions in the If statement. It seemed like an easy matter that I could simply comment out the And statement of choice and recompile. When I tried this, I received a compile error.
Another option would be to use individual togglebuttons to selectively turn off the 5 numerical comparisons. I have no idea how to set that up.
how to do this with either commenting or togglebuttons?
The If statement
If OptionButton4.Value = True _
And ToggleButton1.Value = True _
And Range("G3").Value >= Range("N6").Value _
And Range("AV22").Value >= Range("AU22").Value _
And Range("E3").Value = "Long" _
And Range("G3").Value Range("BG7").Value _
And Range("H4").Value >= Range("L4").Value _
And TradeFound = False And _
DeletedFound = False Then
I am creating a document log that tracks all excel files sent and received.
I use RDBmerge to get the filenames and data from the file batches.
My current macro edits the data down to the last stage of data needed to create the log.
My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"
Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.
I have highlight the cell range on the "Final_Data" Sheet that I need to complete.
filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.
The criteria is this:
A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")
I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.
i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method
[Code] .....
I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.
I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10.
I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.
Attached is my worksheet. I need to program column C to adjust the value in column A according to the rules set in table J2:K17, but ONLY if the value in column B = mens. If the value in B does not equal mens, then leave the C cell blank. I've manually populated column C to show the end result that I'm looking for.
I tried a simple =IF(ISNUMBER(SEARCH("Small",A2)),"S",""), etc., but I can't figure out how to incorporate the additional condition that would let the cell return a value ONLY when a condition in B is also met.
NBVC helped me with a similar request previously, but with that formula, if the condition wasn't met, the cell returned "#N/A" instead of being blank.
I have a series of IF statements in a formula. I need one of the counters in it to start when the conditions is met.
i.e. the false section in an IF is along the lines of $B$3+J2, and then it continues down the column $B$3+J3 then $B$3+J4 etc. I need the J# to start increasing when the condition within the IF statement is met, not from the very beginning. When I get to, let's say the 13th row, when the condition is met, I want J2, not J13or14.
I need to add a condition to a statement that adds a "If TRUE" reference to another cell.
The other cell can be either a TEXT value "RM1" OR "TE2". If neither condition is true than the value is blank or zero.
So right now I have in the destination cell =IF(AB7="CONT",V7,"") That works lovely, but really isn't countable unless cell "Z7" ALSO has Either "RM1" OR "TE2".
If cell "Z7" has the text "DEA" or "SP" then the value of "V7" will be placed in a different column. I will use a different destination column (for this modified "DEA" or "SP" condition)
So, Excel reads my formula. It tells me it's correct, but it has too many statements. [I know this is true, because the equation is absolutely huge. )
The thing is, I need it to tell me, based on two separate columns - one being "State" and the other being "A vs. O", who this contact belongs to. [For each state, there is a different person for A and for O, and the entire spreadsheet is text.]
My ridiculously large, almost functional function is:
=IF(AND(RC[9]="WA",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="WA",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="MT",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="MT",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="ID",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="ID",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="OR",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="OR",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="APP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="OPP"),"Tony
[Code] ....
As you can clearly see, there are a lot of terms, but I need both the "State" and the "A vs. O" column to determine the outcome of the column the formula will be in.
so what will happen is that the number of rows will continuously increase as the groups of data (Labeled Group #) increases. The data within each group will vary from time to time so some groups may contain only one line while others may contain multiple lines. I have included an attachment that illustrates what I'm trying to achieve. When data is entered in Column A under "Q," the adjacent empty cells in Columns B-F (labeled "R"-"V") will turn red. This is the part I have (i.e. Group 2).
View 4 Replies View RelatedTrying to program cell C1:
If A1 is between .81 and 3, AND if B1 is Adult, then C1 is Priority Mail
If A1 is greater than 3, AND if B1 is Adult, then C1 is FedEx Ground
If A1 is Less than .81, AND if B1 is Adult, then C1 is First Class Mail
If A1 is Less than .81, AND if B1 is Child, then C1 is Hold for Inspection
etc.
There will be a list of ~45 conditions that will populate a specific value in C1. Can this be done? If so, how?
if I want 2 conditions satisfied in order for a value to be returned, would I need to set up/name tables, set ranges, etc? e.g,
If A1 contains baby, AND B1 contains Carly, then C1=carly.jpg
If A1 contains girl, AND B1 contains Carly, then C1=carlyg.jpg
If A1 contains baby, AND B1 contains Billy, then C1=billy.jpg
If A1 contains summer, AND B1 contains Wally, then C1=winter.jpg
etc.
Roughly 40 different conditions will be needed. What formula would I use and how should I name and set up my table?
=AND($AE$2:$AE$10<=$F$2:$F$2000;$AE$2:$AE$10>=$E$2:$E$2000) With this I am checking for conditions to be true or false
Lets say I am checking AND(AE2 <= F2 ; AE2>=E2) this tells me if date in cell
AE2 is smaller or equal to Cell F2, but how can I set this fromula to check if all the cells from F2 to F1000 are equal to AE2 so If I write date into Cell AE2 it checks all dates from F2 to F1000 and check if condition is true or false
(and if I write date in cell AE3 it should check all the dates from F2:F1000, if date in AE3 is in this range)
I need to sort data similar to below using the following conditions. If the total of A's is greater than 4 they get an A. If the total of E's is greater than 4 they get an E. If they have more A's than E's, assuming they have at least 4 of each then they get an A. If the total is 5, but they don't have 4 A's then they get an E
NAME E'sA'sTOTAL
John 235 ( so this one should get E)
Mary 459 ( This one should get A)
Tim 112 (This one shouldn't get anything)
Jane 145 ( This one should have an A)
Sam 415 (This one should have an E)
I would like this to sort in one cell - I can do this over 5 cells - but not in one.
Trying the following syntax and I do not get any value in the cell if either of the conditions are true. Pretty sure I'm nesting the functions incorrectly. Maybe I should use Index/Match? =IF(AND(E24="SI",F24="SI"),"ER2","",)IF(AND(E24="sn",F24="sn"),"er2","")
View 3 Replies View RelatedIn column A, I need to highlight the cells green if the value is less than < the values in columns B and C, yellow if A = either B and C, and red if A is greater than B and C. But I only want the formatting to stay in column A. I'm using Excel 2010 and Windows 7.
A
B
C
Green->
444
512
482
Yellow->
364
571
364
Red->
101
99
87
I am trying to write a Macro that would go into same specific cells in multiple worksheets and paste the data into one summary worksheet if ofcourse a criteria is met.
The multiple worksheets have the same format. The worksheets are numbered 001, 002, 003, 004, etc (increase on a daily base) ..
The following IF statement isnt working, so im missing something. If A2 says YES, i want the cell with this statement to be blank. However, if A2 has a date, I want this cell to be that date + 45 days. How can I make this correct?
=IF(A2="","",A2+45,IF(A2=yes,""))
I work at a cardboard manufacturing plant in the Q.A Department. Occasionally paper claims need to be raised due to the paper being faulty (out of spec.). I have attached a spreadsheet which at present works out the paper cost according to which paper grade is typed into a particular columb. But, paper price varies according to the width (i.e. Deckle) of the paper as well. And this is the variable which I have not been able to implement thus far.
The relevant columbs are 'D', 'E' & 'I' for the purposes of this formula... It should be noted that below this I have pasted in the paper pricing list... and that in general each paper grade has 3 different prices depending on the Deckle (i.e. paper width), so there are 3 possible prices which can be displayed depending on the Deckle inputted into columb 'E'.