Search results

Suzanne, A couple of points first. You are using SUM unnecessarily a lot. SUM is only needed if you are addig things, not in every arithmetic operation. For instance you use =SUM(TODAY()-[@[Hire Date]])/365 whereas you would only need =(TODAY()-[@[Hire Date]])/365 and you certainly don't...

Try =--SUMPRODUCT(--(A3:K3<$A$2:$K$2))=COUNTA($A$2:$K$2)

Write yourself a UDF Public Function Conc(rng As Range, Optional delim As String = ",") Dim cell As Range For Each cell In rng Conc = Conc & cell.Value & delim Next cell Conc = Left(Conc, Len(Conc) - 1) End Function`

Try this alternative =SUMPRODUCT(COUNTIFS(Audits!$B$2:$B$179,{"Tech*","Temp*"},Audits!$F$2:$F$179,Plan!ProductCategory))

This should do it for you =SUMPRODUCT(--((ISNUMBER(SEARCH("FRND",Q3Answrs)))+(ISNUMBER(SEARCH("COMMUN",Q3Answrs)))>0))

Try deleting the sheet with that name before the rename, wrap it with OnError in case it doesn't exist.

It is all explained in Help. If reference refers to a multi-area range, you can specify which area withing the range that you find the intersect for For example =INDEX((A1:C6, A8:C11), 2, 2, 2) Looks at row 2, column 2 of the area A8:C11, that is B9. Can't say I have ever needed to use...

Public Sub ProcessData() Dim Lastrow As Long Dim i As Long Dim cell As Range Application.ScreenUpdating = False With ActiveSheet Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = Lastrow To 2 Step -1 If .Cells(i, "B").Value2 = "" Then .Cells(i...

Use COUNTIFS =COUNTIFS(name_range,"somebody's name",attendance_range,"Absent")

If VLOOKUP works, why do you want to use OFFSET and MATCH?

Assuming that the dates are in A1:L1, I think you need =SUM(INDEX(A:L,0,MONTH(TODAY())))-INDEX(A1:L1,MONTH(TODAY())) adjust if your ranges are different.

Use =MIN(8,SUM(A2:C2)) and =SUM(A2:C2)-D2

Off the top With Activesheet lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To lastrow If .Cells(i, "B").Value < Now() Then .Cells(i, "A").Interior.Colorindex = 3 End If Next i End With

Not sure, but try this Private nTime As Double Sub time() If nTime <> 0 Then Application.OnTime nTime, "timecall", , False nTime = Now + TimeValue("00:01:00") Application.OnTime nTime, "timecall" End Sub Sub timecall() Dim ticker As Integer Dim check As Integer...

Try this macro Public Sub ProcessData() Dim lastrow As Long Dim lastcol As Long Dim endcol As Long Dim i As Long, ii As Long Application.ScreenUpdating = False With ActiveSheet lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("A1:E1").Resize(lastrow).Copy...

You need to array-enter, that is hit Ctrl-Shift-Enetre after typing in the formula, not just Enter.

No, it is not possible, the data and PowerPivot are embedded within the workbook. If they don't have Excel 2010, PowerPivot doesn't work anyway; if they do have Excel 2010, why not just install PowerPivot?

Try this =SUMPRODUCT(--(B2+(C2>0.3)*100<=(B$2:B$6)+($C$2:$C$6>0.3)*100))

Can you get hold of Excel 2010, that has slicers, even better!

Some initial comments. Overall, I think it has a very pleasing effect, your use of colours is nicely muted, the labels do not obstruct. You have mis-spelt contributors in the label for Top 5 Contributors (btw, I would use a numeric 5 rather than Five here, more immediately impacting). I...

Q1. Your code should be Sub Calendar_click() Calendar.value = Date range("F3").value = Calendar.value Unload me End Sub TODAY() is an Excel function, not VBA. Q2. Assuming your formula is in A2, you could create an Excel name (Formulas>Name Manager>New), call it formula for...

I would think that you need to get some informaton about the user or their country location, and you are not going to get that without VBA. BTW, I live in the UK, and as far as I am aware, 4th June is a bank holiday, as is the 5th.

SUMPRODUCT is an array formula, it just is not array-entered, so the performance difference between SP and an array-entered SP is minimal. But, if you array-enter it, you might just as well not bother with SP =SUM(NOT(ISERROR(MATCH(A1:A4,E1:E4,0)))*IFERROR(A1:C4*1,0))

Try this http://www.speedyshare.com/5BRbq/Chandoo-Reg.xlsx

Use =IFERROR(INDEX(Sheet1!A2:B7,MATCH("Apple",Sheet1!A2:A7,0),2),"") or =IF(ISNA(MATCH("Apple",Sheet1!A2:A7,0)),"",INDEX(Sheet1!A2:B7,MATCH("Apple",Sheet1!A2:A7,0),2)) for Excel 2003