## New answers tagged worksheet-function

0

This can be achieved with a custom number format that consists of 9 zeros 000000000
Source Using a custom number format to display leading zeros
In Microsoft Excel, you can use either of two methods for using a custom number format to display leading zeros. Use one method for fixed-length numbers and use the other method for variable-length numbers.
...

0

The easiest way to do this will be to set up a helper column that that gives the bi-weekly point totals for each employee. Once you have that in place, you could replace the current point-totaling formula on sheet 1 with something like this:
IF(COUNTIF([range containing bi-weekly point totals],[criteria is
"0"]) = 0,[original point total ...

0

Not sure I totally understand the question, but I THINK you may want
=SUMIF(B4:Z4,"UK IR",B1:Z1)
which means: Sum all cells in row 1 where row 4 equals "UK IR"
Edit:
In the sample file the sum formula is in cell C20 with a simple =SUM(D20:AS20)
You only want to include values in the sum where row 4 contains the text "UK IR"
So, the formula you need is a ...

0

Define a named for the hyperlink target cell, then set the hyperlink to that named range. When rows are inserted or deleted, the named range will still point to the correct cell.

0

Since SUMIF() can be applied only to single row or column, your evaluation must be carried out in 2 steps.
First use SUMIF() on rows and also concatenate the condition values for each summation in an adjacent cell. This concatenated string will serve as a key in next step.
Next apply SUMIF() to the column that contains row sums, using the "key" from ...

0

I had this issue. In my case the tilde character (~) broke the MATCH function, even with pasted values. There may be other special characters that do this as well.

1

I'm using excel 2010 and just highlight the two columns that have the two sets of values I'm comparing, and then click the Conditional formatting dropdown on the home page of excel, choose the Highlight Cells rules, and then differences. It then prompts to higlight either differences or similarities and asks what colour highlight you want to use...

1

Add a new sheet named roomtype to the workbook and in column A enter the room numbers. In column B enter the 1 or 0 for each room. Then go back to the sheet where you want the formula.
=VLOOKUP(A3,roomtype!A:B,2,0)
where A3 is the cell that has the entered room number.
The first parameter to VLOOKUP is a value specifying what value to look up.
The ...

2

You need to use sumif with well set references, enter this to C1 and fill down: =SUMIF($A$1:A1,A1,$B$1:B1).

0

You need to use the CONCATENATE function to concatenate Date: and the value, like so:
=IF(OR(I1=TRUE,J1=TRUE),CONCATENATE("Date:",Sheet1!D2),IF(AND(I1=FALSE,J1=FALSE),"Date:________N/A_________"))

4

To keep it simple, just add following rows to your code before "strFilename = strOutputFolder & "\" & strItem":
strItem= replace(strItem, "~", "_")
strItem= replace(strItem, """, "_")
strItem= replace(strItem, "%", "_")
strItem= replace(strItem, "#", "_")
' and so long ...
strFilename = strOutputFolder & "\" & strItem
[UPDATED]
Well, as ...

0

If your data is sorted by Pay End and Emp ID, you can fill down the following formula in the Exposure field (starting in E2).
=IF(B2=B3,0,SUMIFS($C$2:$C$10,$B$2:$B$10,B2,$A$2:$A$10,A2,$C$2:$C$10,"<"&D2)+SUMIFS($D$2:$D$10,$B$2:$B$10,B2,$A$2:$A$10,A2,$C$2:$C$10,">="&D2))
This formula works by checking if the employee ID below matches the ...

0

I would say
=(YEAR(B1)*12+MONTH(B1))-(YEAR(A1)*12+MONTH(A1))
even though the recommended formula is EOMONTH.
Since OP didn't quite accept (or specify) anything while someone else made a remark without coming up with anything constructive here is a non-integer solution that is based on the latter month's date (as denominator, e.g. ...

1

This is what you are looking for
The formula for ease of copy-paste is added below
=SUMIF($B$4:$C$12,D4,$C$4:$C$12)
Note: If you are new to excel formulas and wondering about the $ signs in the formulas - read about relative and absolute cell addressing here

1

We will just use formatted values:
Sub qwerty()
Dim i As Integer
Dim s As String
i = 2
Do Until Cells(i, 1).Value = ""
If (s = "") Then
s = Format(Cells(i, 1).Value, "0000")
Else
s = s & ", " & Format(Cells(i, 1).Value, "0000")
End If
i = i + 1
Loop
Cells(1, 2).Value = s
End Sub

2

Put your 50 character string in cell A2 and put your field widths in B1,C1,....
Then in B2 enter:
=LEFT(A2,B1)
and in C2 enter:
=MID($A$2,SUM($B$1:B1)+1,C1)
and copy C2 across. Here is an example:
Just be sure that top row of field widths add up to 50
While this solution does use individual string functions, it is completely driven by the field ...

0

I know that this is an old thread but I don't think the previous posters really understood your question.
Here's the formula that you are looking for:
=IF(ISERROR(NOT(MATCH(A1,$B$1:$B$11,0))),A1,"")
http://randoltech.blogspot.com/2015/04/excel-find-differences-between-two.html

0

The ROW(ITS!3:56) argument of SMALL doesn't make sense.
What your formula is doing now:
The SMALL function is calling for the {3,4,5,...,56}th smallest item in your array of matches in a single cell. By pure accident, this has the illusion of working as intended because only the first term in this array is used in the single-cell formula. So, in your first ...

1

You start your formula in row 3 that's why it starts from 3rd result. Either start in first row or use ROW(ITS!3:56)-2.
Why do you need to use formula at all? I'd use pivot table (if you need to show cucumber in each row you can set "repeat item labels").

0

Do you need a formula or just a one go task?
If you don't need to do this often I think manual approach is the quickest here: copy your second column to the third column to the right position (shifting up one row) then sort by first column and you can quickly delete all rows now where first column is "job state".
If you need to do this frequently than even ...

0

You need something like this
Fill column L with:
=INDEX($B$1:$B$16,(ROW()-ROW($L$2))*2+1)
And Column M with:
=INDEX($B$1:$B$16,(ROW()-ROW($L$2))*2+2)
$B$1:$B$16 is area of values to display (targets and job state)
$L$2 can be somewhere in first row of area filled with formulas above.
//You can use any other columns instad L and M

0

If you skip lines than list won't be auto extended.
I think the only solution here is to use user defined function in the definition of your range.

0

You may want to select the data of the Full Name column of your table, then apply a named range, e.g. Full_Name on it. Voi'la, your reference is expanding with your table.
EDIT
So:
You insert a Pivot referencing your table. Place the "Full Name" tag to the visible tags. Place it to the first row for convenience.
You set a hella lot of things in your ...

2

If you multiply by 100 that will get rid of the decimal point, then you can just use TEXT function to give you the leading zeroes, e.g. with your salary amount in A1
=TEXT(A1*100,"000000000")

0

If you mean that column A has numbers and you want to search within a specific range then try this version for "Cabinet 1" (only usin rows with 1-8 in column A)
=COUNTIFS(B:B, B2, C:C,">" & $C2, A:A,">=1",A:A,"<=8")
Change as required for other ranges

2

Combine the SUBSTITUTE and TEXT functions. You may have to change the "." based on your regional settings. To convert the value shown in A1 the formula is:
=SUBSTITUTE(TEXT(A1,"0000000.00"),".","")
Note that it will only convert numeric values. Any text or errors are simply returned unchanged.
If you put in very large values, the result will be longer. ...

0

You are using absolute referencing with the $. This anchors the reference to that address only. No matter how much you drag or copy it, your formula is locked to $B$5:$B$51.
By removing the $ you will be able to drag the formula to other cells and it will adjust automatically using relative referencing.
Keep in mind, Excel is not a mind reader. Depending ...

0

Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wkb As Workbook
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim cell1, cell2, myrange As Range
Set wkb = ThisWorkbook
Set wks = wkb.Sheets(1)
Set wks1 = wkb.Sheets(2)
targetrow = Target.Row
targetcolumn = Target.Column
task = wks.Cells(targetrow, 3)
For i = 3 To 300
a = wks1.Cells(6, i)
...

0

SUMIF is really similar to COUNTIF. If you don't use checkboxes, =SUMIF(D$3:D$6;"<>";$B$3:$B$6) should do the trick:
As you can see from the function wizard in the screenshot, SUMIF can take 3 arguments, Range, Criteria, and Sum_range.
Range is the range of cells you want evaluated against some criteria.
Criteria is the criteria you want to check ...

0

You can do this with a repetitive array formula. It's not that pretty, but it will get you what you want. Type in the following and enter by pressing Ctrl+Shift+Enter.
=INDEX(A:A,MAX(IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1)))
What this does (besides make you appreciate SQL):
IF(C:C="Norway",G:G,-1) represents an array the ...

0

Consider using a small User Defined Function (UDF):
Public Function LowerKiller(sIn As String) As String
Dim L As Long, LL As Long
Dim sCh As String, temp As String
For L = 1 To Len(sIn)
sCh = Mid(sIn, L, 1)
If sCh Like "[a-z]" Or sCh = " " Then
Else
temp = temp & sCh
End If
Next L
...

0

You can use a single SUMPRODUCT formula like this
=SUMPRODUCT((Timecard!$A$2:$A$5574=[@[TC Ref]])*ISNUMBER(MATCH(Timecard!$J$2:$J$5574,{"REG","ADDTL","FMHOL","SHIFT"},0)),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)
MATCH function matches J2:J5574 against an "array constant" containing your 4 values - if there's a match then MATCH returns a number, ...

0

I could get it to work using a helper column as shown below
TC Ref Pay Rate Hours Value Earn Type
7926 45.32 -3 -135.96 REG
7926 45.32 -3 -135.96 REG
7926 45.32 -4 -181.28 OT
7927 45.32 3 135.96 REG
7927 45.32 7 317.24 REG
7927 45.32 3 135.96 DT
7927 ...

0

Another option you might look into is copying the data (values only) to a new workbook and see if the performance issue still exists. It might be that the workbook is corrupted somehow.

1

If each of the disjoint ranges is comprised of a single cell, the following formula will return the number of cells in the range, no matter what the content, or even if they are blank, which I think is what you want:
=LEN(CELL("address",MyPicks))-LEN(SUBSTITUTE(CELL("address",MyPicks),",",""))+1

0

I am using Office 2007 and the formula you show almost works for me.
You need just value instead of number value like so:
=VLOOKUP((VALUE(RIGHT(B10,3))),A$3:A$13,1,FALSE)
This is assuming that Column 1 is in A1 and Cloumn 2 is in B1 and the room numbers start in A3.

2

Changing "Column 1" to A and "Column 2" to B, and assuming you are starting in row 1, try the following in column C:
=vlookup(right(b1,3),a:a,1,false)
Fill column C with this function, and you will wind up with a list of room numbers that are in both columns, as well as #N/A next to any number in B that does not appear in A.
EDIT: you may have to use the ...

0

The formula solution would be difficult to implement / debug, prone to errors, and greatly increase file size. The VBA procedure could be broken into steps:
Delete all the sheets created last time the macro ran (this is easiest if the log you're showing is the only sheet in the workbook but you can adjust to anything)
Get a unique list of the Acct names ...

0

Thanks to Raysafarian for all the great guidance I was able to come up with a working formula.
=IF(Table_timecard[[Earn Type]]="BONUS",VLOOKUP(Table_timecard[[TC Ref]],(Table_check[#data],10,0),0)

0

If we can make the assumption (as presented) that years are in column A and amounts are in column B, this array formula will work in any cell that's not in column A or B (such as E32).
=INDEX(A:A,MATCH(INDEX(B:B,MATCH(TRUE,B:B>=1000000,0)),B:B,0))
For array formulas, type it in and CSE - press Ctrl+Shft+Enter. This will display the formula with curly ...

2

You can do this with INDEX and MATCH. I'm assuming your date columns are sorted from oldest to newest. To find the first date after A2 in B2:B30, use the following formula.
=INDEX(B2:B30,MATCH(A2,B2:B30,1)+1)
The key is that MATCH(A2,B2:B30,1) will return the index of the most recent date in B2:B30 that matches or is before the date in A2. Since your ...

0

You can use the SUMIF function to accomplish this.
The syntax for that is: SUMIF(range, criteria, [sum_range]). So by using the following formula, you can SUM the values in column B for the rows which have "A" in column D.
=SUMIF(D:D, "A", B:B)
By using D:D or B:B it tells Excel to look at all the values in the column. If you would like, you can ...

0

You are searching a table right?
=IF(Table_timecard[[Earn Type]]="BONUS",VLOOKUP(Table_timecard[[TC Ref]],(Table_check[#data],10,0),0)
That should work. You need to specify that the lookup_array is a table, search the whole thing and then pick your column to return.
If you aren't searching in a table, you need to specify a cell range, which could be ...

0

Literally, what you want translates to something like this in the worksheet module for sheet 1 -
Sub worksheet_change(ByVal target As Range)
Dim actdatex As Integer
Dim actdatey As Integer
Dim newcell As Range
Dim rngdate As Range
If Not Intersect(target, Range("A2:A999")) Is Nothing Then
On Error GoTo handler
For Each c In Range("Sheet3!C6:AK7")
...

2

How about this little beauty:
=SUM(FREQUENCY(MyPicks,MyPicks))
Therefore, your whole formula would be:
=SMALL(MyPicks,RANDBETWEEN(1,SUM(FREQUENCY(MyPicks,MyPicks))))
EDIT: As pointed out by barry houdini, the SMALL function will only return numeric values. In that case, the SUM(FREQUENCY()) solution is overkill. Instead, simply use
...

0

Hi as I understand I hope this help u

0

See if this helps:
Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1
Set ws = Sheets("Sheet1")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:C1"
titlerow = ws.Range(title).Cells(1).Row
icol = ...

1

You can usually use SUMPRODUCT for this - I'm not quite clear on the criteria for you but generically you can use this setup
=SUMPRODUCT((A2:A10=Z1)*(B1:J1=Z2),B2:J10)
where A2:A10 are the row labels, B1:J1 column headers and B2:J10 the data. Note the data range must be the same width as the column headers and same height as the row labels
Z1 and Z2 ...

-1

I noticed that a previously saved file with more than one tab selected, also known as group mode, when opened again will default to manual calculation. If this happens you will need to set calculations back to automatic by any method mentioned above.

Top 50 recent answers are included