## New answers tagged worksheet-function

0

Alright, I'm able to get everything but
Set rngdate = Cells(actdatex, actdatey)
rngdate = "X"
to work. I added msg boxes and found the script makes it to the end and found a location of 3,20 like it should. However I cant get it to print the X and not throw the handler msg.
Here's what I have currently:
Sub worksheet_change(ByVal target As ...

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 headrs and height as the row labels
Z1 and Z2 contain the ...

-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.

1

An INDEX/MATCH combination retrieves only one value, just as you are seeing. If I were you, I would use the SUMIF function. It works like this: SUMIF(Range to test, Criterion, Range to sum). The third argument is optional. If you leave it off, it will assume that the range you want to summarize is the same as the range to test.
If I understand your example ...

1

Your question is very unclear, but let's assume the data looks like this
A B C D
Adam 21 Bill
Bill 22 Dick
Carl 23 Carl
Dick 24 Adam
And you want to take the value in column C, find it in column A and return the value in Column B. This is vlookup
D1 =VLOOKUP(C1,$A$1:$B$4,2,0)
D2 =VLOOKUP(C2,$A$1:$B$4,2,0)
D3 =VLOOKUP(C3,$A$1:$B$4,2,0)
D4 ...

-1

Have one column for each if statement:
+---+------+-------------+--------------------+--------------------+--------------------+--------------------+
| | A | B | C | D | E | F |
...

0

Give this a try:
Public Function GetLastValue(rIn As Range) As Variant
Dim N as Long
N = Cells(Rows.Count, rIn.Column).End(xlUp).Row
GetLastValue = Cells(N, rIn.Column)
End Function
and use it like this:
NOTE:
this is in the desired format:
=GetLastValue(A:A)

0

AS eirikdaude wrote, there are many ways to accomplish your primary task in VBA.
To accomplish the conversion of a string into a range object referring to the entire column, you can use something like:
Function LastValueInColumn(COL)
Dim R as Range
Set R = Cells(1,COL).EntireColumn
This will work (set R to the desired column) provided COL is a valid ...

1

Either just use the worksheet-formula in your macro as well, i.e.
Function GetLastValueInColumn(r As Range) As Range
GetLastValueInColumn = Application.WorksheetFunction.LOOKUP(2,1/(r<>""),r)
End Function
Or use some VBA specific methods, a couple are detailed in this answer on StackOverflow.

0

You can import data into your master workbook from any number of other workbooks by using data connections in excel. I had done this along time ago and it worked out very well. This MS office webpage explains how to set it up. You can even place the data in you master in different cell ranges as to track whose mailings are going out.
Connect data in another ...

0

Open a new workbook, press ALT+F11.
Add a new module, and paste the following vba code:
Sub mergexlfiles()
Dim actwb As Workbook
Dim LastRow As Long
Dim DestRow As Long
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set dirObj ...

0

Depends on how strict the rules/how competent your colleagues are the solutions vary from:
shared workbook: everyone understands everything and is trusted with
the data.
split database: everyone gets an input form and you all
work from the same dataset; they are not trusted with the data but
are all competent enough to use it comfortably
separate ...

1

Since you only need values found in both columns, you only need to search one column for each value found in the other column. To do this, you could use the following formula in cell C1:
=IF(ISERROR(MATCH(B1, A:A, 0)), "", B1)
Then copy-paste C1 down until the end of column B. This would display the value of the cell in column B if the same value is ...

-1

write =IF(A1=B1,A1,"") in the cell C1, then drag it down. you'll get the Values in third.
non-same username shows blank.

5

You are correct, Excel 2013 has the FORMULATEXT function which shows the formula as text.
Since Excel 2010 does not have this feature, there are a couple ways you can show formulas without VBA;
By pressing Ctrl + ` (that's the tilde key
above the Tab). This one shows all formulas in the sheet.
You can place a space it the cell before the =. This causes ...

1

Assuming your table starts in cell A1, I think this formula should do it:
=IF(IFERROR(MATCH($B1;$A$1:$A$5;0)>0;"")="";"";$C1)
The MATCH-statement searches for the value in B1 in the A1:A5 range, and returns a #N/A-error if it can't find the value (the third argument, 0, means we want an exact match).
Then the IFERROR-statement returns an empty string, ...

1

So it seems as though the main reason for the forumlae not being copied down is as stated above; the table was not auto-expanding when new rows were added unless they were created by dragging down and duplicating the rows above.
The formulae being copied down were variations on the same VLOOKUP, and the lookup value used in these formulae was in the column ...

0

This generally happens to me when formula is not the same in all cells of a column.
It should be fixed if you overwrite your formula in the column and select "overwrite all cells in this column with this formula" from context menu.

0

Just drag the formula from Cell Sheet2!A1 down and to the right; LO will adjust the cell reference accordingly:
To drag the formula, move the cursor over the little black box at the lower right of the Cell A1. The cursor will turn into a small cross-hair symbol. Now, click left and move the cursor down or to the left.

0

Your issue could be where you start to apply the conditional formatting. The answer with = AND ($B2 > 0, $F2 = "") assumes you are applying your conditional formatting row 2 of any column.
For example if you are applying your conditional formatting rule to an entire column the first row that the rule is applied needs to be the row number in your formula. ...

0

Here is another solution using multi-conditions in SUMPRODUCT:
=Sumproduct((A3:A159=B3:B159)*(D3:D159={"Target A","Target B"}))

1

If nothing will be in the cells prior to you entering data:
=COUNTIF(C5:C40,"")

1

I think you want:
='Live Inventory Sheet'!D3
Put this in the Pineapple sheet on cell D2.
Do the same for each item in your list.

1

Here's a formula that works for your example no matter what your locale settings are:
=TEXT(DATE(MID(A1,7,2),LEFT(A1,2),MID(A1,4,2)),"dd-mmm-yy") & " to " & TEXT(DATE(RIGHT(A1,2),MID(A1,13,2),MID(A1,16,2)),"dd-mmm-yy")
Here's one that works no matter what's in between the dates so long as the dates are of the format mm/dd/yy and at the very ...

0

You could try this (I'm using UK date format)
=CONCATENATE(TEXT(LEFT(A1,8), "dd-mmm-yy")," to ",TEXT(RIGHT(A1,8), "dd-mmm-yy"))
As you can see, it expects the date formats to be a fixed length (8 character, such as mm/dd/yy or /dd/mm/yy).
Since the above is not working, you could try VBa which will read the current value in ColA and paste the new ...

1

As mentioned elsewhere, you should be using commas, not semicolons.
In addition, because you only have two possible return values, I would recommend using the OR function instead of nesting your IF statements:
=IF(OR(E3=E2, F3=E2, E3=F2, F3=F2), "No", "Yes")

0

You need to use commas instead of semicolons in your formula:
=IF(E3=E2,"No",IF(F3=E2,"No",IF(E3=F2,"No",IF(F3=F2,"No","Yes"))))
If you are going to have a lot of these yukky nested if clauses, it might still be worth considering a little bit of VBA to introduce a SWITCH function - see this example, which can then be used as a formula and is much easier ...

1

I changed the part you needed via introducing a boolean variable bFlag.
Private Sub FloorWallTileCombo_Click()
Dim TileSearch As String
Dim TotalPrice As Double, TotalSF As Double, TotalSurCap As Double, TotalCorCap As Double
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Breakdown")
Dim bFlag As Boolean
ws.Range("A41:A60").Value2 = vbNullString
...

1

Begin by removing all the unnecessary loops. Replace:
For j = 41 To 60
ThisWorkbook.Worksheets("Breakdown").Cells(j, "A") = ""
ThisWorkbook.Worksheets("Breakdown").Cells(j, "D") = ""
ThisWorkbook.Worksheets("Breakdown").Cells(j, "E") = ""
ThisWorkbook.Worksheets("Breakdown").Cells(j, "F") = ""
...

0

@TheUser1024 mentioned the SUMIF function and I was able to get this to work. Hope this helps someone with a similar question!
=SUMIF(Table1014152427[Action],"<>Inventory Count",Table1014152427[Unit Total])

1

You can use the SUMIF function for this. You'll want something like:
=SUMIF(A5:A47, "<>Inventory*", L5:L47)

0

It's not clear from your question what is the data type of your arrays string / range?
It seems that excel
either don't know which sheet do you mean (if your reference is stored as a string, than add sheet to the address too (e.g. sheet1!a1:b1))
either excel misinterpret your data (if your reference is stored in range try to use A.address instead of just ...

2

I think this is what you're trying to do, if I understood your question correctly. In C1:
=IF(OR(ISBLANK(A1),ISBLANK(A2),ISBLANK(A3),ISBLANK(A5),ISBLANK(A6),ISBLANK(A8)),"","This is a message")

0

In Sheet2 cell B1 enter:
=CHOOSE(1+COUNT(Sheet1!A1:A6)/2,"",Sheet1!A1,Sheet1!A3,Sheet1!A5)
and in cell B2 enter:
=CHOOSE(1+COUNT(Sheet1!A1:A6)/2,"",Sheet1!A2,Sheet1!A4,Sheet1!A6)
These formulas assume you are filling Sheet1 in pairs and in the order indicated.

0

A) It's usually good form to show what you've tried
B) this would have probably been better posted on Stack Overflow.SE
I have set up Column A with these 6 rows:
1
2/1/2015
2
2/14/2015
3
3/1/2015
Based on that, I put these formulas in other cells on the same sheet
'return the odd numbered row (1, 2 or 3)
...

2

You can determine greater value using MAX(A1,B1) function :
IF(MAX(A1,B1)=0,0,IF(AND(MAX(A1,B1)>0,MAX(A1,B1)<=10),1,IF(AND(MAX(A1,B1)>10,MAX(A1,B1)<=80),2,IF(MAX(A1,B1)>80,3))))
If all values are positive number, you don't have to check lower bounds so you can simplify the function this way:
...

0

If I were you, I would make a third column C that is
=IF(A1 > B1, A1, B1)
Then you can just do you calculation on a single column. You can hide it if needed.
Lets simplify the equation anyway, as for example if the value is over 80, we don't need to test further:
=if(C1 >= 80, "3", if(C1 >= 11,"2",if(C1=0,"0","1"))))
Note that once a test ...

0

Averageif won't take more than one of each criteria and averageifs won't take more than one range to average.
You can get around it by getting creative-
1 dog
2 cat
3 cat
4 dog
5 cat
1 cat
2 cat
3 dog
4 dog
5 dog
=AVERAGEIF(B1:B10,"cat",A1:A10)
results the same as
1 dog 1 cat
2 cat 2 cat
3 cat 3 dog
4 dog 4 ...

0

The given code does what you want. I did not expect it to be this long, sorry for that. But I think this is quite efficient. Sorry for no comments, but I accidentally spent more time on it that I expected. So, for you this might be difficult to understand the code. Anyways, questions are welcome.
Essentially, you are asked to select the 1st table, then the ...

0

Usually when Excel slows down while editing, it is because of volatile cells which have many dependents and therefore the dependents and their dependents also become volatile and ALL recalculate when an edit is made. You can try the profiler in the FormulaDesk add-in to track down all volatile cells and their calculation speeds.
Disclaimer: FormulaDesk is ...

0

A large number of cells usually isn't a problem. It's more likely that the spreadsheet contains some volatile cells, which in turn have many dependents. Every time you edit ANY cell, ALL volatile cells get recalculated (unless the calculation mode is set to manual - but that can be risky).
You could run the profiler in the FormulaDesk add-in, and set it to ...

2

Some functions will accept a discontiguous range as an argument, e.g. FREQUENCY, RANK, SMALL, LARGE.
In this case you could use one of those latter two, e.g.
=SMALL(MyPicks,RANDBETWEEN(1,9))
If the size of MyPicks might be variable, or some cells may not be populated you could make the RANDBETWEEN part depend on the number of values in MyPicks, i.e.
...

0

Assuming your data is on "Sheet1" and your formula in "Sheet2", I would use
=INDEX(Sheet1!A:A,MATCH("john doe",Sheet1!E:E,0))
or more probably
=INDEX(Sheet1!A:A,MATCH(A2,Sheet1!E:E,0))
or even this (to be explicit)
=INDEX(Sheet1!A:A,MATCH(Sheet2!A2,Sheet1!E:E,0))

1

Better to use DATE function in B4, DATE takes the following arguments
=DATE(Year,Month,Day)
so if you have the month number in B2, and day number in A4 then you can use this formula in B4
=DATE(2015,$B$2,A4)

1

Here's an example where the columns you want to check are A, B, and C.
Option Explicit
Sub DeleteIfBlanks()
Dim r As Long
Dim rng As Range
With ActiveSheet
For r = 1 To .UsedRange.Rows.Count
If LenB(.Cells(r, 1)) = 0 Then
If LenB(.Cells(r, 2)) = 0 Then
If LenB(.Cells(r, 3)) = 0 Then
If ...

Top 50 recent answers are included