Several Formulas In One Cell + Logic Fail
May 3, 2009
According to what I've read so far in this forum, this will be cakewalk for you guys to find out of (or at least so I hope.. :-) I don't know much about Excel, and what I do know is just logic sense, and a bit of common math.
My problem is in a sheet I use for my personal economy. (I'm a neatfreak, and a nerd...) The logic of my sheet is that I seperate the different type of monthly expenses, and sum them individually. The way I do this is that the A Column identyfies an expense (where 1 = rent, 2 = food, 3 = gas etc) while the C column is the expense itself, Example:
A B C
On account 500
1 rent -200
2 food -20
3 gas -10
2 food -10
Sum expenses -240
On account 260
I then use this formula to discern them:
{=ABS(SUM(IF(A$4:A$30=X;C$4:C$30;0)))}............
View 3 Replies
ADVERTISEMENT
Jun 23, 2009
If the addition of two cells (a1 and b1) do not add up to more than 10 then I would like cell c1 to display the word FAIL
I've tried the following
If (a1 + b1 ) < 11 then c1 = "FAIL"
But it doesn't like it
View 4 Replies
View Related
May 1, 2006
how to exclude a blanck cell from logic?
I have tried what you see below but neither works as desired.
If Not vData(n, 9) = 0 Then
If Not vData(n, 9)Is null Then
have corrected code error
View 9 Replies
View Related
Jul 2, 2007
=IF(WEEKDAY(D3,2)=7,"WEEKEND",IF(WEEKDAY(D3,2)=6,"WEEKDEND",IF(TODAY()=D3,"TODAY",IF(TODAY()-3=D3,"FRIDAY","ERROR"))))
how can i use the data cell validation using the above function logic?
View 2 Replies
View Related
Apr 15, 2008
I have a spread sheet with 4 columns and 90,000 rows utilizing 4 "IF/THEN" logic formulas. I am getting a "not enough resources" message in Excel even though I have a top end laptop with all the ram and the biggest HD available.
View 3 Replies
View Related
Jun 12, 2009
This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):
1) I want user to input starting cell. So I need to create a button for that.
2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.
If the value is greater than original cell then logic needs 2 outcomes:
a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.
View 2 Replies
View Related
Dec 3, 2013
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
View 4 Replies
View Related
Jan 29, 2014
If I run this in the editor sometimes it works and sometimes it does not.
If I run this in editor with F8, stepping through the code, it does work.
I can only guess that enough time is passing during the F8 presses that the page can load. But is that not the purpose of adding the wait code?
[Code] ....
View 9 Replies
View Related
Dec 15, 2008
I looked around the forum for a answer but none are quite the same. AA2 contains a date. AN2 contains a Pass or Fail based on =IF(AH4<60,"FAIL",IF(AJ4<60,"FAIL",IF(AL4<60,"FAIL","PASS"))).
Now even if those above fields are empty and no date is in AA2 "PASS" still shows up in AN2. I used =$AA$5="" to make AN2 turn white if AA2 had no date in it. I am unable to copy the formatting along the rest of the AN column without it all refering to just AA2. Is there a way to make it copy and correct the formating like it does with formulas? I had planned to due the same thing with the AO column that contains "DUE" if the person has not taken a test in 180 days. =IF(AA2<=(TODAY()-180),"DUE",IF(AA2<=(TODAY()-150),"CLOSE",IF(AN2="FAIL","RETEST","")))
View 5 Replies
View Related
Dec 28, 2006
My nested loop works as intended without If statements, ie it counts non blank cells in a series of 17 ranges (this loop is called the CountRangesOnEachRow loop) AND then drops down a row and repeats (this loop is called the RowNo loop). The problem is that once my first If statement occurs the RowNo loop fails and it stops at the first row.
The first If statement is designed to do the following:
1. Tests to see if A4 has data. If yes, copy cell contents (pupil's name) to Sheet(2).Range("StudentName")
2. If empty skip the countranges loop and move down a row to A5.
3. Cells A4:A35 need to be tested for data in this manner.
I've included a sample workbook & code with the IF statement comment blocked so you can see what it should do AFTER it tests the A column for data. The macro should end after row 6 as A6:A35 are all blank.
View 9 Replies
View Related
Jan 23, 2008
Consider two columns (these columns are part of UNIT TESTing workbook).. result column shows the result of test case executed on respective date
DAte Result
17/1/2008 PASS
18/1/2008 FAIL
18/1/2008 PASS
19/1/2008 FAIL
22/1/2008 PASS
22/1/2008 PASS
How do i calculate using VBA statements :
1. the number of PASSED TEST CASES that are excuted today.
2. Number of all PASSED TEst cases till yesterday.
View 3 Replies
View Related
Feb 12, 2009
I have a range of data that contains can contain either a pass or fail. what I require at the bottom of this range is a formula that says 'if any one of the range = fail then "test fail", else "test pass"'
View 2 Replies
View Related
May 7, 2014
Per the attached, I have a set of questions which must be answered by true or false. the answer to all is true. I need a pass/fail indicator on the userform to give a user realtime view based on percentage of true answers. threshold can be assumed as 80% where below it is reflected as fail
Capture_Excel Forum Query.JPG
View 6 Replies
View Related
May 12, 2014
I have a test log with PASS/FAIL. These results are logged in Excel under the column heading PASS/FAIL.
How do I count the number of PASS or FAIL using VBA macro?
So far I have tried:
[Code].....
This one did not work either:
[Code] .......
the error is at the name = Range("PASS/FAIL").Select line.
My thought process: once I had selected a range, I can now freely "look" at each string in a cell in that range. Not correct I guess.
All this is currently done in the active sheet.
View 3 Replies
View Related
Oct 15, 2009
I have one workbook that im running a userform. From this form i open another form that is contained in another previous opened workbook. On this second form i can update cell values in the second workbook. During this updating macro i am saving the workbook. using thisworkbook.save. Though For some reason when it hits this line. It must be doing a Save As and putting the workbook in mydocuments. There is nowhere in the code that references mydocuments.
Though if i open the workbook on its own (withough calling from another workbook) and load the userform the code runs fine and saves the workbook in its correct place.
View 3 Replies
View Related
Feb 9, 2007
When I'm manually formatting a spreadsheet I'll almost always freeze the header row for ease of navigation and to keep the headers in view. However when I attempt to do this in my code it seems no matter how careful I am it inevitably freezes the page in some peculiar location which bears no relation to the instructions I give it. Even knowing this annoying fact I'll take the extra step of turning off freeze first to clear any existing situation before setting my prefered freeze. ... and it still blows up in my face
Sheets("RTN").Select
ActiveWindow.FreezePanes = False
Range("F4").Select
ActiveWindow.FreezePanes = True
Is there any fool proof way to freeze rows?
View 9 Replies
View Related
Jan 27, 2010
I have a source data tab laid out like this:
QC Date.......Policy#......Associate.......Pass/Fail......1stError.....2ndError.....3rdError
QC'ers can enter up to 3 different error types committed on a single failed policy, thus the reason for 3 error fields, even though all 3 fields offer the same value list selections (ie ErrorTypeA, ErrorTypeB, ErrorTypeC......). A policy, whether it has one error or three errors assigned, should only count as a single fail against the associate.
My problem comes into play when attempting to pivot the data. I would ideally like to see the pivot table in this format:
(filter)Associate
(filter)QC Date
...
ErrorTypeA.......Count
ErrorTypeB.......Count
ErrorTypeC.......Count
ErrorTypeD.......Count
Where the "count" is the total number of occurances of that error type across all three error fields - 1stError, 2ndError, 3rdError.
But since 1st/2nd/3rd error fields are different columns, I am unable to do a single count.
My pivot ends up looking something like this:
1stError.....ErrorTypeA.......Count
1stError.....ErrorTypeB.......Count
1stError.....ErrorTypeC.......Count
1stError.....ErrorTypeD.......Count
Then repeated for the 2ndError and 3rdError fields.
View 4 Replies
View Related
Aug 20, 2012
It's a worksheet for testing milk tanks. The issue is as follows:
Column F = Chart Error/This can be either a positive or negative whole number
Column G = Tolerance/This is always a whole positive number
Problem is I want Column H = Status to say "Pass" or "Fail" based on meeting the Column G number. But it's only the number itself (not whether it's Positive or Negative) that matters in determining the P/F status.
Examples:
Column F= -20
Column G= 15
Column H= Fails
or
F= 20
G= 15
H= Fails
The formulas I've been coming up with will show one or the other as Failing but not both + or - numbers if they fail to meet the G Column tolerance number.
View 6 Replies
View Related
Oct 16, 2007
I am going to use excel for test cases. I can creat a drop down list with the selections "pass", "fail", "not run". That isn't a problem using the data validation toolbar. However I would like to basically color the cell (test case) with a certain color based on the drop down selection.
for instance:
Pass = Green
Fail = Red
Not run = Orange.
View 9 Replies
View Related
Apr 3, 2009
I am using the below code to locate the last column in various worksheets before pasting information. The problem is the column selected is either: Correct, Is a blank column with many blank columns in between it and the last visible text. The code also highlights all the cells containing text in some sheets. The results are the same for each sheet the code is run in i.e it is not varying.
View 4 Replies
View Related
Jan 25, 2007
There are three situations that should yield the answer "overdue" and a fourth and fifth situation that should yield an answer "on target". I'm new to functions and have spent SEVERAL hours on this
{=IF((G4<>0,F4<$g$1="Overdue")+(F4<G4="Overdue")+(G4<>0,F4>$g$1="On Target")+(F4>=G4="On Target"))}
View 10 Replies
View Related
Nov 6, 2011
Although the "Trip Sequence" is same , but I need to separate the Trips if the difference between the "1ST TIME " is more than 3 hours keep the first "Trip Sequence" unchanged , then by either adding a prefix or add 2000000 to the second "Trip Sequence" and 3000000 to the Third, so forth so on until all the rules are applied ( assuming more than 3 Trip Sequences could be also allocated in the database ).
Data Base
TRIP#
1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA3270004
XXX05111835$VV1984GVACDG3270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005
In the above examples , The "Trip Sequence" is same 3270004 but the "1ST TIME" is more than 3 hours difference:-
I want to be able ( ONLY IF "1ST TIME" is in access of 3 hours while the Trip Sequence are same , I want the results as follows:-
TRIP#
1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA5270004
XXX05111835$VV1984GVACDG6270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005
View 9 Replies
View Related
Apr 24, 2007
I have 3 ranges in an excel sheet
col 1 range contains a numeric value ("1" or "5")
col 2 range contains a string value ("left" or "right")
col 3 range contains a string value ("a" or "b" or "c")
example: I am trying to get a total count if col 1 contains either a "1" AND
col 2 value is "left" And col 3 value is "a". Note: If col 1 is blank (" ") then the rest of the row is irrelevant. See attachment
View 4 Replies
View Related
Nov 25, 2009
I know its not monday but I'm having a monday like problem. When I try to run this code it gives me "Pastespecial method of range class fail".
View 3 Replies
View Related
Jan 23, 2012
Workbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?
View 4 Replies
View Related
Dec 8, 2008
I am trying to calculate the number of forecasted hours worked per week for each contractor based on their allocation to the project and also based on a 37.5 hour standard week.
I am using a formula which was previously provided to me for another problem but theoretically should share the same logic. The formula works only when a contractor is forecasted to work for a full week. If there is a contract which finishes mid week the formula returns a weird value. Also another problem is that if a contract is starting on the 12th of January, it still forecasts a full week for the week commencing the 7th of January. I have attached a spreadsheet for everyone's reference.
View 4 Replies
View Related
Jan 15, 2009
I want cell B to be blank if 1 of 2 different conditions are met. If Cell A is blank then so too should be Cell B. If cell A contains data, then I want cell B to perform a formula $D$1-E6.
However, if Cell C contains data then Cell B should return to a blank status.
View 6 Replies
View Related
Aug 13, 2009
Sample spreadsheet attached to provide a visual of what I'm trying to accomplish.
Issue: Need to identify which userid's can be deployed and which userid's can't
Detail: In the attached spreadsheet, column A would list all userid's that the project team wants to deploy an application to. Let say for this example that they want to deploy Adobe Acrobat Pro v9 to replace Adobe Acrobat Pro v7. In my actual spreadsheet, there are 1307 userid's in Column A.
As a result of meetings, we have identified 11 Functional Teams that CANNOT have their users upgraded to Acrobat Pro v9. Using internal LDAP look up tools, I am able to generate a list of userid's for each of those 11 Functional Teams. The numbers vary. Again, in my actual spreadsheet so far, Functional Team #1 has 600 userid's, FT#2 has 2700 userID's, FT#3 has 6700 userid's, etc. I'm not done generating the userid's for each functional team but I know I need assistance once I'm done!! LOL!!
Using Column A, I'd like to see if that same userid exists anywhere in Columns B thru L. If it does, then that specific userid cannot be deployed. I would like that userid to be displayed in column N. And in Column O, I'd like to display the name or names of the functional team that this userid is a member of. It could be multiple teams. This will help me identify users that have multiple functional team memberships. Useful data for future deployments.
Using Column A, if a userid does not exist in Columns B thru L, then that userid can indeed be deployed. I'd like to get that userid displayed in Column M.
View 10 Replies
View Related
May 7, 2009
In the columns below B is just 95% of A. What I want to do in column C is to put the maximum value of B over the range that corresponds until the value in A is less than that. For example for my 1st run the value would be $1,125.68 because this is the maximum amount before you fall below that in column A, or in this case hits $1,106.40. I want to be able to perform this automatically down the line (expanding the range until the logic test is true) but can't figure out how to do this. The formula would basically be the max of the range in Column B is > Column A until this is not true, then return the max in Column B for entry in Column C. Then in Column D I would run an identical analysis but return the Row identifer for the dataset.
View 11 Replies
View Related
Sep 21, 2009
I need your help on the attached sample sheet. I have used the concatenate & indirect combination logic to get the desire comments as outcome , but my problem is that I have 300 rows & 30 sheets in a workbook with this logic , it is increasing the size of my file as I have a total of 30 sheets with same logic. I need it in every sheet. If there is any other alternative or solution.
View 14 Replies
View Related
