Tag Info

Hot answers tagged

4

There's no need to use VBA to change any cases. There are certainly VBA functions that do this as shown by other answers but, unless you're already writing something in VBA, this is overkill. The following formulas will convert cases for you. =UPPER(A1) converts all letters to uppercase =LOWER(A1) converts all letters to lowercase =PROPER(A1) converts the ...


3

Vista supports the Volume Snapshot Service (VSS) that allows for the recovery of files even if you haven't made a backup. If you have the Business, Enterprise, or Ultimate version of Vista, simply right-click on the current version of the Excel file, choose "Properties" then select the "Previous Versions" tab. Then select the prior version of the file and ...


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


2

Let's suppose the letter (L/F) is in column B, then: Select the first row. Open Conditional Fomatting / New Rule. On type of formula, select "Use a formula" In the box put =$B1="F" On the button Format select the format that you want. With the row still selected use the Format Painter to copy this format to the other rows.


2

It's correct at my side. Please note that pivot tables doesn't update automatically, you need to manually go to pivotTable - Options - refresh to show the latest data. If you wan't the table updated whenever you open the sheet then you need a macro for it. You can find several guidances by searching for "excel automatically update pivot table vba", ...


1

To break out your series based on a value in your source (or any other column), you could use helper columns. Create an additional column for each value you want to break out. For your Boston column, use the formula =IF([Source] = "Boston", [Parameter 1],na()). Repeat for each additional source, replacing the comparison value as appropriate. Define the ...


1

There's an option to prompt for filename. Go to Data / Connections, choose your connection, click properties. Under the refresh control section, on the Usage tab, take the tick out of the box "prompt for file name on refresh"


1

Here is a totally different approach. The screenshot below shows the top of the worksheet with the data in columns A and B and a sequence of charts. The top left chart is simply an XY Scatter chart. The top right chart shows the distribution with the X axis scaled as desired. Error bars have been added to the middle left chart. The middle right chart ...


1

Excel is an application that is subjected to the limitation put on it by the operating system. If you run it on the latest OS, let's say Windows 8.1, with a half decent video card with enough RAM, you should be able to get a smooth user experience with scrolling.


1

Try this formula: =IF(A1<>"",MAX(OFFSET(B1,0,0,IFERROR(MATCH("*",OFFSET(A2,0,0,COUNTA(B:B)-ROW()+1),0),COUNTA(B:B)-ROW()+1))),"") To get the height of your data I used COUNTA() on column B, it will work correctly only if you don't have blank cells in column B.


1

I need to capitalize every cell in excel with first letter as capital?any easy way to accomplish it? Yes, use this macro. Remember to take a back up of the file first! Sub uppercase() For Each cell In Application.ActiveSheet.UsedRange If (cell.Value <> "") Then cell.Value = UCase(cell.Value) ' this will make the entire cell upper case ...


1

Here is a simple macro to convert text in a range to all CAPS. Change the range on the 3rd line to the range you want to convert. Sub Uppercase() ' Loop to cycle through each cell in the specified range. For Each x In Range("A1:A25") ' Change the text in the range to uppercase letters. x.Value = UCase(x.Value) Next End Sub



Only top voted, non community-wiki answers of a minimum length are eligible