# If Statement Within If Statement (recognize The Cell)

Oct 1, 2008
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.

View 2 Replies
ADVERTISEMENT
Oct 2, 2008

I have the following formula with multiple if statements:

IF(N2>0,N2,IF(O2>0,O2,IF(P2>0,P2,IF(Q2>0,Q2,IF(R2>0,R2,IF(S2>0,S2,IF(T2>0,T2)))))))

This formula refers to cells in columns N,O,P,Q,R,S and T. Every cell in columns N,O,P,Q,R,S and T also contains formulas (VLOOKUPs).

My if statement works fine but when it checks cells in column P, for some reason, it doesn’t recognize cells with non-zero values. I can’t figure out why this is happening and how to fix this problem.

View 9 Replies
View Related
Jul 6, 2009

if the code is 110 add together all amounts attached to that code and sum them in a new cell. Perfect. Now however there is an added level of complexity and i need to figure out how to nest an IF statement into a =sumif() function so that code amounts are summed into their root code, for example the root code of 110, 120, 130 is 100.

So basically i want a function that will sum the specific code (110) and then sum together that amount under the root code (100). Here is an example. The amounts for the lower level codes have been summed but i need the high level codes to be summed together.

View 4 Replies
View Related
Jul 28, 2009

I am currently using an Intersect statement in a worksheet module to perform two things:

1. Insert a time stamp into row 2 when row 1 has a price inserted

2.To clear that time stamp if the price is deleted at some later date.

My problem is with the time stamp value being deleted by the user.

If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.

I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?

View 5 Replies
View Related
Apr 22, 2009

I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."

(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:

=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")

(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.

View 5 Replies
View Related
Feb 14, 2012

I am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.

My purpose is trying to look up

Code:

tb_SelJobID.Value

from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if

Code:

TbSelYr.Value

matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.

Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.

Code:

Sub CmdGo3_Click()

Dim Row As Range

Dim FinalRow As Long

Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range

Application.ScreenUpdating = False

[code]....

Windows 7 with Excel 2010

View 9 Replies
View Related
May 5, 2014

I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:

Code:

Sub testInsert()

Dim adoCommand As New ADODB.Command

Dim sQuery As String

Dim i As Integer

Dim strTest As String

strTest = "test"

[Code] .......

Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:

Code:

Sub testSelect()

Dim adoCommand As New ADODB.Command

Dim sQuery As String

Dim mrs As New ADODB.Recordset

Dim strTest As String

strTest = "test"

[Code] ....

The result I am getting looks like this:

9

8

7

6

5

4

3

2

15

14

13

12

11

10

1

I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.

View 5 Replies
View Related
Jan 6, 2009

I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.

"Invalid procedure call or argument"

I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.

View 9 Replies
View Related
Mar 18, 2014

I am looking to have one formula containing two If Statements that minus.

=IF(B6="investment",C6,0)-IF(B7="gross",C6,0)

So the result of If Statement 1 minus the result of If Statement 2.

What I am seeing at the moment in the cell is FALSE and what I want to see is the sum.

View 2 Replies
View Related
Jun 13, 2009

If A1 shows 10:00am and A2 shows 4:00pm, then A3 calculates the total number of hours: =(A2-A1)*24

But if A1 shows "Off," then A3 shows 0: =IF(A1="Off",0,(A2-A1)*24)

Now, if I want to change "(A2-A1)*24" to another if-statement, how do I do this? I can always set up a hidden cell (A4) that contains the results of the first if-statement, and then say: =IF(A1="Off",0,A4). But can I do this without going through all the trouble of setting up hidden cells?

View 3 Replies
View Related
Apr 25, 2007

Each row represents a call. If a call in column A equals "CW" and it has the highest duration (H:MM:SS) value in column B, then provide me the date (MM/DD/YYYY) for that call that is stated in column C.

i.e.

Column A --- Column B ---- Column C

AB ------------ 0:02:22 ----- 04/14/2007

CW ----------- 0:03:13 ----- 04/16/2007

CW ----------- 0:01:42 ----- 04/13/2007

Thus, the value that should be returned is "04/16/2007".

View 9 Replies
View Related
Jul 3, 2007

I have a problem with a formula inserted with control+shift+enter

The formula is

MEAN(IF(' VALIDATION'!$G$2:$G$59999=D30;'VALIDATION'!$E$2:$E$59999))

In the Validation sheet I have the column G with values 0 and 1, and another column E with numeric values.

Changing the value of cell D30, the formula will calculate the mean of the values in column E that have a value equal to D30 in column G.

The problem is that this function works when D30=1 and not when d30=0, in this case the formula returns N/D.

I have tried using also text values instead of 1 and the formula works . The problem is only when D30 is 0

View 7 Replies
View Related
Nov 17, 2006

i need to do a if statement to take 10% off the value in cell b2 if a2 =yes or if a2 =no then no discount will be applied.

the yes and no in a2 is a v lookup from another worksheet.

View 13 Replies
View Related
Sep 25, 2009

This is the current

Sub IfExample()

If Range("C1").Value = "Yellow"

Range("D1").Value = "COLOR"

End If

End Sub

I get a compile error on the "If" line.

Once I get it working how would I say this correctly?

If Range("C1").Value = "Yellow" or "Red" or "Purple"

The final "hope" is that it will continue down column C and D looking for the condition until first empty row is found at which point the code will stop looking for the condition.

View 9 Replies
View Related
Sep 21, 2006

I need an if statement which returns a value if cell B2 contains the value “Liability” The whole value of B is Liability with a 10 digit number (which is changing). I tried:

=IF(B2="Liability","Liability","")

=IF(B2="Liability*","Liability","") and

=IF(B2=CONCATENATE("Liability"," *"),"Liability","")

But nothing is working. Can’t get my head around to get it up and running and couldn’t find previous threads.

View 3 Replies
View Related
Feb 4, 2014

I am trying to use the if statement, if a cell = a cell that has a word in it then show content of another cell that has a name in it.

View 5 Replies
View Related
Aug 8, 2014

Attached is a small sample which displays what I am trying to achieve - I am trying to create an if statement for cell J2 which says:

IF F2 = 1 then "R", IF F2 = 0 but there is a 1 in either H2, I2, J2 then "W" and IF F2:I2 are all 0's "N"

I Have manually typed the desired output in col J

I Have manually typed the desired output in col J

Attached File : IFFF.xlsxâ€Ž

View 6 Replies
View Related
Jun 30, 2009

i have the condition below.

1<=x<2 = a

2<=x<3 = b

3<=x<4 = c

4<=x<5 = d

>5 = e

how to put the if statement function in the cell? or any better function to use?

View 2 Replies
View Related
Jul 23, 2009

I am trying to do a calculation based on the conditions of two cells but one cell I would need the range of the report. Either way, here is my current statement.

=IF(P2:P15 = "Green Building 15",SUM(COUNTIF(C2:C15,"Over AC")+COUNTIF(C2:C15,"Top Lab AC")),0)

I get a Value# error (though it systematicaly works if you check in the funtion area), and its because of the range I am using, is there anyway to bypass thiss issue or can someone give a better calculation.

View 4 Replies
View Related
Jan 30, 2014

I need a simple IF statement that look up in Column C for any text, and then add the value from Combobox "txtfloors" to Column B .

View 1 Replies
View Related
Dec 3, 2008

I'm looking for an if/then statement that will check if there is any kind of value in cell b before doing a calculation to cell J

View 7 Replies
View Related
Jan 12, 2009

i want to make and if statement in a cell that does the following:

if "J8 > 10" then fail

if "5

View 9 Replies
View Related
Jul 6, 2006

I have a cell that containes a concatenate statement for two named formula. The value taht the cell returns is a multiple of 10 (i. e10, 100, 1000, 10000 etc etc.) then in the adjacent cell, i have a nested if statement giviing differing text dependent upon the other cells value, i.e if less than 1000, return text string of "good" , however the formula does not seem to accept the value given in the concatenate cell.

View 9 Replies
View Related
Jan 15, 2007

When I tried using if & or statements I got an error - so I tried this:

=IF(K7="&","V,",""),IF(K7="1 Space + &"," V,","")

I want to return 'V,' if cell='&' or if cell='(space)&' I want to return '(space)V,' What is wrong with this statement..?

View 5 Replies
View Related
Nov 18, 2008

I have an area of a spreadsheet that I want to "disappear" when a particular option button is selected. I can make the text go away, but part of that area has cells that are formatted differently than the surrounding cells. I would like to change the cell background color, text color, and border setting. How would the syntax read?

View 5 Replies
View Related
Mar 28, 2009

If I have any value in cell A1 then the cell should show 1 if true or nothing if false. I have managed this via

View 3 Replies
View Related
Jun 7, 2009

I have two sub-system tabs (IAS & CCTV) and one calculations page (estimate). I need G3 (estimate) to give me the total price of hours sold on a project.

Because the systems hours can be marked up differently I wrote an average formula. But I need to add an IF statement saying if there is no hours in the sub system then ignore the hour price.

if this formula doesnt take this into account and I delete H3 (CCTV tab) then the overall price of hours sold in estimate will be wrong.

I have attached the sheet.

View 10 Replies
View Related
Aug 11, 2009

G5 - can contain True or false

G6 - contains text but the if is on the basis of this cell being "Hand Delivered" or not

I have tried the following to illustrate what I after:

View 2 Replies
View Related
Sep 10, 2009

In a cell I have a description, say "FOOD" then another cell with amount say £2, then in another cell I want it to be looking and if it sees "FOOD" entered, it takes the amount £2 and adds it to a running balance.

Cell A1 = Food

Cell B1 = £2

Cell H1 = (looks and if Food) adds £2 to cell

View 9 Replies
View Related
Dec 4, 2009

If C36, C37, C38 or C39 contain a 0 then put 0 if not continue with the the formulae

I have this but I know its not right as this is a sum: =IF(C36:C39=0,0,ROUNDUP((C36/C37)+(C38/C39),0)).

View 4 Replies
View Related