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:
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.
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.
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 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
Dim 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 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:
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.
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.
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.
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'.
I have a work sheet which includes a column of numbers representing certain daily events. I am building a user defined function to analyse the trend in the numbers by assisigning values from -2, -1, 0, 1, 2 based on comparison of two days.
Below is the function I built but it is not working, it is resulting in zero values in most conditions. I have attached the sheet which includes the numbers and the function.
I am trying to create a validation list, with its source data being filtered from another sheet of the same workbook. I also need to validate the drop list against some conditions in the source table.
Here is an example of the data am trying to create`(I want to create the droplist for the column Contract):
"Contract Definition" Sheet or Table: ----------------------------------- Company Contract Service Report End date: Status --------- --------- -------------- --------- ------ CompanyA ContractA1 Service 1 31/12/2014 Active CompanyA ContractA2 Service 2 31/12/2014 Active CompanyB ContractB1 Service 1 31/12/2014 Inactive
"Service Reports" Sheet or Table: -------------------------------- Company Contract Service Report --------- --------- ---------------- CompanyA ContractA1 Service 1 CompanyA ContractA1 Service 2 CompanyA ContractA2 Service 3
In the Service Reports Sheet, i want to create a list for each Contract, based on the specified Company and on the Status field, as defined in the Contract definition table.
I want to know how I can make Excel look at a list of information on one worksheet, extract certain items from the list, and place it on another workshet
For example, the following list would be imported from an inventory program as text and pasted on worksheet 1:
P+green beans d-2.99 c=2 cd+10258
p+cherry tomatoes d-3.99 c=1 cd+11204
p+red apples d-.69/lb c=3 cd+08902
p+tissue d-1.99 c-2 cd+20023
How can I have Excel take this information and create a list like the following on worksheet 2? ...
I am trying to write VBA code that will list all possible combinations of data contained in a worksheet based on a few conditions. I have a list that varies in length. In the first column is a list of numbers denoting a particular course number. In the second column is a list of numbers denoting the specific class number for the course. As an example, I have a 6 courses. The first course has 1 class, the second course has 3 classes, the third has 11, the fourth has 3, the fifth has 7, and the sixth has 6. Thus, my columns look like this:
A B 1 1 2 1 2 2 2 3 3 1 3 2 3 3 3 4 3 5 ...
I need to list out all possible combinations of classes given that I must have all six courses. EXAMPLE (Course, Class):.......................
i would like to be able to have two input parameters in the form of lists, i.e. step 1 and step 2. the contents of step 2 box will be dependant on what the user selects from the list in step 2 box.
In the example attached, if the user selects option 4 from step 1 box, then i would like the "letts" lists to be made available in list form in the step 2 box. for all other step 1 options i would like the step 2 box to return "no"
i defined my numer list as "num" and my letters list as "letts"
I have a worksheet which contains all the details of the medical equipment in the county that i'm responsible for.
This worksheet posesses amongst others; two columns titled location and sublocation.
I need a technique which will allow me to select a location from a dropdown list, select a sublocation again from a list and for these two conditions to then populate a list containing data such as asset code and description of all the items in the location and sublocation (possibly a little like an advanced filter, but only for the selected conditions)
This list will then be used to provide an engineers report on equipment holdings at various locations. I'm open to any ideas fellas.
I have a list of holiday data which has the following info. Name, Date from, date to, hours off per day. I need to look up this information and place the number of hours against each day for each person. The problem I am having is that I can't get the hours into the cells for which the date is between the date from and date to. I have attached spreadsheet and how I want the summary page to look. I just need the correct formula inserting.
I am trying to filter a range of text and copy only those entries from it that meet selected criteria into a new part of my page. The entries will all look like the following but will have varying numbers before the final Equity/Index part:
IBM US 3 C60 Equity IBM US 3 P60 Equity UKX 3 P5000 Index UKX 3 C5000 Index
I have constructed something using a modification of the find nth word function from this site that allows me to filter based on whether the fourth part (or for the third and fourth choices, third part) shows C or P at the beginning and then based on the value that is written after the letter. However, I have to break down the code a bit further to firstly check whether the final word of the string is Index or Equity to decide which word to check for C/P. I was wondering if anyone knew whether there is possibly a more simple way of running a find within one cell than using the nth word function. Would it possibly be a case of making two subs within a different module and then calling them to look for the specific word number based on what I have in F6 (my original equity/index ticker symbol: for example IBM US Equity or UKX Index)? Attaching code below. I'm sure it doesn't make a lot of difference but in case it helps I am currently running Excel 2007 on a Vista machine but I also use it at work on a Win XP PC with 2003.
This below code i find out from this form Skip the row find next but i need it i have column fom c to M if the cell k valur or j value = 0 or empty skip the enitre row go next row ifthe kvalue <> 0 or <> empty do something the below code where do i moify .
Set c = .Find(MyInput, LookIn:=xlValues, SearchOrder:=xlRows) If Not c Is Nothing Then Count = Count + 1: ff = c.Address k = c.Row Redim Preserve wsName(1 To Count): wsName(Count) = ws.Name Redim Preserve rng(1 To Count): rng(Count) = c.Address(0, 0) Do
IF the first letter (i.e. left most letter) in any of the cells in range F7:F17 is "R", return its content to cell L3 and put a line break between each (i.e. create a list within a single cell).
I've read line breaks can be put in by using &CHAR(10)& but can't get a full formula to work.
I have a validation list list in column E "Check, Charge, Credit Card, Other". in another column i have what i thought was a straight forward IF statement: =if(E3="Check",9999,"[ ]")
It does not recognize "Check" as the condition. Is there a tweak i can do to get it recognize the data validation.
HOW I CAN USE IN EXCEL VALIDATION DROP DOWN BELOW CONDITION =IF(B1="Assets",[Mapping.xls]Assets!$A:$A,IF(B1="Liability",[Mapping.xls]Liability!$A:$A,""))
