Tag Info

New answers tagged

-1

I'd recommend using google docs. It's possible there. Each sheet represents a monthly statement. These are all pulled automatically, minus the first row and some of the columns, into a yearly sheet, which various pivot tables make use of.


0

You can use Remove Duplicates feature to get only unique records. Select Column A i.e, category and Column B i.e, Sub-Category and use Remove Duplicates or use Unique Records feature in the Advanced filter. and paste the records some where else. Here , Don't select Column C i.e, Price After, You will get Two columns with unique records. cat ...


2

Better yet just sort column C in order highest first but keeping column a grouped as well. Sort > Custom Sort > Sort by column A, then by column C


0

All you need to do is replace the last "" with A1 so that if excel doesn’t find a < or > it will return the entire contents of A1 instead of blank: =IFERROR(MID(LEFT(A1, FIND(">", A1)-1), FIND("<", A1)+1, LEN(A1)),A1)


1

Use the following: =IF(B1<(A1-2),1,IF(B1>(A1+2),-1,0)) The first IF checks if B1 is less than A1-2, which means that B1 is less than A1 by more than 2. It returns 1 if true, otherwise it checks for the second condition; IF B1 is more than A1+2. It returns -1 if true, otherwise 0. Finally you close both IF functions.


0

Yes it is possible, here is one of the ways that it can be done: =IF(ABS(A1-B1)>2,IF(A1>B1,1,IF(A1<B1,-1)),0) The first part of the formula checks to see if there is a difference of more than 2, if so it will then check to see if A is larger than B (1) or if B is larger than A (-1), finally it will give the result if the difference between A and ...


0

You can check for multiple conditions using the AND function. Add 2 additional conditions; Cell D10 contains a date, (greater than your starting date). The DVD was not returned. Suppose you enter "Returned" in column F when a DVD is returned, you can use the following: =IF(AND(D10>1/1/2000,D10<(TODAY()-14),F10<>"Returned"),"Yes","")


0

As your problem arises from having the cell D10 blank when the DVD is returned you have to make two verifications, one if cell is blank and other if the date is over 14 days. So you have to nest IFs, just like this: =IF(D10="","",IF(D10<(TODAY()-14), "Yes",""))


0

The formula on cell C3 should be: =(B3/$B$2)-1 Copy down this formula to cells C2, C3, C4 and so on. Select column C, right-click and select Cell format -> Percentage


0

Pick an unused column, say V, in V3 enter: =IF(AND(A3="Thing1",B3="Thing2",C3="thing3",U3="No"),ROW(),"") and copy down: and if you want a nice condensed list with no spaces, then in W3 enter the array formula: =IFERROR(INDEX($V$3:$V$176,SMALL((IF(LEN($V$3:$V$176),ROW(INDIRECT("1:"&ROWS($V$3:$V$176))))),ROW(V1)),1),"") Array formulas must be ...


0

You are getting "FALSE" because you haven't close the previous IF function. Try this.. =IF(P571>=1,IF(P571<=44,"Variable Tempo"),IF(P571>=45,IF(P571<=69,"Slow Tempo"),"Not Found"))


0

The following formula neither needs additional cells nor does it have to be entered as an array formula: =RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1) If a single zero shall be returned for strings like 0 or 00, the following formula may be used: =IF(A1="","",RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(LEFT(A1,LEN(A1)-1)&" ...


1

Ok, I think I got what you're trying to do and I think VLOOKUP could not be the way to go for this, using MATCH instead. Let's begin... 1ST APPROACH "all-values-in-a-single-cell": On sheet "tickets" C2 cell put formula =IFERROR(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0),0); D2 cell put formula =IF($C2>0,INDIRECT("'tickets_info'!O"&$C2),""); ...


0

The problem you're having is because when xcel saves a sheet as CSV, it actually separates the values with a semicolon (;) and not a comma. So once you've saved the sheet as a CSV, close the sheet and then open the sheet with notepad. Here you will see the values separated with ; - Now click Edit, Replace, and in the "Find" field type the semicolon (;) and ...


0

How about INDEX and MATCH? Tutorial: http://www.mrexcel.com/articles/excel-vlookup-index-match.php You can do a MATCH in one column and use that in the INDEX function for the other columns in the same row. That way you only lookup the row once. You would basically have two tables on top of each other. The top one showing values aligned with table 1 that ...


3

Using the mouse does not mean that you have to drag a formula down hundreds of pages. You can use a simple double click instead. Enter the formula into C1, then double-click the Fill Handle (the black square at the lower right corner of the cell) Before the double click: After the double click on the fill handle: Edit: How far will Excel fill down? ...


2

You can use Ctrl and the arrow keys, with or without Shift held down, to navigate around the spreadsheet quickly. Ctrl+arrow keys jumps directly to the "last" cell in that direction which has content. Holding down Shift selects cells as you move around. Depending on how your spreadsheet is laid out, you may also be able to use Ctrl+Home (jumps to the top ...


0

Much as been already suggested, you need to check if you are returning a result. As you only want to check existance, a VLOOKUP function is a bit of overkill. I would suggest a simpler MATCH function with a check to see if it is returning a row number (found) or error (not found). =IF(ISNUMBER(MATCH(D4, '[05.14_Wave 5 Priority 14 (Big+Cranberry) ...


1

Assuming the sheet wherein you stored your list of codes is called CodeSheet ={SUM(IF(IFERROR(MATCH(f3:t3,CodeSheet!$A$8:$A$22,0),0)>0,f4:t4))} This is an array formula. Enter it without the curly braces, and hit control-shift-enter. Excel will put the curly braces in for you. How it works: MATCH(f3:t3,CodeSheet!$A$8:$A$22,0) ...


0

Wrap each VLookup in NOT(ISERROR()) to achieve what you're looking for: =IF(NOT(ISERROR(VLOOKUP(A1,sheetname!A:A,1,FALSE))),"some statement","another vlookup")


0

You can do this with a formula. Do an index/match lookup through each sheet. If the sheet does not contain the number it is cached by the IFERROR and you try to look in the next sheet. Below formula looks through all 5 worksheets :) Paste it into cell B2 in sheet ATT and copy it down... =IFERROR( IFERROR( IFERROR( ...


-2

To switch Excel columns and rows, 1.Open the spreadsheet you need to change. 2.If needed, insert a blank worksheet. 3.Click the first cell of your data range such as A1. 4.Shift-click the last cell of the range. Your selection should highlight. 5.From the Home tab menu, select Copy. 6.At the bottom of the page, click the tab for the blank worksheet such as ...


2

If we have this in Sheet1: and run this short macro: Sub ReOrganize() Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = Sheets("Sheet1") Set sh2 = Sheets("Sheet2") Dim N As Long, M As Long, i As Long, j As Long, K As Long Dim t1 As String, t2 As String N = sh1.Cells(Rows.Count, "A").End(xlUp).Row K = 1 For i = 1 To N ...


1

The change to incorporate multiple columns is quite simple. Only one line of code needs to be changed: Private Sub Worksheet_Change(ByVal Target As Range) Dim A As Range, B As Range, Inte As Range, r As Range Set A = Range("I:I,K:K,P:P") Set Inte = Intersect(A, Target) If Inte Is Nothing Then Exit Sub Application.EnableEvents = False For Each r In ...


2

A function is part of a formula. So the below should work in exactly the same way. Select the column you want: Paste your formula: Then hit Ctrl + Enter. Done!


0

What you are describing could be accomplished with a pair of offset() functions: B2 =offset($a$1, row()*5,0)-offset($a$1, (row()-1)*5,0) offset expects a starting cell, the number of rows to offset by and the number of columns to offset by. row() returns the current row number so =offset($a$1, row()*5,0) references A5 in the first row (row()*5 = 1*5 =5) ...


1

If your sheetname includes spaces, you must use single quotes: ='My Sheet'!B3


1

How do I reference an excel sheet name in an excel formula? Use the following syntax: SheetName!CellAddress Notes: The worksheet name comes before the cell address, followed by an exclamation mark !. If the worksheet name includes spaces, enclose it in single quotation marks '. Example: 'Sheet Name with spaces'!CellAddress Further reading ...


1

You simply use =Sheet2!C25 In this case, I'm referencing Sheet2, cell C25 If I had named the sheet something like DataSheet, I would use =DataSheet!A1 The above would access the A1 cell of DataSheet So, if my DataSheet A1 had the value 5, and my Sheet1 A1 had the value 15 , then on my ResultsWorkSheet I could have =DataSheet!A1 + Sheet1!A1 or ...


0

I belive the issue is first with your formula, you need to use AND like this =IF(AND(P3>=1,P3<=44),"Variable Tempo",IF(AND(P3>=45,P3<=69),"Slow Tempo",IF(AND(P3>=70,P3<=94),"Slow-Medium/MidTempo","Not detected"))) Instead of showing "Not detected" you may want to simply show "" (to show nothing) Copy and paste that down in colum Q ...


1

You can use Sumif() to sum all values in column A where column B equals a specific value. =SUMIF(B1:B10,"a",A1:A10)


0

Eliminate col 1, place the weight in col 2, put this in col 3. =(IF(C5="Yes"; "1"; "-1") * (IF(C5="Yes"; F5; "1")))


0

If you're not adverse to using VBA, I can get back with more detailed code later, but in pseudo-ish code: Dim array(1, 1) As Long For i = 1 To Len(table) ' if value is already in the array, skip this part. if not, redim the array and store the value. If Not Match(Cells(1, i), array) <> 0 Then ReDim array(1, i) array(1, i) = ...


0

You can reference across Sheets, but you will need the correct syntax. So, set up your first Sheet. Then, on the second Sheet set up what data you want to replicate (based on the employment date being true). You can create a similar condition for A2 to fill in the Name of employee. When these two cell formulas are ready, you can simply copy down the ...


0

This can be done with a straightforward IF statement. In the column you want the values to be "copied" to in the second sheet: =IF(C1<>"", A1, "") Then autofill down. Note that the second sheet will likely have a sparse list, rows will be empty when the corresponding row in the first sheet has an empty C column. However if you want a contiguous list ...


0

Do you mean that you want the assigned group of employees populate a drop-down in the next column? If so, what you are after is dependent data validation. It requires a bit of prep work to set up all the products and the employees. On a helper sheet you need to create a list of employees for each product and then assign each of these employee lists a ...


0

UPDATE: I was informed by fixer 1234 that my solution was only introduced in EXCEL 2013, so it will not work for you issue in Excel 2007 I am going to leave my previous answer up however, as it would be a solution to someone searching for how to do this with Excel 2013 or later. This should work for you: =MID(FORMULATEXT(F16),2,10) Result: A3 as text. ...


1

I think some of your IF logics are out (reversed). Place all formula into one IF statement, as you seem to have only two states - Yes (1) and No (-1). =IF(C5="Yes",1.5, -1) That should be it?


1

With multiple criteria consider using Sumproduct(): =SUMPRODUCT(--(A1:A15="Richard")*(C1:C15>=1500)*(C1:C15<=1800))


3

Excel does not have a Sort nor a Unique function. I fail to see how you make that work. But if you have that working, however you do it, you can use a simple Sumif to add up all the numbers for the animals =SUMIF($A$2:$A$5,C2,$B$2:$B$5) Copy down. Or you could use a pivot table on the original data. Four clicks. No formulas.


0

I'm a little confused about how the names and percentages are organized on your spreadsheet. Anyway you could post an image just so we can have a better idea? by your description, I think it looks like this: Is this about right?


0

=1*(0<sumproduct((A1:A5="Mike")*(B1:B5>=0.6))) Change the ranges to suit your data. This function turns (Mike, Mike, Bob, Charlie) into (1,1,0,0) and (0.4,0.7,0.8,0.2) into (0,1,1,0) and performs a sumproduct on the results. Note that this is a volatile formula and may slow your sheet over 74k rows. Set calculation to manual if so.


0

For each function (IF and ISBLANK) you need to have a pair of parentheses, surrounding the argument(s) to that function.  ISBLANK takes one argument: the value that you want to check for whether it is blank.  IF takes three arguments: the Boolean (true/false) value, the "if true" value, and the "if false" value.  So your formula should be ...


1

Cells(1, 1).Formula = "=LEFT(A2;5)" using .Formula instead of .FormulaR1C1 solved the problem.


0

Try this one, with double quotes moved to include the colon, and a second & added after the H1s: =SUMIF(INDIRECT("$'PC Activity'.$F" & H1 & ":$F$" & H2);"Bank.*";INDIRECT("$'PC Activity'.$C$" & H1 & ":$C$" & H2))


2

This should work: =IF(C2="NO",IF(E2=100,Sheet2!A1,""),Sheet2!A1) If C2="NO" then: If E2=100 then: Sheet2!A1 Else: "" (blank) Else: Sheet2!A1


1

There are two different ways I would do this: VLOOKUP or INDEX/MATCH. In sheet 2, cell B2, use formula =VLOOKUP(A2,Sheet1!$A$2:$B$11,2,false) Drag this formula down as far as you require, where: A2 is the first account code in sheet 2 you wish to look up $A$2 is the start location in sheet 1 with the row column indicators preceded by a dollar sign ...


2

First, you need to extract the last name from cell B1 MID(B2,FIND(" ",B2)+1,99) Then you can use that result to perform a Match() on column A: =MATCH(MID(B1,FIND(" ",B1)+1,99),A:A,0) (in C1 of the screenshot) This will return the row number of the found name. If a name is not found, the cell contains #N/A. To avoid that, you can use IsNumber to return ...


1

AVERAGEIF is the best answer for Excel 2013+. For older versions, you'll need to use a third column, C. All cells in column C (with a value to the left) have this formula: =IF(ISNUMBER(SEARCH(C$7,A1)), B1, "") Cell, C7 just has the word desk typed out so that those formula don't need to be fixed every time you change the search. After this, you simply do ...


0

I'm not sure if using column C is a possibility for you, but here is one option that I have tested. In Cell C1, input the following formula: =IF(ISNUMBER(SEARCH("desk",A1)),B1,"") Copy/paste this formula down for each row in column C Average the amounts that appear in column C. If column C is already used, just insert another column in its place



Top 50 recent answers are included