Reply:

Count with two Conditions
Good Day to All I got a colum with values like 2 , 3 , 4 , 5 , 9 , 7 , 8 , etc. I would like to count how many values i got higer then 5 and smalle then 10. Can anyone help me plse. Thanks in advance. Joao: -- Message posted from http://www.ExcelForum.com Hi one way: =COUNTIF(A1:A100,">5")-COUNTIF(A1:A100,">=10") or try =SUMPRODUCT(--(A1:A100>5),--(A1:A100<10)) -- Regards Frank Kabel Frankfurt, Germany > Good Day to All > > I got a colum with values like 2 , 3 , 4 , 5 , 9 , 7 , 8 , etc. > > I would like to count how many values i g...

Copy and Paste Conditional Formatting
I have set a conditional formatting for Cell B10 =OR(C10>D10*$G$1,F10>G10*$G$1) $G$1 is the absolute referece and the other cells are relative reference. This means the formula for condtional formatting of Cell B11 =OR(C11>D11*$G$1,F11>G11*$G$1) Is there a way to copy the conditonal formatting up to Cell B2000 Thanks. Igbert Copy, Paste Specal - formatting only or Select the entire range of cells you want formatted, with cell B10 being active. Then input your conditional format as you have it written. Format will be applied to all cells, varia...

Sum with condition
Hi, The range K7:H794 has range name of expensescode. In cell H5 the formula is =SUBTOTAL(9,H7:H794). This gives the SUM of the mentioned range. I used the SUBTOTAL because I might use the filter depending on expensescode and have he sum. What changes do I need so that the sum in cell H5 does not include the amounts in colomn H (numbers with currency format) that has an expensescode of 999 or 888 not to be included in the total. i.e. If the expensescode is 999 or 888, do not include that number in the summation. ...

How to Reconcile Two OnOpen Conditions in a Report?
We have a form frmStudentMarks with a date already in a textbox. We use a macro "macDateControl" with the Windows Mode "Masked" that opens that frmStudentMarks. With that macro we had not to write the date when we had to open a rptStudentMailAddress report. But now we have a module which needs to write =LabelSetup() on the property OnOpen of that report. So how to reconcile these two OnOpen conditions together? Any help will be appreciated. Thank you. ...

Conditional format to highlight only cells with formula, not const
I am trying to set up a spreadsheet that will change the cell color if the formula in the cell has been replaced with a specific value. Is there any way to conditional format all cells that contain a formula, but not a constant? Herb Hi, Try this Go to the Name Manager (Ctrl+F3) and click on New (In Excel 2007). In Excel 2003, Ctrl+F3 would do. In the name box type cellhasformula and in the Refers to box, type the foll =get.cell(48,indirect(“rc”,false)) Now go to conditional formatting and in Formula is, type =cellhasformula. Select the desired format Hope th...

Case Statement Problem
Hello, Can someone please help me with my Case Statement Public Function FEE_TYPE(DET_REF As String) As String Dim strRef As String Dim strFeeType As String strRef = DET_REF Select Case strRef Case "LP*" strFeeType = "LP" Case "PP*" strFeeType = "PPP" Case "Prop*" strFeeType = "Prop Tax" End Select FEE_TYPE = strFeeType End Function Query SELECT FEE_CATEGORY: FEE_TYPE(DET_REF) FROM tblFees I do not get any results when I know there are many of them :( Try this: Pu...

Conditional Formating (how to use Offset() in cell reference)
Using XL 2003 & 97 How do enter into VBA code; the cell Offset R-12,C into the Contitional Formatting below? (I want the "not equal to" referrence a cell 12 rows above in the same column) Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=$J$6397" TIA Dennis This might help you get to the next step: Option Explicit Sub testme01() With Selection If .Row < 13 Then 'too high in the worksheet Else 'remove any existing format first????? .FormatConditions.Delete .FormatConditions.Add ...

SUMIF with two conditions ? #2
I have 3 colums colum A G7705 colum B 300 colum C Units I need sum the units in colum C if colum A & B = "G77053000" Can somone Help =SUMPRODUCT(--(A1:A10="G7705"),--(B1:B10=300),C1:C10) Regards, Peo Sjoblom "Mestrella31" wrote: > I have 3 colums > > colum A G7705 > colum B 300 > colum C Units > > I need sum the units in colum C if colum A & B = "G77053000" > > Can somone Help Ok I did that & it worked, now when I try to get the data from another worksheet it gives me wrong data, do you know why? ...

HELP!!! How to Nest AND OR functions
I am attempting to nest the and or functions. I would like the argument to state: IF C47 and D47 are negative... take the absolute value of g47/d47 IF C47 or D47 are negative.... take g47/c47+1 IF C47 and D47 are not negative... take g47/d47 Here is what i have written.... can someone please help?????? IT IS GIVING ME ERRORS =IF(AND(C47,0,d47<0),abs(g47/d47),OR(C47<0,D47<0),(G47/C47+1),(G47/D47)) Hi, =if(and(C47<0,D47<0),ABS(G47/D47),if(or(C47<0,D47<0),G47/D47+1,if(and(C47>0,D47>0),G47/D47,"nothing"))) =IF(AND(C47<0,D47<0),ABS(G47/D47),IF(O...

Need Help In Update Statement
Hello all SQL gurus, I would like to update one field in a table. The field should have data such as: K:\pictures\XXXXX.jpg. XXXXX is the employee id number. "K:\pictures" & "jpg" are constant string. I need to update about 1500 employees which file path is null and their paystatus is A. There are about 3250 employees in MS SQL 2005 db. Table Attributes: dbo.eeKeys -------------------- eeLink Int (4) <-- a unique and appears on all tables eeEEnum char (10) <-- employeeid dbo.eeEmployee ...

Choose Value on Conditional basis
Scenario: ====== I have a to calculate incentive of about 100 employees based on their %Ach Column, we have already made a *Table of Incentive Slots* i.e.if %Ach falls between (>=95% to <=98%) he/she will be qualified for $6500 ...and so on Required: ======= I need a forumla or way to easily calculate the incentive amount of each individual. Details: ===== The Layout of working sheet is below: Name Title %Ach IncentiveAmount Mr. xyz abc 98.7 ?? (this should be equal to 13000) The layout of *Table of Incentive Slots* is below: %AchMin %AchMa...

Nested formular to give a word answer based on 2 cells
I have a s/s which I want to produce a statement based on 2 cells G170 is a cell that contails a sum (if cells above are filled it it will add them up) I170 contains a cost if G170 & i170 are blank then do nothing If G170 is greater than 0 and I170 is greater than 0 then again do nothing but if G170 is greater than 0 and I170 is = < 0 then "No Charge Made" Can anyone help all I get is FALSE I have tried =if(g170<=0, and (i170=<0,No Charge made","")) Hi, =IF(AND(G170="",I170=""),"",IF(AND(G170>0,I170...

Help with iif statement in query
I have a query which has the following iif statment: Accrued Vacation: (([Consolidated]![Regular Day (REG)]/8)/24) This tells me how many hours have been accrued for vacation in 2008 I have another iif statment in this query which is: Expr1: IIf([Vacation_Balances]![Vacation]=" ",[Accrued Vacation],[Vacation_Balances]![Vacation]) This statement is returning a blank cell for those employees who it should be returning the result from the previous iif statment, however it is returning a blank cell. Can someone help me with this - please let me know what other information is nec...

if statement with dates #3
I would like to write a statement like: If(a1>0,1/1/2004) where 1/1/2004 is interpreted as a date. EXCEL is doing the arithemetic and giving me a decimal. I could write 1/1/2004 as its numeric offset and write something like: if(a1>0,37987), but that is rather awkward. What else can I do? TIA, Sanford "1/1/2004"*1 or DATE(2004,1,1) HTH Jason Atlanta, GA >-----Original Message----- >I would like to write a statement like: >If(a1>0,1/1/2004) >where 1/1/2004 is interpreted as a date. >EXCEL is doing the arithemetic and giving me a decimal. >I could wr...

Sort in an IIf statement
To all: I am using Access 2003. I have a IIf statement in a query that looks like this: Owner: (IIf([LLCName]="Smith Properties","SP#" & (IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD")) The LLCNumber field is stored as a number. The LLCName is stored as text. It returns things like: SP#101 SP#89 SP#45 SD SP#XX Right now, it is returning them with all of the 100s before the 90's as it recognizes 1 is before 9, etc. I would like to sort these by the LLCNumber. Is that possible? Thanks in advance for your help! At the bottom of the SQL sta...

Possible conditional formatting?
cell A1="new" cell B1="new" cell C1="new" cell D1="comp" cell E1="ABCDE" Within conditional formatting, how do I go about filling E1 in green if any or all of the A1:D1 range is populated with "comp", keeping the narrative in E1 "ABCDE" All help much appreciated Thanks, Gary Under CF, choose Formula Is and use: =COUNTIF($A1:$D1,"comp")>0 HTH Jason Atlanta, GA >-----Original Message----- >cell A1="new" >cell B1="new" >cell C1="new" >cell D1="comp" &...

Conditional Popup message
I am putting together a spreadsheet that allows a client to enter ineterview criteria that is weighted etc. Part of this is allowing them to change the weights (pervcentages). I would like for a popup message to appear if the percentage goes over 100. I have roughly put this together Function ValueCheck(ByVal Target As Range, Maximum As Integer) If Sum(Target.Column) > Maximum Then Beep MsgBox "Caution, 100% has been exceeded!" End If End Function In the cell that shows total percentage I have =ValueCheck(B7:B28,100) I would like to try make it prett...

If statement please help
I need help, what would this statement need to look like if I want th following info: Column L must equal column N only if column K=TRUE if column K=FALS then column L must equal 0. Thanks in advance Linse -- Message posted from http://www.ExcelForum.com One way: L1: =IF(K1,N1,0) another: L1: =N1*K1 (TRUE is coerced to 1 in math operations, FALSE is coerced to 0) In article <drreamsurfer.1bnjgc@excelforum-nospam.com>, drreamsurfer <<drreamsurfer.1bnjgc@excelforum-nospam.com>> wrote: > I need help, what would this statement need to look like if I want t...

two tables with same field names
I create an order status table for my division each week by running queries on corporate's systems. The field names are always the same, and there lies my problem. I need to measure the change in promise dates weekly to the order line level. For example, I need to pull last monday's promise date minus this monday's promise date to get the change in number of days. Post your table structure so a query can be assembled. -- KARL DEWEY Build a little - Test a little "deb" wrote: > I create an order status table for my division each week by running queries > o...

Conditional validation :-)
Hi, I am using list validation in column A where user can choose 1-9 in each cell. What I wish to create is: if A1=1 then the validation list for B1 is 1.1 to 1.9 if A1=2 then the validation list for B1 is 2.1 to 2.9 and so on.... In effect, its conditional which validation list is being used. How do I set this up?? Thanks in advance Robert Hi! Here's one way: Create a table like this somewhere on your sheet: 1 1.1 2.1 3.1 ... 9.1 2 1.2 2.2 3.2 ... 9.2 3 1.3 2.3 3.3 ... 9.3 4 1.4 2.4 3.4 ... 9.4 ... 9 1.9 2.9 3.9 ... 9.9 Assume the table is in the ran...

Problem Updating Money 2000 to Broker Statement Persists
I have downloaded activity reported by my broker to Money 2000 and am on the page "Review Investment Activity". The page says to click "Next" to update Money to the statement, but when I do the page freezes for about 15 seconds and stays the same. Nothing happens. Why? What can be done to update Money 2000 to the statement? I was advised to close Money, delete the browser temporary files and then open Money with Start, Run and "msmoney -s" (omitting the parentheses), which I did. This did not solve the problem. Any help would be greatly appreciated. ...

CONDITIONAL FORMULA
This is a multi-part message in MIME format. ------=_NextPart_000_0014_01C70410.CA63BB70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, By using the following conditional formula, I am able to get the = category needed based on the sales. The sales are mutually exclusive. In = other words, if there is a value under CAD SALES then USD SALES & USD FX = will be either zero or blank. And if the USD SALES is filled then CAD = SALES will be either zero or blank. And if all three sales fields are = empty then the category should be ...

Accounts to match in downloaded statements
Does anyone know where the "data" is stored that indicates which account a statement is to be matched with on import? I keep getting the wrong account updated when a statement is imported and even when I delete the account number and the bank name from the account information it still tries to match it. In microsoft.public.money, satxfili wrote: >Does anyone know where the "data" is stored that indicates which account a >statement is to be matched with on import? I keep getting the wrong account >updated when a statement is imported and even when I delete...

Max Date + condition
Hi all! In column 1, I have dates. In column 2, I have criterias. How can I ge the max date matching criteria in column 2. Example: Col 1 2004-08-01 2004-08-01 2004-08-02 2004-08-01 2004-08-05 Col 2 AA ABC AA CC CC How to get max date of AA ? I should get 2004-08-02 Thank you -- Message posted from http://www.ExcelForum.com Hi try the array formula (entered with CTRL+sHIFT+ENTER): =MAX(IF(B1:B100="AA",A1:A100)) >-----Original Message----- >Hi all! > >In column 1, I have dates. In column 2, I have criterias. How can I get >the max date matching criteria in co...

Losing conditional formatting when saving
I have created quite an extensive excel file with a significant amount of conditional formatting. I have reached the point where if I save now the conditional formatting of the last few rows is not saved. Is there a way around this or is there a limit to how much conditional formatting you can use? I've never noticed xl dropping any CF, but I don't use it a whole bunch. But if it drops it for you, maybe you could reapply it in the workbook_open/auto_open code each time the workbook opens. You may want to post some specifics. How many rows. How you used Format|conditional forma...