Multiple Condition, Multiple Range Formating
Apr 10, 2009
In range C15:C45 I have names of some steps. Starting from column D, row 6 have a drop down with 6 possibilities. Now based on what is selected in row 6 (so cell D6, E6, F6 and so on), I would like to color certain cells from 15 to 45 in that particular column (certain steps that are applicable to option selected in row 6 of that column). And as month goes by, users do that in more and more columns.
Also, steps overlap between conditions that are selected in row 6 and I would like this to macro (or formulae) to be running so that as soon as user selects the condition cells are colored and then they can fill out only those cells. So, I couldn't figure out any way to do this by conditional formating as there are more than three conditions and range is not continuous.see attached file as an example of final output.
View 4 Replies
ADVERTISEMENT
Feb 12, 2009
I am having problems with choosing multiple values for the second condition in the b range. The formula works for one account in the array, but not multiples.
=SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--('Data Sheet'!$B$2:$B$997={"300100","302100"}),'Data Sheet'!D$2:D$997)
View 3 Replies
View Related
May 31, 2013
VBA code. I think I need a worsksheet change event. Here is what I'm looking for:
If cell K1 = 3 and if the value of any cell in column A is < 80000 or is > 86666, or if the five digit number begins with an alpha, then a msg box will pop up stating "Invalid range".
More background:
The user will be manually inputting 5 digit numbers into column A, but if they've selected cell K1 to equal 3 from a dropdown menu (which the user also does), then values entered that are less than 80000 or greater than 86666 are 'out of range', and the user should be alerted and if possible the number they entered gets cleared.
Additionally, some of the numbers the user could type in might begin with an alpha prefix, such as "G1234" - these would also be considered out of range when K1=3, so if the conditional statement could also recognize this it would be useful.
Column A will be blank until the user types in the 5 digit number, so the code only needs to trigger if the number typed into a cell in column A is outside of the aforementioned range.
So to sum it up once again, I'm looking for a msgbox (and clear cell) if the following criteria is simultaneously met-
*If K1 = 3 (meaning that K1 equaling anything other than 3 wouldn't cause the msgbox)
*And values typed in column A are less than 80000 or are greater than 86666, or begin with an alpha (meaning that values typed in between 80000 - 86666 wouldn't cause the msg box.)
View 1 Replies
View Related
Oct 10, 2007
I need to find a value in a pivot table with a range of values over 12 months (Book5) when 3 criteria are met, Branch#, LOB# and Month#. Sum(if() works for the 1st month, but I do not know how to do the coding when the month changes.
Attached are copies of the two files I am working with and my coding so far....
View 5 Replies
View Related
Feb 12, 2010
Can this code be simplified? I currently have this code repeated 15 times, I need to color the ranges yellow and then unlock them. So for now I am repeating the code below for range ("C19:N26") and then 14 other ranges.
And if you have suggestions on cleaning up the code below please let me know, I would like to know the proper/most efficient way to write code.
Sub FormatingBudgetFiles()
'Colors each Forecast cell yellow that the end user can change
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
With ActiveWindow
Range("c14:k14").Select
End With
View 9 Replies
View Related
Feb 20, 2009
i have a list of customers with various information, i would like to rank these customers 1 to 5 and change the colour of the cells dependant on there rank.
Conditional formatting allows me too do this but is limited to 3 formats.
I have Columns A-K filled with data and want the condition to be set on column B if Column B meets a requirement it will then colour that Row (A-K)
So for example Row 3:
B3 = the requirement to change colour to red so cells A3:K3 will turn red
B4 = the requirement to change colour to gree so cells A4:K4 will turn red
and this must go down to row 1000 or whatever it may be.
View 9 Replies
View Related
Sep 14, 2009
I'm trying to get it so that a cell becomes automatically highlighted if 2 other cells conditions are met. For example, I want cell A5 to become highlighted if BOTH cell B1=Loan 1 AND cell B5=Yes. Here is a mockup of what I'm talking about incase it makes it easier to understand. I'm using Excel 2007.
View 3 Replies
View Related
Nov 5, 2008
I have two columns from which criteria must be satisifed. column A denotes gender (m or f) and column B denotes a number of points. I want to sum those males who achieve >4 in one cell and those females who achieve >4 in another cell.
View 3 Replies
View Related
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
View Related
Dec 22, 2013
formula using if condition. I attach the sample file about my problem.
View 14 Replies
View Related
Jan 25, 2007
I am trying to sum based on a set of conditions including time. The formula works fine except when time is between 11.30 pm to 12.00 am.I have attached the sample which will make the scenario clear. I am not sure how to modify the formula to ensure it works when the time is between 11.30 pm to 12.00 am.
View 8 Replies
View Related
Jun 4, 2008
I am trying to write a formula, that looks at row A1:A6 (Country), and equals A11 (Country chosen), but also looks at Columns C:E (Month), and equals B10 (Month Chosen). I can then add the figures in C3:E10. However I want a YTD idea, and so ADD Month 1 through to Month chosen in cell (B10) for the relevan contry chosen (A11). Uptil now I have used SUMPRODUCT, but am only able to sum one column, and not the required numbe rof columns.
View 6 Replies
View Related
May 27, 2014
I got four product name in column & want to bring other product name equal to same same product in different colum
like if products name in column range is PRV IT should bring birkett in other colum
PRV SHOULD BE IN NEXT COLUMN AS BIRKETT
TANK " " "" WAM
INSTRUMENT " " " " AGI
HVC " " " BAILEY
and rest of should be Marston
I had put that formula but didn't work 100%
=IF(C5>="PRV",IF(C5>="TANK",IF(C5>="INSTRUMENT",IF(C5>="HVC","BAILEY","AGI"),"WAM"),"BIRKETT"),"MARSTON")
View 8 Replies
View Related
Jan 9, 2009
I've been trying to figure out how to do a vlookup using two conditions. Reading some other posts, I saw this could be done by combining a MATCH function but I'm still having trouble. Here is what I'm trying to do: I am trying to match cash amounts based on two look up values....one being the identifier (cusip) and one being the pmt code. http://i105.photobucket.com/albums/m204/RAK_08/data.jpg
This data is formatted exactly the same but comes from two different sources. I need to match the pmt amounts for a cash reconciliation.
View 4 Replies
View Related
Jul 18, 2014
I have added this example:
Test lookup main supplier.xlsb
I have a list of 5000+ items that I need to check (only 6 of them in this example in the red table).
I have a list of 25000+ items in a lookup table (only 10 of them in this example in the blue table).
Each item can have more than one supplier, but has only ONE Main supplier.
I need VBA code that determines if the supplier in B1 already is a main supplier for that specific item in the lookup list (yes or no).
I tried filtering it but that appears to be too slow for 5000 x 25000 items.
I have tried with VLOOKUP and Match, but I do not know how to combine the two conditions.
View 7 Replies
View Related
Apr 16, 2006
in cell A1 and cell B1 I have a "yes", "no" or "maybe"
in cell C1 and D1 I have a number
if cell A1 and cell B1 "yes" multiply cell D1 times 2 (all results in cell E1)
if cell A1 "yes cell B1 "no" multiply cell D1 times 1.2
if cell A1 "no" cell B1 "yes" multiply cell C1 times 1.2
if cell A1 and B1 'no" multiply cell cell C1 times 2
if cell A1 "maybe" cell B1 "yes" multiply cell D1 times 2
if cell A1 maybe cell B1 "no" multiply cell C1 times 2
if cell A1 "yes" cell B1 "maybe" multiply cell D1 times 2
if cell A1 "no" cell B1 "maybe" multiply cell C1 times 2
View 6 Replies
View Related
Apr 30, 2007
You all were so great last week (thanks Ninja). This is a tracking form for a contest based on restaurant cost results (Page attached). I need a formula for if C2>D2, but less than 1/2%, give100, if C2<D2, give 200.
View 9 Replies
View Related
Jun 29, 2007
I have a worksheet where there are rows of information in a specific range (A5:G600). I'm using the conditional format of =AND(MOD(ROW(),2), COUNTA($A5:$G500)) to alternate the row colors. I would also need to have the information in column A formatted. Column A contains dates, and if the date is 90 days less than or equal to today's date (90 days or more old) I would need to format that also.
I've tried using both at the same time, but the conditional formats will override each other when applicable. Am I able to do this via conditional formats, or would I need to do one or the other in VBA?
View 8 Replies
View Related
Sep 18, 2007
I am trying to use an OR condition within a sumproduct formula. Why am I not getting correct results?
(see attached)
I want to sum all costs for each of my PO #s:
If the Rebate Type is IPT, Wireless, or Security, then the result goes in the "Fndtn Ext Cost" column
If the Rebate Type is IPT Advanced, Wireless Advanced, or Security Advanced, then the result goes in the "Adv Ext Cost" column.
The formula I am using is as follows:
=SUMPRODUCT(($A2=PO)*(OR(Rebate_Type=$F$3,Rebate_Type=$F$5,Rebate_Type=$F$7))*(Ext_Cost))
But for some reason, even though the OR statement is evaluating to FALSE (I tested it by itself), it's still summing ALL the extended costs for that PO #.
View 9 Replies
View Related
Apr 7, 2014
I have data like this in data have tab in my sample file
POSSIBLEREDYELLOWGREENPINKVIOLET
GRADE/GRADEA/CG/GA/GG/GA/A
GRADE/GRADEC/CG/GG/GG/GA/A
GRADE/GRADEA/CC/CA/AA/AC/C
GRADE/GRADEA/CG/GA/GA/GA/C
GRADE/GRADE-----
and i have this data in lookup tab in my sample file
possibleREDYELLOWGREENPINKVIOLET
COLORC/CG/GG/GG/GA/A
GRADEA/AC/CA/AA/AC/C
now i want to do like this
if B2 value (red column) of datahave tab is equal to B2 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to A.
if B2 value (red column) of datahave tab is equal to B3 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to B.
if B2 value (red column) of datahave tab is not equal either B2 and B3 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to H.
[code]....
i tried normal Hlookup but i do not know how combine multiple IFs to solve my problem.
View 4 Replies
View Related
Nov 6, 2013
Is there a way to filter data based on multiple OR condition with a like parameter.
I have data which has near about 50000 rows and now i would like to filter on a header called "Activity Type" and would like to fetch result for a text which resembles "from ABC group" or "From PQR group" or "From XYZ group".
View 5 Replies
View Related
Feb 1, 2009
I have 4 IF(AND) conditions that work fine separately but I can't seem to combine them into one.
=IF(AND(H56="New",I56="RE",Q56<=5),”YES”,”NO”))
=IF(AND(H56="New",I56="NON",Q56<=1),”YES”,”NO”))
=IF(AND(H56="Renew",I56="RE",Q56<=3),”YES”,”NO”))
=IF(AND(H56="Renew",I56="NON",Q56<=2),”YES”,”NO”))
View 2 Replies
View Related
May 14, 2011
I am trying to round a number to next multiple of 10 with condition i.e. for example if number is 1230.56 then it should round off to 1230 but if the unit number is more than zero i.e. 1231.56 then it should round off to 1240.
That means the main number should round off to next multiple of 10 only if unit number is equal to or more than 1.
Example 120.11 should round off to 120
121 to 130
120.99 to 120
119 to 120 and so on
View 8 Replies
View Related
Feb 27, 2008
I have this problem on putting the right formula. Column A entry are dates corresponding to replacement of parts x at column B with running hours of parts x at column c respectively. the table will look more like this:
A B C D
1 jan 2, 2008 brake front 200 ?
2 jan 20 , 2008 clutch 150
3 jan 30, 2008 wheel front left 300
4 feb 2, 2008 brake front 50
5 feb 5, 2008 brake left 85
6 feb 15, 2008 clutch 300
the formula will first look at column B and look for the entry "brake front" and every time it matches the entry it then looks up the date and then displays the corresponding latest running hour value on column C at cell D1. D1 formula will be having a result of 50 as it is the latest running hour value compared to 200.
View 9 Replies
View Related
Mar 28, 2008
we can get if functions to return conditions, as in if so and so "a" etc... is it possible to return long statements, instead of letters, with fill stops and commas. i don't know if it would be appropriate if i put an example of what i was trying to do. i am not trying to be rude but this
will this work in c3
=if(b3=5,"Readily understands classroom statements, questions and instructions.Can usually understand short electronic texts without difficulty.",if(b3=4,"Understands most classroom statements, questions, instructions.Can usually understand short electronic texts with only occasional difficulty.",if(b3=3,"Can generally understand classroom statements, questions and instructions, but may need repetition.Can understand short electronic texts reasonably well.",if(b3=2,"Sometimes misunderstands classroom statements, questions, instructions.Has some difficulty in understanding short electronic texts.",if(b3=1,"Has noticeable difficulty in understanding classroom statements, questions, instructions.Has great difficulty in understanding short electronic texts.")))))
View 9 Replies
View Related
Mar 17, 2013
I have workbook with multiple sheets with full of formulas. I need a excel macro which can convert formulas to values based on multiple conditions.
The formulas are in B2 and below.This formula provides results as soon as a value is entered in C2 and one among D2 and E2 cells (see attached excel)I want a excel macro to convert the values provided by formula to actual values as soon as results are obtained.Further, when user delete data in C2, D2 and E2, i want the formula to be working again since if a user enters different data in C2, D2, or E2 the value should get updated.
I found a similar post which addresses some part of my problem but works only with one condition. [URL] ....
View 8 Replies
View Related
Jun 4, 2014
My data set has a number of duplicate entries. But I would like to sort them out based on some conditions. Say for example my C2 code appears three time in the dataset. I would like to sort this multiple code using the time and i column.
Say for example, 871514 code appears three times in this dataset, and if this code appear within three years - time with a different (i column text e.g. public and private), I would like to exclude these three duplicate entries from my dataset. If this code appears within next three years from the date, but have same I column text (e.g.public versus public), I will keep them.
So each code will be considered based on three years of time and type of text in I column. If the code appears again later after three years, I will keep them.
So the codes need to be checked within three years time with i column text.
My dataset identifies the difference of dates between last entry and next entry.
View 1 Replies
View Related
Jan 23, 2014
I have 4 conditions in one formula, and only the first condition (when met) evaluates correctly. When the other conditions are met, I get a VALUE# error.
I want to concatenate 2 cells, based on whether there is an "a", "b", "c", or "d" in a particular cell. see example. Below is my formula:
=IF(FIND("a",A4),B4&$B$1,IF(FIND("b",A4),B4&$C$1,IF(FIND("c",A4),B4&$D$1,IF(FIND("d",A4),B4&$E$1,B4&$B$1))))
View 6 Replies
View Related
Feb 17, 2014
I am having trouble evaluating two conditions with nested logical operators to produce the output I need.
For example Condition 1 has 5 choices (A,B,C,D,E) and Condition 2 has 3 choices (a,b,c).
Breaking it down,
[Condition 1] option "A" always applies and should give "Yes" (No matter [condition 2], "A" always gives "Yes")
[Condition 2] option "c" always applies and should give "Yes" (No matter [condition 1], "c" always gives "Yes")
[Condition 1] option "D" never applies and should give "No" (No matter [condition 2], "D" always gives "No")
[Condition 1] options "C" and "B" only apply if [condition 2] is "b" (if so, the answer is "Yes")
What I need is the equivalent of:
if ((([condition 1] == A) || ([Condition 2] == c)) || (([condition 1] == B || C) && ([condition 2] == b)), "Yes", "No")
View 4 Replies
View Related
Mar 16, 2014
I Want To Use User Form to add data with multiple condition........
1st condition : select name, AHSAAN G, ALI G, SHAHID G.......
2nd condition : select , ONFLOOR BC, ON FLOOR VC, ON LINE VC
3rd condiotn : select, PT P2 P3 PB HR LK
In this use form the i used define name "name_1"
1 = I Added this to combobox 1. if if a aded a new name who is not in the list "name_1" then he asked for added this name or name. if i click yes then added the name in "name_1" list.
2 = When i added the data to my sheet by default all field of UserForm don't blank....
View 4 Replies
View Related
