I have some fairly simple conditional statements to format date fields based on current date and upcoming dates. They work perfect on the mac, but when my co-workers open the excel sheet on their PC's it all whacks out?
how to create or save these so they open properly on both platforms?
Trying to use several ifs together. Not sure, where my end if's need to be. I keep get Next w/o For error.
View 4 Replies View RelatedI have an set of conditional statements to check which i'm not sure whether to use IF or CASE statement. Find below the
View 2 Replies View RelatedI need some help with applying some conditional formatting to cells that are populated with logic. I attached a portion of the sheet I am working on.
The cells in rows 41 and 42 will always be there so I am ok with them. The data in cells C43:I43 is loaded if a certain feature is selected on a separate sheet. If it is NOT selected, I need the values to not show up, (this part is already done with the formula in the cells) and i also need the conditional formatting to make all the cells white or blank. The same is true for the data in cells C46:I46
I have a spreadsheet that has several columns containing names and data. In the first column, is a person's name, in the second, is a client number for that person, and in the third is the "last contact" date. I have a fourth "reference" column that has a conditional statement I was trying to use for formatting. For example, in A1 would be "John Doe," in B1 would be 987, and in C1 would be 06/10/07. The next row would have data for a different person. In column E I have my reference column.
If the last contact date is less than 4 days from the current date (which is in cell K2), the reference column (using an if formula) will produce the number 1. If it is 5 or 6 days from the current date, it produces the number 2, and if it is 7 or more, then it produces the number 3. Here is the exact formula:
=IF($C2+7<=$K$2, "1", IF($C2+5<=$K$2, "2", IF($C2+4>=$K$2, "3")))
What I want to happen is this: if the reference column produces a 1, then I want the corresponding row to turn red. This would only include the name, assigned number, and last contact date. I do not need the entire row to change a certain color. If a 2 is produced, I would like the row to turn yellow, and if a 3 is produced, I would like the row to turn green. This is going to run for many rows (up to 150). How do I do this using VBA? Keep in mind the numbers in the reference column will change if the "last contact date" is updated to a more current date.
I have tried a variety of if & and type statements but I cannot get this 2 conditional format to work. Review attached spreadsheet.
View 3 Replies View RelatedI am having difficulties trying to get some conditional formatting done.
For the below i need to highlight the 2012 YTD Column based on the following conditions, and i cant get the formula correct
GReen = If 2012 YTD meets /beating both 2012 target and 2011 Actual figures =And(D2
I am using VBA to put a formula into a cell. The formula is
VB: = IFERROR(VLOOKUP(LEFT(A5,11), 'New Quarterly Report'!A$11:E$37,3,FALSE), " - ")
But when I try to put this into a cell using the value option, I get a run time error 13: Type Mismatch. No problems while im compiling it but it comes up only during execution
VB: Sheets("Summation Table Template").range("B4").Value = "= IFERROR(VLOOKUP(LEFT(A5,11),'New Quarterly Report'!A$11:E$37,3,FALSE), " - ")"
When I try to put only the VLOOKUP, it works fine.
VB: Sheets("Summation Table Template").range("B4").Value = "= VLOOKUP(LEFT(A5,11),'New Quarterly Report'!A$11:E$37,3,FALSE)"
But I want the cell to not show #N/A when a value through vlookup is not found. Other formulas Iv tried are to use ISERROR with IF and also to store the formaula in a string and then put it into Range.Value but it still gives me a run time error 13.
Can you have IF and AND statements in Conditional formatting formulas?
I put this and I get an error: =IF(AND($G5=""($H5=<>"")) I was testing the water for adding and OR statment also. I really want if cell G5 is blank and cell H5 or I5 has text, then G5 should be red.
use of SUMPRODUCT and what I believe is called a double unary operator (--). Here's the original post with Paul's solution:
http://www.excelforum.com/excel-gene...-new-post.html
I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.
The data:
There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date.
The formula needed:
I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on.
My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data.
The furthest I got was this, but it didn't work.
=AVERAGE(IF('A3:A33>=VALUE(D3)
I wish to enter a number in cell D5 and have the entry time recorded in A5.
The code below does that, however, each time I add subsequent data, the original times update to the most recent entry.
I want each entry to remain static or fixed.
Col A Col D
8:56PM 6520
=IF(D5>0,NOW(),"")
=IF(D6>0,NOW(),"")
=IF(D7>0,NOW(),"")
=IF(D8>0,NOW(),"")
I have a problem now with my new spreadsheet. I need to use vba to automate a process.
Date
Item
Holding Days
01/03/2014
A
1
[Code] ........
Scenario: On 01/03/2014, I bought A and hold it therefore holding days is 1. Then I sold it on 02/03.2014 so it became 0. Until 04/03/2014, I bought A back, therefore holding 1 day. 05th I didn't sell so I am still holding it, meaning holding days =2. Then on 06th, I sold A and buy B, resetting the holding days to 1 for a new item.
From the table above, how can I use vba to automate the last column?
I am trying to use a public variable but it is not holding the value when I call another sub.
Here is basically what I have:
Public AcctName as Long
Sub Statments()
There is much more code in here but it is not relevent
AcctName = an account name lets say "frank"
If X XX then
Call Title
End If
End Sub
It then goes to the title sub and executes the code however it is supposed to write out the value held in acctname but it comes up blank instead of as Frank which is the value it held when it left the statments sub. I am new to this public thing and need any help I can get.
Just a note: the spelling is correct and the variable does hold the correct value before title sub is called.
If I'm starting in Sub #1 and I have turned screenupdating off, then later in the procedure I call a function that also has screenupdating off (and also back on at the end of the function), will I get a slight flicker here? I'm noticing a slight one, and I can only imagine that it's where the screenupdating is being triggered again. (removing the updating from the function isn't an option, because another procedure calls the same function & needs it turned off)
View 2 Replies View RelatedI am given a number say 155 and want to find out which range the number falls in. For example there are ranges like 142-151,152-161, and 162-171. I need a function that tells me my number 155 belongs to the labeled range 152-161.
View 9 Replies View RelatedI have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies View RelatedSheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
I have attached a 2004 excel document that has a conditional statement that says, if an amount is over $400.00 you get a 10% discount, else, the cell says no discount. That is working fine. I also have a absolute cell ready to do the math for the 10% amount. There is also a column that shows the discount amount and column that shows the amount after the discount has been applied. Since I have been just doing one calculation and auto-filling, my problem is, how do I get the record that doesn't get a discount (no discount) not to apply the 10%?
View 4 Replies View RelatedLet's say I have a cell which has a value between 0% and 100% and I want to set three criteria to change colors.
The problem is that I only have three conditions that are able to be set and recognize that I need the first to remain as is, so that it stays white if no data in there. Can it be done without a macro?
For instance:
Condition 1 - Cell Value Is - ("") - the result is shaded white (this works)
Condition 2 -Formula is - =F8>=(0.95),(and(F8<(0.99) - I want this green, but it isn't taking it? Can you put multiple statements in a conditional format if the answer is either true or false?
Condition 3 - Cell Value Is - <95% - Turn Red (This Works)
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
I try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
My formula is not working correctly; not sure what to do. I use this same formula with numbers and it seem to work, but only have 8 IF statements as well. This example below doesn't even work with only 8 IF statements.
--------------------------------------------------
What I am saying below is = If E3 = a swat team (i.e. EO-Deal Processing-Prescreen) then please enter "Chumbley" and so on. For anything else put an na or n/a for blanks or swat teams that I haven't lised in the criteria.
---------------------------------------------------
=IF(E3="EO-Deal Processing-Prescreen",Chumbley,IF(E3="EO-Deal Processing-DocGen",Chumbley,IF(E3="EO-Deal Processing-Events",Chumbley,IF(E3="EO-Deal Processing-Triage",Junk,IF(E3="EO-Deal Processing-Processing",Queck,IF(E3="EO-Deal Processing-Calcs",Doyle,IF(E3="EO-Deal Processing-Closing",Terry,IF(E3="EO-Deal Processing-D11B",Blazier,IF(E3="EO-Deal Processing-Isolated UI",James,na)))))))))
a = Range("d1").CurrentRegion.Resize(,2).Value
and
Range("G1").Resize(n,2).Value = b
what do the above two lines of code do?
I have the following:
=IF(I8>=G8,IF(I8
if-and-else statement in vb.
I made a combo box where the user selects a part in a combo box and I want the part selected to be stored in a certain cell. The first selection would be stored in C15 and if thats not empty then in D15 and if thats not empty then in E15 and if thats not empty then in F15.
Dim m As Integer
m = cboPart.ListIndex
If m = 0 Then
If IsEmpty(ActiveCell.Range("C15")) = True Then
ActiveCell.Range("C15") = cboPart.Value & vbLf & "Name: " + txtName
Else
ActiveCell.Range("D15") = cboPart.Value & vbLf & "Name: " + txtName
End If
Else
If IsEmpty(ActiveCell.Range("D15")) = False Then
ActiveCell.Range("E15") = cboPart.Value & vbLf & "Name: " + txtName
Else
ActiveCell.Range("F15") = cboPart.Value & vbLf & "Name: " + txtName
End If
End If
When I run this if-and-else statement it stops storing the selected data after the following:
If IsEmpty(ActiveCell.Range("C15")) = True Then
ActiveCell.Range("C15") = cboPart.Value & vbLf & "Name: " + txtName
Else
ActiveCell.Range("D15") = cboPart.Value & vbLf & "Name: " + txtName
End If
I got a 2 columns (A:B) with values in workbook1 (survey2.xlsm)
I want to open a sheet named according to values from column A wb1 in workbook2 (du_database2)
And I want to fill in the offset values (column B) in the aproppriate sheet
I wrote this sub for it. I am having troubles with the for each statements (how do I set them up properly?). The script itself hasn't been checked might also been wrong there.
VB:
Sub copytest()
'by J
Dim ws As Worksheet
Workbooks.Open "survey2.xlsm"
Application.Workbooks("du_database2.xlsm").Activate
[Code] .....
I'm using vlookups within IF statements to look in a number of various speadsheets depending on what the lookup value is.
So for example,
Formula:
This works well enough however I need some look up values to look in multiple spreadsheets as the data is split out between several spreadsheets. So for example, if B1=Hello I want to vlookup to 2 different spreadsheets.
I know I could do two instances of a vlookup within the formula, both for "hello" but looking to different spreadsheets. But doesn't this mean that if the first vlookup finds the value, the second one will overright it with an "#N/A" if it doesn't find it?
I have 2 conditions that I need to apply.
View 3 Replies View RelatedI need to write IF statement with 4 criteria, or at least I think IF will do the trick. It would be difficult and not very visual to describe my question here, therefore I have attached a dummy workbook with the comments. Basically I have got 2 values in 2 columns. Next 2 columns will hold letter "x". There are 4 possible variations how "x" will appear in those two columns: first, second, both or none. So depending where the "x" is, I need to return one of the 2 values.
View 6 Replies View Related