Search results

Use conditional formatting with a formula of =(LEN(A2)-SUBSTITUTE(A2,"(",""))=2 and format as required

Format M46 as [h]:mm as well.

Okay, just =IF(B14=0,0,MAX(0,G14-N14))

@kchiba, YM and MD are just telling DATEDIF which interval to use, the doubles usually mean an interval of one type excluding the other, YM is Months excluding Years, MD is Days excluding Months, DATEDIF is in Excel 2000 help, I still keep a copy of old Excel versions.

How about =MAX(0,G14-N14)

Try =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days"

Outsorting the B items, which the OP seemed to want to do.

Try this ARRAY formula =AVERAGE(IF((A2:A20="B")*(ISNUMBER(B2:B20)),B2:B20))

HR can be greatly simplified Sub HR(ByVal BeginRow As Long, ByVal EndRow As Long, ByVal ChkCol As Variant) For RowCnt = BeginRow To EndRow Rows(RowCnt).Hidden = Cells(RowCnt, ChkCol).Value = "" Next RowCnt End Sub

Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<<<< change to suit Dim Lastrow As Long Dim cnt As Long Dim i As Long Application.ScreenUpdating = False With ActiveSheet Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 1 To Lastrow 'Lastrow to...

fred, LEN(SUBSTITUTE(A1,{"A","a"},"") doesn't produce 113 per se, it produces an array of two values. It does two substitutions, one for A and one for a. The first returns 59 characters, the second 54, giving a total of 113. If you notice, I do a LEN(A1)*2 because I am looking for two letters...

We might be able to help if you can explain what you want. That terse sentence doesn't give us much chance.

This also works for the second case =(LEN(A1)*2-SUM(LEN(SUBSTITUTE(A1,{"A","a"},""))))

If it is just capital A, then LEN(A1)-LEN(SUBSTITUTE(A1,"A","")) If capital and lower-case A, then =(LEN(A1)-LEN(SUBSTITUTE(A1,"A","")))+(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))

Try Private Sub Saveandreturn_Click() Dim sh As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False With Me On Error Resume Next .Parent.Worksheets(.Range("C2").Value).Delete On Error GoTo 0 Set sh =...

Make it read only.

Try =COUNTIF(rng,">"&threshold) and =SUMIF(rng,">"&threshold)-COUNTIF(rng,">"&threshold)*threshold

Take a look here http://chandoo.org/wp/excel-dashboards/

cell.Resize(, 6).Merge

Maybe =IF(ISNA(HLOOKUP(M$2,INDIRECT("'"&C1&"!$C$2:$BB$12"),7,FALSE)),0,HLOOKUP(M$2,,INDIRECT("'"&C1&"!$C$2:$BB$12"),7,FALSE))

Try selecting the formula in the formula bar, and then hit F9. Take a look at what you see.

You could link it to a cell that has a formula that concatenates some others.

TextBox1.Text = Range("A1").Value & Range("B2").Value as an example

It is not clear. You say ... No, I don't want to put value of ranges into one cell ..., then you say ... Instead of C4, It gives me the range of C4 to C11 ... Those two statements are contradictory.

You can't put the values from a range of cells into one cell without concatenating them. You could just use a formula of =IF(B4,C4,NA()) and just copy down.