Tag Info

New answers tagged

0

If you don't have Excel 2013, you can fake the FORMULATEXT function with a short function in Visual Basic. I did use the question that douglaslps referenced to return it as a value instead of just the string. (Very neat answer, that one.) In cell C1, you'd use the formula: =CombineFormulas(A1:B1,"+") and you'd put this function in the workbook's modules ...


0

This is due to your PC's regional settings. You will need to go to the Control Panel, and then "Region and Language", then set the date settings there to match dd-mm-yyyy.


0

If this field is in the Pivot Table then it is also in the Field List, which means you can uncheck the box beside the Field name in the field list, that should hide it.


0

I would create a conditional format that will highlight any values in column A where there is not a corresponding value in column B. In this fashion, you can quickly visually see what tags have not been turned back in. To enter a conditional format Select cell A1 On the ribbon, select Home tab and then Conditional Formatting On the following screen, ...


0

Look into MATCH() - it will display N/A for not found, IF(ISNA(...),...) will help for those if you wish to display something else. Example sheet below. -- Untitled.csv -- Left,Sent+NR,Returned,"nonS,Returned" a,"=MATCH(A2,$C$2:$C$8,0)",e,"=MATCH(C2,$A$2:$A$8,0)" b,"=MATCH(A3,$C$2:$C$8,0)",g,"=MATCH(C3,$A$2:$A$8,0)" ...


0

Modify the VBA so that instead of copying all the data from sheet1 into sheet2, it only copies the data that you need. Each time you're going to copy data from sheet1 to sheet2, check whether the data already exists in sheet2, and if it does, don't bother copying it. You'll need separate indexes for the rows in sheet1 and sheet2. How much VBA do you know? ...


1

Your formula is fine conceptually, but your range references are messed up. To refer to a range of cells on another sheet, you only need to reference the sheet name once, like so: bil!A2:A12


0

You need VLOOKUP. I'm not going to do your homework for you, but here's the basics: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Configure the arguments as follows: lookup_value is the value, or a cell reference to the value, you want matched. table_array is a reference to the cell range which has the data you're interested in. This ...


0

I would up the value of this question because I want a better answer than this: Build your pivot table Select all of the values Paste special in a new sheet "Values Only" Add a "summing column" that adds previous to current Chart it! Below is a TR submit date along with the total # of TRs for that date which the pivot table did ...


0

I navigate it like this.... see image left to right in sequence...I hope this will be of some help


0

As Techie007 points out, the easiest way would be through a macro, but I have a feeling you do not need things to be that advanced. Here's the easiest way. EDIT: You can use the ROW() function to get the current row's number. OBSOLETE (tnx Scott): Use a column (or insert it anywhere). Lets assume you'll use column A. Select Cell A1. Enter value 1 in the ...


1

Open up Excel. Open up the VBA editor (Alt-F11). In the Project pane, double click the sheet you want to apply this to. In the code window that opens, input the following: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) MsgBox ActiveCell.Row End Sub Close the VBA editor. Now as you click/move around to different cells it will ...


1

Sub TOOLS_DELETENAMEDRANGE() Dim nm As name On Error Resume Next For Each nm In ActiveWorkbook.Names If (MsgBox("Delete name- " & nm.name, vbYesNo) = vbYes) Then nm.Delete End If Next On Error GoTo 0 End Sub


0

Better you reset your Browser settings Goto Tools > Internet Options > Advanced tab > Reset From the Reset Internet Explorer Settings window, select the Delete personal settings check box, and then click Reset After resetting, select a default program from Internet options > Internet programs > Set your default program There is a Microsoft fix ...


1

Open an empty spreadsheet and insert an image. Save the spreadsheet and for example we name it spreadsheet1.xlsx Close spreadsheet1.xlsx Open another spreadsheet, save it and name it spreadsheet2.xlsx Now, open spreadsheet1.xlsx Press the save button before copying/moving the tab/sheet. Copy or move the sheet/tab on spreadsheet1.xlsx that contains the image ...


0

Does this work? =IF(ISBLANK(H2),"",H2-G2) The null string might break some formulas that are expecting a number though.


0

A simple solution select all cells drag the columns to a desired pixel size (you will see the column size in both points and pixels as you drag and resize the columns) repeat for rows (choosing the same number of pixels) this should give you perfect square sized cells across the worksheet


0

I assume what you want is to make the replacement once only, and stop further rules once a replacement is made. Taking your second sheet as an example, line 12 "but" should be translated to "however", and stop further rules so that "however" does not translate to "hoyouever" (as rule #17 translates "we" to "you"). A workaround is to first translate ...


1

Your case is exactly like I had couple months ago. I also try to build SQL query in my head every time I need to do some manipulate with data in Excel. So, I have created a little Add-In called XLTools SQL Queries. Now I have published it on our website for others like you and me! It allows to build any SQL query against tables in Excel workbook(s) using ...


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

Yes, you can scroll smoothly in Excel if you drag with the middle mouse button.


0

Print the Excel document to PDF, now you have smooth scrolling. I would suggest (as per my comment) that you look for a better tool for the job as the best option.


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

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



Top 50 recent answers are included