Test Cell Values Before Adding Weekdays
Jan 8, 2008
I have the followinf formula
=IF(A2="","",IF(C2>TODAY(),"",IF(E2="",IF(B2="1st",WORKDAY(A2,4,$H$1),WORKDAY(A2,5,$H$1)),C2)))
What I am trying to do is cause (where the formula is) to:
If A2 is empty, put nothing,
If C2 is greater than today put nothing, otherwise put C2
If E2 is empty then if b2 is "1st" add 4 workdays to A2, otherwise add 5 workdays.
I seem to have a problem with getting the formula to put the value of C2 if it is not greater than today In otherwords if the date in C2 is 6th Jan, i want 6th Jan as the result of the formula.
View 9 Replies
ADVERTISEMENT
Aug 21, 2013
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
View 3 Replies
View Related
Jan 26, 2008
I want to reference 2 cells to open a msgbox. Currently I use this formula in a cell "=IF(E4="C",IF(A4>30,"OT","")) I am trying to write a macro that will open a msgbox instead. This is what I've tried so far
Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1, 1).Address = "$E$4" Then
If Target(1, 2).Address = "$A$4" Then
If Target(1, 1) = "C" Then
If Target(1, 2) > "30" Then MsgBox("1")
End If
If Target(1, 1).Address = "$E$4" Then
If Target(1, 2).Address = "$A$4" Then
Target(1, 1) = "F" Then
Target(1,2) > "38" Then MsgBox("2")
End If
View 3 Replies
View Related
Apr 28, 2006
I have a macro that takes a value of one cell ("Cells(iFoundPass, 5")) and makes another cell (Admin_Level) equal it (the valules can be wither 'Administartor' or 'Standard'). When someone runs a macro it checks to see if "Admin_Level" equals "Administrator". The problem is that when that first macro runs, even if ("Cells(iFoundPass, 5")= "Administartor" and then "Admin_Level" will be made "Administrator" , my IF/THEN statements do not work. I think this is because the value of "Admin_Level"("Administrator" in the example above) is not text. I can't seem to figure out why. I do know, though, if I manually enter in 'Administrator' in the cell name "Admin_Level" then my IF/THEN works.
Sheets("Config").Range("Admin_Level") = Sheets("Config").Cells(iFoundPass, 5)
and then this is the logical check that runs anytime someone wants to run a macro...
If wb.Worksheets("Config").Range("Admin_Level") <> "Administrator" Then
PasswordForm.Show
End If
View 2 Replies
View Related
Nov 20, 2012
TWO more days to turkey day. 3 more to BF --- yaaaaak! Anyway,
Iam trying to display only 3 workdays in each of three cells. Days displayed depend on today's date.
I am usinf the following but it displays SAT and SUN.
Q3 = today's date
=UPPER(TEXT(Q3+2,"DDDD")) displays THURSDAY
=UPPER(TEXT(Q3+3,"DDDD")) displays FRIDAY
=UPPER(TEXT(Q3+4,"DDDD")) displays SATURDAY -- would like it to display MONDAY
Q3 changes to reflect today's date
therefore this would display:
=UPPER(TEXT(Q3+2,"DDDD")) displays THURSDAY
=UPPER(TEXT(Q3+3,"DDDD")) displays FRIDAY
=UPPER(TEXT(Q3+4,"DDDD")) displays MONDAY
In other words I want to display only the week days in these 3 cells.
I tried:
=TEXT(WEEKDAY(INDIRECT("Q3")),"dddd")
but "Q3 + 2" will not work
the end result desired:
MONDAY
TUESDAY
WEDNESDAY
TUESDAY
WEDNESDAY
THURSDAY
[Code] ......
View 3 Replies
View Related
Oct 27, 2009
Here's an example of what i'm working with,...
Range is C40:D48
View 4 Replies
View Related
May 13, 2009
I have a column where the data is listing the day of the week and time worked. What I need is to sort the column by the first four letters in cell I used the left(e5,4) to return the letters in a helper column but when I sort it still not in the Monday -Friday order. example:
View 2 Replies
View Related
Jan 25, 2007
I am creating a spreadsheet to monitor horse race betting but my formulas aren't coming out right.
The problem is to do with adding winnings to a running balance or minusing each bet's stake money from the running balance if the bet loses.
I have two formulas that are giving me a problem, both are in the P column in the attached spreadsheet. The first one, located in row 2, being slightly different as it is the first data row and so contains the starting balance in the formula.
=IF(OR(J2="W",J2="P",300+S2),IF(J2="L",300-O2,"")).
This produces a blank or error result.
=IF(OR(J3="W",J3="P",P2+S3),IF(J3="L",P2-O3,""))
This produces an error whatever is in the J column
View 9 Replies
View Related
Apr 15, 2014
I want to randomize a range of values stored in sheet 1 and insert them in another cell.
I am having values for sales consisting of product names and its price. there are 10 product names and its corresponding prices and is stored in cells E1:F10. I want to generate test data containing product names and its corresponding price. The generated data needs to be saved in the cells A1:B50
the product names needs to be randomized. This needs to be done in vba. Below is the screenshot of the final result that is needed. As you can see, I have randomized my 10 products and its prices into the test data column. This was done using vlookup formula and I need the same to be done in vba.
View 2 Replies
View Related
Nov 7, 2011
I have a workbook with several tabs. the first tab is a data input sheet: subtotals from this sheet get posted to cells on the other sheets. at the top of the Data sheet is a drop down validation list that the user will select the next month from. On each of the other sheets, is a grid with calendar months down the left hand side and years across the top:
Excel 2007BCDEFGHI26Incoming111009080727Month201220112010200920082007200628
JAN1201135317522050194029FEB1392150817082130217230MAR1538164117831823246831
APR1338132717301819211632MAY1485129615762008244933JUN14991380162618652028187034
JUL15491382166020362091170335AUG16531383148619252132197236SEP15901411155620051982214937
OCT23391324159420732014247738NOV1440146718842186282139DEC15631602203721472197Incoming
I am trying to figure out a simple method to check whether there is data on any of the four other sheets in the spot corresponding to the month selected by the user on the Data tab. the format of the validation list dates are dd/mm/yyyy in cell B2 on the data tab.
View 3 Replies
View Related
Oct 19, 2007
The user will select a range (example, B4-Z4). The macro needs to test each cell to see if the number is in a certain range (example, is the number in the cell between 21-40, 41-60, 61-80, etc? - these ranges will not always be the same on each worksheet). If the cell is in that certain range, that cell is a 1 for that range. Example,
B4 = 23
21-40 = 1
C4 = 30
21-40 = 2
D4 = 45
41-60 = 1
After all ranges are tested, it will be graphed on a separate worksheet with the x-axis being the ranges (21-40, 41-60) and the y-axis will be the total number of cells that fit in the range. The above would be....................
View 3 Replies
View Related
Feb 3, 2014
Below is a list of sku's where column C contains values that represent quantities of items received. Now, column D contains 4 different categories of items, BK, BL, CBL and PP, so what I need to do, is add the numbers shown in column C, for each category separately. That is, all values from column C having a "PP" value on column D, should be added separately, and so on with the other categories from column D. The resulting values will be used to create pie charts, but that part I have resolved.
What I'm doing to achieve this, is selecting column D, then, conditional formatting→highlight cell rules→equals to, then typing each category so I can highlight the corresponding cells. Then, adding Filter to column C, then sorting by color, then, selecting the corresponding cells from column C to add their values. The process works, but takes too long, especially when considering my lists usually have a few hundreds of sku's each. The below list is just a shortened version of one of them.
View 2 Replies
View Related
Nov 15, 2013
I need a formula that will return a range of values from a list.
Example: I need a formula in cell A2 that looks at the list and will return all numbers that are >99 but 199 but299 but
View 6 Replies
View Related
Jun 19, 2014
I was wondering if it is possible to get the SUM function to:
1) Add numbers in cells that contain letters or no letters.
2) If there are brackets that cell would be subtracted from the total, again ignoring any letters.
For example:
A1 = 3 ABC
A2 = (3 ABC)
A3 = 4
3 ABC + (3 ABC) + 4 = 7
View 5 Replies
View Related
Mar 30, 2009
I have a spreadsheet with two columns and 39900 rows, from that data I want to create two new columns so cell C1 = A143 and D1 = B143, C2=A286, D1=B286, and so on. The row location increases by 143.
The simplest thing I tried to do was to say C1=A1+142, but (knowing it is wrong) I get a #value error.
How can I tell excel to grab these values located every 144 rows below and create two new columns?
View 9 Replies
View Related
Feb 1, 2007
i have an excel file which contains data, I want to be able to search this file and to fill the cell and its next 3 cells to the right with a certain colour. Is this possible as I have wrote some vba to do this but, Im only able to search and not fill the cells with the correct colour.
View 13 Replies
View Related
Oct 1, 2009
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
View 5 Replies
View Related
Nov 9, 2009
How do I test if a cell has #N/A?
View 4 Replies
View Related
Oct 10, 2012
I need a UDF to test a single cell if it is colored. If it is the result returned should be a 1, if not the result will be blank. This is what I have so far, but I don't know much about VBA.
Function Filled(MyCell As Range)
If MyCell.Interior.ColorIndex > 0 Then
Result = 1
Else: MyCell = ""
End If
End Function
View 3 Replies
View Related
Dec 28, 2008
I need to take a specific action when a cell has an actual formula in it versus when it just has a "value". Is there a procedure or command which will allow me to identify if a certain cell has a "formula" (like =sum(a1: a5) ) or just a value.
View 3 Replies
View Related
Aug 19, 2009
How would I write an IF Then Statement to test if a cell is blank? (meaning it could have "Div/0!", text, 0, or "n/a".)
View 4 Replies
View Related
Feb 17, 2012
I am trying to build a macro that test for the value of each cell of a range (in a column), and if found, then the value of the cell of the same row (another column) will be set to 1. If not, then the macro writes a formula to get some data from BBG (this part is ok). this is what I have done so far but I have an error message, telling me "Not Else with out if" .
Code:
Sub Fx()
With Worksheets("DivRelease")
Dim LastLig As Long
[Code].....
View 9 Replies
View Related
Sep 10, 2008
Is there formula to use if I want an IF statement to add a date "TODAY()" to a cell if a corresponding cell is highlighted? I would search for this in the archives if I had a clue what to search for. "Conditional formatting", maybe?
In Col J "Appt. Date", I want to add today's date if the cell in Col C of that row is green.
How would that work? My worksheet is posted below: ......
View 10 Replies
View Related
May 4, 2006
I find myself needing this often and hope there is an elegant formula that can make this easier.
Is there a formula that will test if a particular cell is referenced somewhere in another array or vector? Specifically, I find that I have to aggregate long lists into categories to fit budget formats of various lenders and investors. For example, my detail budget has separate rows for Water, Sewer, Garbage, Electrical, and Gas. These expenses have to be aggregated on one funder’s budgets as “Utilities.” Sometimes after going through this I find that my totals don’t add up, i.e. I left an item out of the aggregated budget. I would like to be able to add a column on the detail budget to test if each budget item has been referenced in the aggregated budget.
View 3 Replies
View Related
Dec 16, 2006
I want to add an IF statement to my macro that will run only if a cell on the worksheet is not blank (empty). I know how to use the if statements but can't figure out how to test a cell for contents.
View 4 Replies
View Related
Jul 10, 2007
I'm testing to see if a cell has nothing in it, and if that is true then it will execute the code. My problem is that even though the cell is blank the field does have a drop down list (list validation set for that field) below is the code i used, but doesn't work.
If Range("b3").Value = " " Then
View 2 Replies
View Related
Aug 22, 2014
Basically I have a column (lets call it column A) whereby I manually fill the cells green once I have received some documents, another column which has a numeric value in it (column B) and I want to create a third column which basically just copies column B but ONLY if column A is filled with a colour (actual colour doesn't matter cause I only use green)
I tried using the IF function but I don't know how to use cell colour as the logical test
View 3 Replies
View Related
Feb 28, 2008
I'm trying to write a macro to test whether a cell has any neighbouring cells that match it's value.
I'd like this to be cumulative so that the more matches, the higher the value.
I'd then like the result to be written in another cell to set up a separate grid.
So far I'm getting error messages and I don't know why:
View 9 Replies
View Related
Jun 15, 2009
In column AX2:AX2000 there is a value(alpha numeric) MB60176685 and in column AX2:AX2000 there are various values.
I would like to test if any cell in AZ 2:2000 is greater than 200. If yes then blank AZ in that row. If less than 200 go to the next row. The value of AX will remain the same in that row.
SEE EXAMPLE of results BELOW
AX AZ
201
MB400174444 10
MB400174444 155
View 2 Replies
View Related
Aug 28, 2012
I have a code that I have put in the ThisWorkbook section, and I thought it should work - but nothing happens....
Code:
Sub FirstSub()
Application.OnTime TimeValue(Sheets("SetUp").Range("G1").Text), "SecondSub"
ResetTime = Now() + 1
Application.OnTime ResetTime, "FirstSub"
End Sub
Sub SecondSub()
If Weekday(Now()) > 2 And Weekday(Now()) < 7 Then Call CreatePDF
End Sub
It's supposed to call a sub called CreatePDF.
View 9 Replies
View Related
