I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 RepliesI consider myself a beginning intermediate Excel user and am really learning a lot through this forum but have not run accross the answer to what I'm trying to achieve. I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet. It seems Vlookup is the approach to use in combination with multiple if conditions or an array. But I am not yet comfortable with such a complex equation. Can someone provide me with some guidance or an equation that would work. So far this is how I've been learning. Looking at equations and deciphering how they are written.
View 5 Replies View RelatedPlease refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet.
1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12.
2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
I am trying to sum the error points of staff in their work done based on a specific time frame. For example,from 4.00pm to 4.30 pm as per the attached. I tried sum product but did not work. Is there an addition formula that I need to use when incorporating time ?
View 6 Replies View RelatedIs there a way to have a sumif formula dependent on two conditions?
View 9 Replies View RelatedI have three columns of data
Col-A-----Col-B------Col-C
1---------(blank)-----a
3---------A----------b
1---------A----------b
2---------B----------c
2---------(blank)-----c
4---------D----------a
3---------E----------a
1---------A----------b
3---------B----------c
I am trying to calculate two things:
first, a simple count of entries (non-blank) in Col-B corresponding to 'a' in col-C. Ex. for Col-C='c', the no of entries in Col-B is 2. Here I was trying SUMPRODUCT(--(Col-B range""),--(Col-C range="a")) with CTRL+SHIFT+ENTER. It doesn't work.
second, a more complex count without repetitions. Ex. for Col-C='b' and Col-B='A', the no of entries in Col-A is 1, because there are two 1's. If there would have been three 2's and four 3's the count would be just 2.
I am working on a formula that has just gotten beyond me. This is what I want it to do:
If:
=IF(K9<I9,
Then:
IF((I9-H9+T8+U8)>=180,(180-T8-U8-(K9-J9)),(I9-H9)),IF((K9-J9)+(I9-H9)>=180,IF((K9-J9)>=180,0,180-(K9-J9+T8+U8)),
Else:
IF(I9-H9)>=(180-T8-U8-(K9-J9),(180-T8-U8-(I9-H9),(I9-H9))
This is what I currently have, but it's not working:
=IF(K9<I9,IF((I9-H9+T8+U8)>=180,(180-T8-U8-(K9-J9)),(I9-H9)),IF((K9-J9)+(I9-H9)>=180,IF((K9-J9)>=180,0,180-(K9-J9+T8+U8))),IF(I9-H9)>=(180-T8-U8-(K9-J9), (180-T8-U8-(I9-H9),(I9-H9))))
I am attempting to create a multi condition IF statement for work. Essentially what we have is a column dedicated to the date in which a piece of equipment is supposed to go into service.
What we want to do is make it so that if the current date is 40 days past the scheduled in service date, a status column displays the term "Verify".
Additionally, if the scheduled in service date is 365 days from the current day, a status column should display the term "Future".
If it is any day in between the two, it should display the term "Active"
I have two seperate lines of code, one that covers verify and active and the other that covers future and active but I can't combine the two so that I have one function. The code I compiled is included below, any help would be appreciated.
=IF(D7=0,"Active", IF((TODAY()-40)-D7>0,"Verify","Active"))
=IF(D8=0,"Active", IF((TODAY()+365)-D8<0,"Future","Active"))
(D# corresponds to the respective cell that contains the scheduled ISD)
i tried to compare 2 different cells with text inside.Each cell contains "yes","no"," ", 6 possible values
For example
A B C
1 YES NO
2 YES YES
3 YES " "
4 NO YES
5 NO NO
6 NO " "
In column C, i want to enter the result f.e. if a1="yes" and b1="yes" do 1,if a2="yes" and b2="no" do 2 and so on There is a function for this?
Currently for the first line on the sheet I'm working with, I have the following:
=INDEX(SALESMEN!$D$2:$D$500,MATCH(TRUE,SALESMEN!$H$2:$H$500<>"X",0))
which is completed with CTRL+SHIFT+ENTER, and it works properly. However, I want to do the same for all lines following, where it will go to the next item matching that value. For instance: on my SALESMEN sheet, I have John Smith, Fred Johnson, and Mary Williams. Fred Johnson is considered inactive, which is represented in the SALESMEN sheet as an "X" in column H. Therefore, I want line 1 to show John Smith, and line 2 to show Mary Williams.
I am trying to figure a way -- preferably in a single- cell formula -- to calculate a conditional volume-weighted average of a series of associated prices and quantities located in two separate columns based on the date (or dates) the transactions occurred.
I can do a simple Vol. Weighted Average (VWA) of all the prices and quantities over the five-day period with the following formula: (I have created named ranges for the price column (A1:A30=price), the quantity column (B1:B30=quantity) and the date column (C1:C30=date):
= SUMPRODUCT(price, quantity)/SUM(quantity)...
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
I need a formula to workout what is the most up to date firmware that should be installed on the machine. This will always be in column B. The second formula should work out the variance between the installed firmware and recomended. The formula cells will be normally held on a different summary sheet.
View 2 Replies View RelatedI'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
Please refer to attached spreadsheet
I receive data from an external source and it displays what appears to be dates in column B.
In column G I apply a formula to display month/year info.
It turns out that some of the data in column B is valid date data, but other data imports as plain text and therefore I can't get the month/year info that I require.
I have attached just a small example.
I get this data monthly and it usually covers thousands of rows and therefore impractical to change manually.
I got 2 colums:
Column A: Column B:
EUR 1.00
USD 2.00
USD 3.00
EUR 4.00
As you may have guessed I now want Excel to check in column B the content of column A and to change format accoring to content:
Goal:
A B
EUR 1.00 €
USD 2.00$
USD 3.00$
EUR 4.00€
I want to stress, that a solution using macros isn't possible.
i cannot find my old msg , so i re-read again.
let me repeat my question one more time .
right now i can achieve is :-
when i enter in cell $A$6 " win " and i set the conditional format when this cell text = Win. i highlight "RED color "
i can set the above without much problem.
what i want to a bit more is when $A$6 ="Win" i wanted , the Row A6 till F6 cell all highlighted with red.
I have a 20000-row sheet generated by a daily report and I only want to work with about a quarter of this data; any rows that contain "PH4" in the M column. What's the best way to automatically single out this data to work with?
My initial solution was to create a seperate sheet to pull off all the columns with PH4 in the M cell, eg cell A8 would be:
=IF([WEB_StoSta_Rep.xls]Sheet1!$M8="PH4",[WEB_StoSta_Rep.xls]Sheet1!A8,"")
Then I could run lookups off that sheet. However, this feels like a sloppy solution to me, and Excel throws up an error when saving, unable to save external link values with available resources. Is there a better way of going about this?
I am trying to work out a custom number format to display betting fractions properly - namely 3/2 as 6/4 and 2/3 as 4/6. The following works for 3/2:
[=1.5]"6/4 " ;??/??
However I can't get anything to work for 2/3 as it is a recurring decimal.
i'm trying to figure out a conditional formatting that if a cell is greater or equal to another cell - color a cell "GREEn"
otherwise...color the cell "red"
Given three criteria (category, name and date) I want to find the specific value within the table or array. refer to attachment.
View 4 Replies View RelatedSo I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
I set format condition in range by tool bar(=if formula is yes then display cell.interior.colorindex=3), but I have no idea how to find out (.address) the red colour cells are displayed in range with VBA?
View 5 Replies View Relatedis there any way to set the conditional format to the colour of a particular cell if the condition is met?
View 9 Replies View Relatedi have set some cells with format condition, so when the value of it is true the entire cell should be gray "font,border,pattern". so the data will be invisible. so it dose that but when i print the font is back to black? which should be gray? i dont know why its doing that.
i tried this in other computers some of them work ok and most of them dont?? maybe its the printers? if so whats the solution?
I'm trying to Multi-Select rows in order to format all rows, in "One Step" if the cell in col. "A" = 1. I managed to write the hereunder code but I'm still frastrating because I don't like the first RNG setting command. How can I avoid declaring RNG as row(1) and still be able to run the code. I assume I need some other Range set for RNG which, unfortunately, I could'nt find and the command I used includes row1 in RNG although cell A1 = 5.
Sub Conditional_Multi_Rows_Select()
Set RNG = Rows(1) ' this is my problem
LR = [A65536].End(xlUp).Row
For R = 1 To LR
If Cells(R, 1) = 1 Then
Cells(R, 1).EntireRow.Select
Set RNG = Union(RNG, Selection)
End If
Next
RNG.Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End Sub
I had in mind to use:
Set RNG = Rows(65536)
but this will not make it a "neat" vba code, either.
i m trying to format a cell so that it colours in red when a specific text is chosen in another cell, ive tried conditional formatting but I cannot get it to work. e.g column A has a drop down list, if GB or IEN is chosen in that cell then cell in column K will become red. (as if these two are chosen then they have to input a number into column K). Once they have inputted the number into column K i want it to go back to its original format. Or if they dont select GB or IEN then it would stay in its original format aswell.
View 2 Replies View RelatedHere's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.