Result Conditional Upon Preceding Values
Mar 19, 2007
I am using the following formula (which I need to adapt) to enter a value in a cell C30.
C30=IF(A30>0,A30,0)
I will need to enter this formaula in column C rows 1 to 300.
The problem is that, in addition to A30 needing to be >0, I only need to enter a value in C30 if a value (>0) is found in column B (B29 going back to B1) before a value is found in the A column (A29 going A1). If a value in the A column is found first then I need to enter "0".
For example, if A30 is >0 and the previous A row A29 is >0, I want to enter 0 in C30.
Another example, If A rows A20 to A29 are 0 and B19 >0, then I want to enter A30 in C30.
View 9 Replies
ADVERTISEMENT
Sep 28, 2007
I'm having trouble figuring out how to evaluate the result of Conditional Formatting using VBA.
I've got a series of cells ( dates) that are formatted in BOLD when a corresponding cell elsewhere in my spreadsheet shows that the date is Actual and not Projected ("A" vs. "P").
The Conditional Formatting works fine, I just can't figure out how to get VBA to skip the cell if the value is not BOLD based on the Conditional Formatting result.
ex:
For Each i In Selection
If i.FormatConditions(1).Formula1 = True Then
'OR i.FormatConditions(1).Font.Bold = True Then
i.Copy
i.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
End If
Next i
View 6 Replies
View Related
Aug 3, 2009
I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:
Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.
i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow
However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).
View 4 Replies
View Related
Dec 6, 2012
I'm looking to conditional format a cell/cells based on whether a formula result returns a whole number or not.
I don't want to include the formula in the sheet itself, just have that as the formula in the condition.
The formula will be along the lines of:
=IF(SUM(BB10/BA10)"a whole number",TRUE,FALSE)
My problem is, is that I don't know who to refer to "a whole number" in Excel formula language.
View 4 Replies
View Related
Nov 10, 2008
I know when you multiple anything by 0 the answer is 0.
But I have a list of numbers I need to multiply out
but want to keep the total even if there is a 0 in the sequence
Eg
A1*A2*A3*A4
10*15*0*11
5*21*6*0
0*14*10*0
So A1*A2=150 if I then multiply by A3 I get 0, but I need to ignore the cells that have 0.
So for the last example my answer would be 140.
View 9 Replies
View Related
Jan 15, 2014
This has got to be simpler than I'm making it out to be, but I'm stuck. I have a column that is created by a formula that counts the number of times a name appears in a list and prepends the number to the beginning of the name. The result is something like:
A1:
1JohnSmith021488
2JohnSmith021488
1JaneDoe102187
2JaneDoe102187
3JaneDoe102187
4JaneDoe102187
5JaneDoe102187
6JaneDoe102187
7JaneDoe102187
8JaneDoe102187
9JaneDoe102187
10JaneDoe102187
11JaneDoe102187
I need a filter that results in a list of names preceded by "1" (but not 10, 11, 12, etc.) The correctly filtered result of the above list would be:
DESIRED:
1JohnSmith021488
1JaneDoe102187
My autofilter searches for data that begin with "1", so instead of the above result, I get:
UNDESIRED:
1JohnSmith021488
1JaneDoe102187
10JaneDoe102187
11JaneDoe102187
There's got to be an easy way to do this, but I can't figure how to account for the variable number of digits (1 or 2 digits) at the beginning. It would probably work to set up an advanced filter for cells that begin with a number less than 2, but I don't know how to do that.
View 3 Replies
View Related
Jan 21, 2012
I have a project network spreadsheet shown below and in column BT I need to identify each non-critical path task (value in column BS is "N") that has a critical path task (value in column BS is "Y") as one of it's immediate predecessors.
Predecessor tasks are listed with commas separating them in column G. They are also separated out into columns H through Q for another function in the spreadsheet.
Note: Task ID's can be numeric or text
*BGBSBT11Task IDPreceding Tasks * * * * * *(comma seperated)CP?
*12Start*Y*13aStartY*14baY*15dbY*16ebNX17cbNX18fcN*19g*N*20h*N*21ihN*22je,d,i,g,fY*23kjY*24Finishk**250***
View 7 Replies
View Related
Feb 28, 2013
I have been using this code I found awhile back to fill blank cells in a column with the preceding value. but it leaves the very last cell blank. Like this.
A
B
41055182
905182
41054750
905182
[Code] ........
Is there a way to add possibly a "Do until column B is null or """?
Code:
Sub FillBlanks()
Dim rRange1 As Range, rRange2 As Range
Dim iReply As Integer
If Selection.Cells.Count = 1 Then
MsgBox "You must select your list and include the blank cells", vbInformation, "OzGrid.com"
[Code] ........
View 9 Replies
View Related
Mar 5, 2012
I have data in the (various) formats as follows:
1) Text value here
1. Text value here
1.Text value here
1 Text value here
1)Text value here
Is there a way to take the cell contents from the first Alpha character? (i.e. no punctuation, no numbers). There are other numeric characters in the text values that I would like to keep.
View 5 Replies
View Related
Jun 15, 2007
I need to find the first non-empty cell in a range and paste a constant value into the preceding cells.
aa bb
aa bb
aa bb
aa bb cc
aa bb cc
aa bb xx
aa bb xx
aa bb xx
aa bb cc
aa bb cc
View 5 Replies
View Related
Jan 27, 2012
I am trying to use the status of Conditional Formatting to toggle on/off protection for a cell.
I have a cell with Conditional Formatting applied if the result of a formula is true. If the Conditional Formatting is applied, I want the cell protection turned on so a user can not change the entry in the cell. If Conditional Formatting is not applied (false), cell protection is turned off and the user may edit.
I am using Excel 2010 and Vista.
View 1 Replies
View Related
Nov 21, 2009
i am trying to figure out, how to automatically update time, based on specific city's time zone? Lets say, if the city is dallas, time zone is Central GMT-6. In the next cell, the time shows up as Dallas's local time, instead of local computer time. And this time updates itself after every 2 min.
2ndly, if that city's time is later than 8:59 PM local time, that specific city's cell grays out.
Attach: Snapshot of sheet
View 14 Replies
View Related
Aug 14, 2013
I am working with arrays that extend far beyond their actual content, and so i am looking for a way, through macros, to find the first blank cell in a column and then copy all preceding cells in that column.
View 8 Replies
View Related
Sep 5, 2013
I need a macro that will examine column A starting at A2 and working its way down copying the data in the first cell (A2) and then delete that row. If the next cells are empty (usually the next 5 cells) it should paste this copied value in each of the empty cells until it comes to the next cell with data in it. At this point it should copy this next value and delete the subsequent row and copy this value in each of the empty cells directly following (again, usually 5) until it finds the next cell that has data in it. At this point, again the whole process begins again repeating it self until it comes to the end where no more data is.
Here is an example of what I need done:
This sheet:
Sheet1
*
A
B
C
D
1
Item Number
Type
Type 2
Feature
[Code] ...
Should look like this:
Sheet1
*
A
B
C
D
1
Item Number
Type
Type 2
Feature
[Code] .....
View 9 Replies
View Related
Jun 28, 2007
I need help using the vlookup formula when there is 2 values that needs to be looked up to find the result.
for ex:
I need the result from Column "C1" which is the price from sheet2 by looking up the color, navy which is in Column B1 on sheet2 & looking up shirts which is in Column A1 on sheet2.
View 9 Replies
View Related
Apr 6, 2014
Spreadsheet 'Raw Table' has many, many rows, and a dozen columns of raw input data. Each row has date, number, and text fields.
Spreadsheet 'Dashboard' is a somewhat simple one page summary of one day's worth of data entry from Raw Table. Dashboard isn't a simple table, but more form-like in appearance (not sure if that matters). Essentially, the values from Raw Table are spread out in different locations in Dashboard, and not just in a row.
The Dashboard fields gets their values from Raw Table based multiple criteria, the most important being a manually inputted date entry at the top of the Dashboard. The other criteria are simple numbers (ie, 1, 2, 3, 4....) built into the formula. So a set of criteria for one particular cell in the dashboard could be: 4/6/2014, 1, 3. Those 3 criteria are unique identifiers: there is only one match, ever. If those criteria are met, then a value from a specific spot in that row from Raw Data is placed in the dashboard field.
Here is one formula example for one such field in Dashboard:
=INDEX(('RAW DATA'!$D$1:$D$20000),SUMPRODUCT(('RAW DATA'!$A$1:$A$20000=$D$1)*('RAW DATA'!$B$1:$B$20000=1)*('RAW DATA'!$C$1:$C$20000=3)*ROW('RAW DATA'!$D$1:$D$20000)))
The first sumproduct criteria is a match for the date in Raw Data's column A with the manually inputted date in Dashboard located at D1.
The second criteria is match for the value in column B with a value of 1. The third criteria is match for the value in column C with a value of 3. (Again, this is an example. The 1,3 set can be any specific combo from 1,1 to 8,8. Thus, a day's data can have no more than 64 records of a dozen date, number and text fields.)
The Index function is obviously array,row,column. So this example index function results in the contents of column D in the row that the sumproduct function generates.
This dashboard spreadsheet works perfectly everywhere except four fields (out of 100+ instances of the same formula). In the faulty spots, the value that the index function is returning in Dashboard is basically the contents of column D in Raw Table in the row that corresponds to the place the formula is located in Dashboard. It's almost like there's an error in the formula result, and the index is defaulting to the row number in Dashboard. But I've tested this 8 ways til Sunday. I can even straight-up copy these four faulty fields to another part of the dashboard, and the results are magically correct. And I've checked to see that the actual sumproduct function result is returning the correct row for these fields. I don't have an answer as to why it's defaulting to the formula row number in just these four locations, and not the row value that the sumproduct function is correctly producing.
I've thought maybe it was a data type problem in Raw Data, but that route produced a dead end. It has to be something with the Dashboard. I'm going to start over with a new Dashboard spreadsheet, and see what happens. One thing I did was, early in development of the dashboard, use ctrl-shift-enter in the first faulty field, then went back and changed the formula wholesale to something different, and copied that cell to (at least I think) the three other faulty spots. But if I went back and completely changed the formulas and did away with the brackets, the cells should be fresh, no?
View 6 Replies
View Related
Feb 25, 2014
I have set of column data A & B In those A contains Serial No & B contains Ticket No...
I am looking for formula to solve the function by those conditions... almost get done by countifs functions.
1.If Serial & Ticket No Only Once result should be "FIRST TIME LABOUR ONLY"
2.If Repeat Twice For The First Serial No And Ticket "Labour Only", For The Second Serial No And Ticket "Labour & Parts"
3.If Serial No Twice But Ticekt No is different for both serial no Result Should be "PART USED 1 OF 2"
4.More Than 2 Times Serial & Ticket No Repeats" Result Should Be "CCI"
View 5 Replies
View Related
Aug 7, 2009
I use this code to look for values like V1/V2/V3/V01 in my column. If the cell contains the value, nothing happens, if not, the whole row is deleted.
View 2 Replies
View Related
Sep 2, 2006
I'm trying to use Advanced Filter to generate all the unique values within a list, only at times I want to Autofilter the data, then use the advanced filter to determine all the values of the slimmed list. For some reason, this works fine on column A, only returning the values that begin with a 1 when the column is filtered for all values beginning with 1. However, when I run the advanced filter on column 2 with column 1 filtered to all beginning with 1, all values all returned regardless of whether column 1 criteria is met. Either way, after the advanced filter, the autofilter has been removed.
View 7 Replies
View Related
Feb 4, 2008
I am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
View 9 Replies
View Related
Mar 29, 2014
I have below table and want to get new order quantity if the closing stock of a particular product is less than or equal to the ROL after viewing that a previous order of the same product has not been placed within the lead time of that particular product even the closing stock is less than ROL.
Product
Date
New Order
[Code]....
- 1st it match the product with the relevant one
- Then compare closing stock parameter
- Then finally look up the previous order and compare it with relevant lead time
View 9 Replies
View Related
May 27, 2014
I have been trying to find a formula for this but I have yet to find one that works for me.
Data;
English - United Kingdom, Czech, Spanish
UK - United Kingdom
Swedish
English - US
French
US
France
German
Portuguese
+8000 more various values
I am looking for a formula that can search for multiple values e.g. english, us, uk and united and then return the value "Yes".
I was looking for something that includes wildcard searches or contains so that it is not affected by upper or lower case but combining this with multiple searches was proving difficult.
View 7 Replies
View Related
Jun 27, 2014
I have 4 text boxes in a user form where user type numbers. At the end i have another text box(yellow one) that adds these numbers using this code.
[Code]....
In another text box(grey one) user types another number. Then using this code
[Code] .....
I try to add the values of the two previous text boxes. I get wrong result when i use decimals numbers and i can not understand what is wrong.
Attached File : add text boxes.xlsm
View 7 Replies
View Related
Mar 17, 2009
I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value.
I have a column between the years that calculates the percent of gross revenue for the specific department/cost.
I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever.
The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.
View 7 Replies
View Related
Mar 3, 2014
I need to input a formula that will produce a "yes / no" result if a "0" value appears consecutively six or more times.
Using the date below (ranges A1:Q1, A2:Q2 and A3:Q3), I need cell R1 to read "yes", as there are 7 consecutive "0" values. Cells, R2 and R3, however, need to read "no" as there are less than six consecutive "0" values in rows 2 and 3 respectively.
A
B
C
D
E
F
G
H
I
J
[code].....
View 8 Replies
View Related
Sep 27, 2006
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
View 9 Replies
View Related
Jun 13, 2008
I have wind speeds and elevation data for 200 points. Each variable needs to go into a calculations spreadsheet on another worksheet. Once each variable gets put in the calculations automatically spit out the result. Now I need to use macro to automate this process so that it will repeat the steps I took for every line of data.
In English code terms, I want the Macro to say, take wind speed number and input in wind speed cell in calculations spreadsheet. Then take elevation number and input in calculations spreadsheet. Then take result number from calculations spreadsheet and input in cell. Then loop to go down the 200 lines of data. Is this possoble? Sorry for the non-technical wording.
View 3 Replies
View Related
Jun 24, 2008
I'm trying to find a formula that will give me a count of all programs by month that have an evaluation result. So, in column A1:A24 I have a list of programs with. In cell A25, I have the total number of programs. In column B1:B24, only 7 of those programs have evaluation results, some of the rows are blank.
I know that I can get a sum of the programs by month, but how do I only get a count of those with an evaluation result? What formula can be used to get a year to date (a running count), not a count based on 12 months? Meaning that we would need a year to date for each month, so currently for October to June (based on fiscal). If we based it on year to date, then I think we would have to divide be 12 and that would make the results inaccurate.
View 2 Replies
View Related
Apr 14, 2013
I have an excel file with some data in it. I have two columns with one of four words in it. For simplicity I'll call them RED, GREEN, BLUE, and Yellow. A few columns over I have columns "Same" "Good" and "Bad". I'm trying to figure out a way to get excel to put a 1 in the appropriate column, if cell A1 is Red I need excel to look at the word in cell B2 and put a 1 in the "same" column if A1 and B1 are the same word. If the word in B2 is Yellow I need excel to put a 1 in the "Good" column. If the word in B1 is GREEN or BLUE I need it to put a 1 in the bad column. The tricky part is each of the four words RED, GREEN, BLUE, and Yellow have a different set of words that will be called "good" or "bad". So if the word in cell A1 if Green for example it will have a different set of good and bad color words.
So it seems I need four different functions depending on what word is in A1. In the column next to these lists of words I have the date which I'll need to use to give me the sums of same, good, and bad for each day. But first things first. I think I need to use a combination of IF functions and MATCH function, but I can't figure out anything that works. Below is what I'm hoping it will look like when I've got the right formulas.
First column
Second column
Date
Same
Good
Bad
[Code]....
View 9 Replies
View Related
May 24, 2008
I'm quite a novice at Excel. I have a column of values that I sum as follows;
A
0
0
0
0
0
0 <----------------sum of A1:A5
A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
View 7 Replies
View Related
