## New answers tagged worksheet-function

0

I'm not sure why you're using semicolons to separate your arguments, but on my system, I need to have commas.
I think the main problem is due to how your logic is laid out. With this function, you'll want to put in the same range twice, and put each end condition separately: AVERAGEIFS(A2:A10,B2:B10,">=16:00",B2:B10,"<=18:00")

0

I assumed the error lay within the code because when I got the runtime error, there was no output to the given cell. The problem actually occurs if a formulas were to be written to a cell and it would generate that pop up window that states "We've found a problem with this formula...." Not the same as #NAME?, #VALUE!, #N/A type errors. In my case, my code ...

1

You can use an array formula to solve this (also known as a CTRL+Shift+Enter formula).
The first thing to do is manipulate your second table so that there is a date column in its own right. (It's generally best to avoid having information held in your column headers.)
I've used Tables (Insert > Table) so it's a bit easier to read the formula.
Now in ...

0

I would use the Power Query Add-In for this. You can load data from an Excel Table into a Query.
I would start by building one Query that filters down to just the rows that need to be deleted. Then I would start another Query based on the same Excel Table. With this one I would Merge the first Query, joining by ID Number and adding any column from the ...

0

A very easy way with notepad++:
If you need to make modification on a column, then:
copy paste your excel colum on notepad++
press alt + the arrows.
what you will write will be add in all lines

0

I would use the Power Query Add-In for this. Power Query can import the data from an Excel Table. I would use the Unpivot command to transform each cell into a row, with a single values column holding all the absences values (e.g. AL, SL.5). Then I would use the Split command on that column to separate the first 2 characters (AL, SL) from the .5 values. ...

1

You could use the match function to see if the number is in your list. If the match function doesn't find an exact match it returns a n/a. Using this fact we can flag cells as either being in the list or not. Once you have a flag filter the rows and delete all the ones that are in your list.
=IF(ISNA(MATCH(A2,$F$2:$F$3,0)),"Not in List","In list")

2

So you have
=COUNTIFS('S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date],">="&B1,'S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date],"<="&B2)
Which is basically count how many rows in the table are both greater than or equal to B1 AND less than or equal to B2. This would be the same as:
=SUMPRODUCT(('S:\Data\HQ\2014\Report.xlsx'!Table2[RFI ...

0

Here's the formula I came up with:
=INDEX($A$1:$E$1,1,MATCH(LARGE(B2:E2,1),A2:E2,0))&INDEX($A$1:$E$1,1,MATCH(IF(LARGE(B2:E2,2)=0,LARGE(B2:E2,1),LARGE(B2:E2,2)),A2:E2,0))
I put your array of data on a worksheet starting at cell A1 and put this formula into cell F2 (with the header "Product Code") and filled down the rest of the column. It should give ...

1

You just need to combine the formulas:
=IF(B2="N/A","N/A", IF(NETWORKDAYS(A2,B2)<0,"UNKNOWN",NETWORKDAYS(A2,B2)) )

1

The problem with your first formula is that ISBLANK() does not consider cells with formulas as being blank (even if the result of the formula is blank). One way to workaround this is to use ="" (or <>"" for not blank):
=IF(BI2<>"",BI2,IF(BH2<>"",BH2,IF(BG2<>"",BG2,"")))
Or you could use something like this:
...

0

Create the following macro:
Function SHEET_OFFSET(Offset, Ref)
' Returns cell contents at Ref, in sheet Offset
Application.Volatile
With Application.Caller.Parent
SHEET_OFFSET = .Parent.Sheets(.Index + Offset) _
.Range(Ref.Address).Value
End With
End Function
Then, if you want the May sheet to access the April!Q42 cell, use ...

1

The following module takes a range and replaces cell references with values found in the referenced cells. Uses Range.Precedents to get the referenced cell addresses to a string and Split to transform it to an array of cell addresses. This array is then used to find and replace matching text in the formula of the target cell.
Usage
Copy the entire code to ...

0

I would recommending using a Pivot table for this:
Add HTS and Country of Origin as Row Labels
Put your value as "Values". If it's numeric, it will default to summing automatically
Set a "Value Filter" of "Greater than" 2500.

0

I would use the Power Query Add-In. I would import the data from each Excel files into separate Queries, use the "Merge" command to join them together, probably using your common ID column.
http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872
Power Query has lots of other data-massaging features that ...

0

This will, obviously, take some VBA to pull off. I see a potential problem with your logic, but the VBA will do exactly as you requested. I'll talk about the problem at the end.
Open your VBE with Ctrl+F11 and create a new Module for your workbook. In that module add:
Sub hideColumns()
Dim ws as Worksheet
Set ws = Sheets("YOUR SHEET NAME") ...

0

When a formula is copied down a column the cell references change to maintain row-to-row references (relative). [screenshot: column C]
When a dollar sign is added before a row and/or column reference, XL keeps it fixed to that reference (absolute). [screenshot: column B]
Perhaps if a portion of your formula had the dollar signs removed and then copied the ...

2

Type your “1”.
Drag the square (I believe that it’s called the “fill handle”)
with the right mouse button.
When you release the button, you’ll get this menu:
Select “Series…”. You’ll get this dialog box:
Set “Step value” to 2, and there you go:
...

0

Click in cell B2.
Create a conditional format with the formula
=OR(AND($A1=$A2,B1<>B2), AND($A2=$A3,B2<>B3))
like this:
and apply it to your entire data area:
Here’s the result:

0

So this depends on how you want to get your output, but what I would do in your situation, if there's not a reason that you can't, is to write a simple macro in VBA that scans across all columns and either returns the location of the different column, creates a link to that cell, or highlights the differing values.
The macro could flow like this:
Get ...

0

First, create a table with what the batchcodes mean. This can be in a different sheet, as long as it is in the same workbook (otherwise it might break, or the lookup doesn't work properly.)
In the actual sheet, in A you have the batch numbers. In B you would add a =VLOOKUP(A1,!sheet2.A:B,2,FALSE)
See also: ...

3

You have to type the first 2 or 3 numbers in and then select all of them and then drag down. eg. Type 2 4 and then drag and you will get 2 4 6 8 10 etc...

0

If it is just 30 times, you might want to use the easiest way of linking cell data.
Make it so that you have both the source and destination open in the same Excel. Then select the cell you want to enter the formula. Type an = and without typing anything else, just click on the cell you want to link to, and press enter.

0

I'm not positive I fully understand what you are trying to do but if it is just selecting a value from a different table then you might have more luck with the index function which you can input a proper array to
Y1=index('[Flacflow balancesflacflow.xlsx]Data'!$A77:$C77,B3)
This assumes that B has a value like 2 (which would mean you want the second cell ...

0

If you use the function below it will give your the row letter.
=CHAR(<COLUMN NUMBER>+64)
Therefore if you use the below you will get your column letter, C.
=CHAR(MATCH("Hello",1:1,0)+64)
All you therefore need to do is to concatenate it with itself to get what you are looking for, C:C.
...

0

I would use the Power Query Add-In. It has a Group By command where you can specify the grouping columns and also calculate a Sum or similar Aggregate functions:
http://office.microsoft.com/en-au/excel-help/group-rows-in-a-table-HA103993875.aspx
You can output the result to a new Excel Table.

0

Your formula in Column C should be as follows then:
=MID(B1,1,SEARCH("><",B1,1))&A1&MID(B1,SEARCH("><",B1,1)+1,LEN(B1)-SEARCH("><",B1,1))
RESULT:

0

Try this:
Right-click on the tab you'd like to hide.
Choose "View Code"
Under "Visible" change the value to (2) "VeryHidden"
If you care to password protect it from there continue...
In the VBA window, right-click the sheet and choose "VBAProject Properties"
Under the Protection tab, check "Lock Project for Viewing"
Set password
I'm almost certain ...

0

Updated
Try pasting the following VBA into "ThisWorkbook" (If you need help with this, ask in comment):
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ValidationIndex As Long
Dim rngTest As Excel.Range
'assumes the data validation is in a cell named "rngTest"
On Error Resume Next
Set rngTest = Sh.Range("rngTest")
If ...

0

=evaluate(put_reference[s]_here)
This is a semifunction - it can only be used in Name Manager.
This is how you can use it:
- you point to a cell and you open Name Manager
- then you write =evaluate( and click on the cell you want [best to keep relative reference]
- you finish the formula with )
- you give it a NAME - [in this example I'll just call it ...

1

Usually, you would use a VLOOKUP, but the index and the values to be pulled are in the wrong order, in that case, use INDEX and MATCH:
=INDEX(A:A, MATCH(C1, B:B, 0))
Find the row in which C1 appears in column B, then from column A, take the value in the row that you just found.
So, for the first one, it looks for Y in column B, finds in in row 2, then ...

0

First of all, you need to use INDIRECT in your first formula.
The second argument to MATCH needs to be an array, and you are passing it a string.
Change it to
=MATCH(B$4, INDIRECT(CONCATENATE("'", A5, "'!$E:$E")), 0)
Secondly, in the second formula,
you are calling CONCATENATE and passing its return value to another CONCATENATE.
That’s quite ...

1

Create a dummy sheet.
Link it to the Data Set sheet as follows:
Click in cell A1, and either
type ='Data Set'!A1, or
type =, click on the “Data Set” tab, and click in cell A1 on that sheet
and then type Enter or click on the checkmark to the left of the Formula Bar.
Click in the Name Box (to the left of the Formula Bar)
and type a range that covers ...

2

I would probably create two additional columns. In one column create a flag if there is a text file and in the other a flag if there is a cover. The formula would look like this if you column with extensions is column B. These cells would go in row 2 and would be copied down.
They will return the number of cells with the text that matches the ISBN number ...

0

From what I can gather, you need to do the following:
Go to D1 and type =A1 (this will set the value equal to A1), then drag the small black box in the bottom right corner down as far as you need (this will change the following values to =A2, =A3, =A4, and so on)
Do the same for column H but use B1 instead of A1.
Is this what you were looking to do?

0

You could perhaps use something like this:
=COUNTIF(OFFSET(E2,MATCH(H2,E2:E11,0)-1,0,MATCH(H3,INDEX(E2:E11,MATCH(H2,E2:E11,0)):E11,0)),"B*")
I'm using OFFSET here to define the range in which you will count the cells.
The first MATCH indicates where the range starts, the second match indicates where it ends.
The second match uses INDEX and MATCH to make ...

1

Here's what a solution using a pivot table might look like.
To achieve the tabular layout go into the Pivot Table Design menu, and change the Report Layout to Show in Tabular Format. Turn off sub-totals for each column.
Remember to use a SUM on your "Count" value field because you have numbers greater than 1 in there.

0

I managed to figure this out in the process of writing this question. My solution was to copy the old table to a new tab, remove duplicates based on only the columns I care about, then place a SUMIFS() formula in the new “count” column that checks each cell in the old tab against the cells in the columns I care about in the new tab and adds up the numbers in ...

0

The COUNTIF(S)/SUMIF(S) family of functions generally attempt to interpret your data as numeric where possible, though here there is evidently some confusion as it does not appear that it can consistenly interpret both your range values and your criterion as being of the same format.
In this case:
=SUMIFS(C2:C8,A2:A8,"Graduation",B2:B8,">1986-05-21")
...

1

Here is a neat, formula-based solution.
Create a New Name in Name Manager called wshNames, with the value:
=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))
In any cell you want to show the number of worksheets, enter: =COUNTA(wshNames)
Note: Step 1 relies on an XLM formula. More on this here - note you will need to save as a ...

0

you have to use a little bit of VB for that.
Try for "Table sheets -> without Diagrams"
Public Sub test()
MsgBox ThisWorkbook.Worksheets.Count
End Sub
Try for "All Sheets" -> including Diagrams
Public Sub test2()
MsgBox ThisWorkbook.Sheets.Count
End Sub

0

If you've followed all of the directions in the original question's answer, and everything looks good until the point you've mentioned above, I think you're just not selecting the new Sector Weight series when you try to reformat (I recreated the Chart and had trouble getting the selection right).
Once you've added the Sector Weight series, go to Format ...

1

Converting dates from OOo/LO Calc to MS Excel has to consider Excel's inherent restrictions regarding the handling of dates (see HP005199291 regarding Excel 2003 and HP010342495 for Excel 2013). Especially, Excel doesn't support calculations for dates before 1900. You can enter such dates in Excel only as strings, using the apostrophe to mark the cell ...

0

Try this formula for A starting in the third iteration (row 4):
=IF(SIGN($D3)=SIGN($D2),IF(A3=A2,A3,$B3),IF(A3=A2,$B3,A3))
You can apply the same to C as well.
=IF(SIGN($D3)=SIGN($D2),IF(C3=C2,C3,$B3),IF(C3=C2,$B3,C3))
This uses two levels of conditions; first it checks for sign change in f(m), and then checks whether the midpoint was previously being ...

0

Yes, using VLOOKUP. Explanation taken from here, courtesy of bigmyk2k in reply to similar question.
The work here is not done in the dropdown (which is easy to make), the
work is done in each column you want to populate.
For each cell, write a VLOOKUP which finds the desired value in a
table, based on the value in the drop-down cell. In the ...

1

If you want to make it accurate you can try out the formula
=(YEAR(E2)-YEAR(D2))*12+MONTH(E2)-MONTH(D2)-(DAY(E2)<DAY(D2))
the formula should be self explanatory except the last part -(DAY(E2)<DAY(D2)), which subtracts one month if it is not a complete month.

0

The formula you already have in place is about as reliable as it can get if you can live with a month averaging 30 days. You could improve it by making sure it only returns the full number
=int((e2-d2)/30) will cut off the decimals.

1

If the date you mention is always the first of the month, then this one will also work:
=INT((WEEKDAY(A1-1)+EOMONTH(A1,0)-A1)/7)

0

This can be accomplished if you know which weekday the month started on. I'm sure there are other ways of getting the same information.
Here's the formula I came up with
=IF(EOMONTH(A1,0)-IF(WEEKDAY(A1)-1<>0,7,0)-(A1-(WEEKDAY(A1)-1))>27,5,4)
If the difference between the end of the month and the last Sunday of the previous month (or the first ...

0

You need to set the datatype in your FieldInfo argument
Redim FieldInfoVal(1 To 1)
FieldInfoVal(1) = xlDMYFormat
do don't have 4 columns so I am using an array of 1
then pass in FieldInfoVal after FieldInfo:=
You might be able to dispense with the variable by using
FieldInfo:=Array(0, xlDMYFormat)
I think the array is 1 based so it will ignore the ...

Top 50 recent answers are included