Excel Tips
Excel tips
--------------------------------------------------------
--------------------------------------------------------
Preventing Hidden Sheets from Appearing
Tip # 111 from - F1 Get the Most out of Excel! The Ultimate Excel Tip Help Guide
When a sheet is hidden and the workbook is not protected, it will still appear in the list of hidden sheets in the Unhide box (from the Format menu, select Sheet, and then Unhide).
To prevent hidden sheets from appearing in the Unhide box:
1. Click the Properties icon OR from the Control Toolbox toolbar, click the Properties icon.2. From the Visible dropdown list, select xlSheetVeryHidden.
To cancel the xlSheetVeryHidden option for a hidden sheet:
1. Press Alt+F11 to open the VBE.2. Under VBAProject in the left pane, double-click the sheet name you want to unhide.3. Press F4 or click the Properties icon to open the sheet's Properties dialog box.4. In the Visible dropdown list, select xlSheetVisible.5. Press Alt+F4 to close the VBE.
--------------------------------------------------------
Limiting the Movement in an Unprotected Sheet (http://www.exceltip.com/st/TipofTheDay/674.html)
In this example, the sheet is divided into two parts: an area where movement is allowed (the Scroll Area), and an area where movement is restricted (that is, a protected area), without protecting the sheet.
Set the Scroll Area range in the Properties dialog box:
1. Click the Properties icon OR From the Control Toolbox toolbar, click the Properties icon.2. In the Scroll Area text box, type the scroll area range, or type the defined Name for the range (this is flexible, if you plan to add more data to the range, it is better to use a defined Name).3. To cancel the Scroll Area restricted range, clear the Scroll Area text box.
Note: You cannot add two Scroll Areas to the Scroll Area text box.
View screenshots at: http://www.exceltip.com/images/screenshots/674.gif
--------------------------------------------------------
Get the Last Nonblank Cell in An Excel Range
By Neil J. Rubenking June 22, 2004
Total posts: 6
I have an Excel spreadsheet containing several instances of information for each month in a two-year period. In each case I need a formula to display the contents of the last nonblank cell in the two-year range. I tried using the IF function, but you can only nest seven IF functions. I tried other formulas without success. What formula can I use to display the most recent entry—that is, the last nonblank cell?Jean CooperFirst, let's work up a formula that will yield the row number of the last nonblank cell. We'll assume labels in cells A2:A25 and values in B2:B25, with the formula for the most recent entry in B26. In that cell, type=MAX(IF(NOT(ISBLANK (B2:B25)), ROW(B2:B25),0)) Instead of pressing Enter, press Ctrl-Shift-Enter to create an array formula. Excel processes each element of the array B2:B25 in turn, returning the row number for nonblank elements and a zero for blank elements. The MAX() function returns the maximum of these results, which is the last nonblank row.To get the contents of that cell, we'll use the OFFSET function. OFFSET returns the contents of a cell that's a specific number of rows and columns away from the starting cell. To convert the row number found with the first formula into an offset, simply subtract the row number of the starting cell. Don't delete the first formula; insert OFFSET(B2, at the beginning and add -ROW(B2),0) at the end. That yields this new formula, which you must again finish using Ctrl-Shift-Enter.=OFFSET(B2, MAX(IF(NOT (ISBLANK(B2:B25)), ROW(B2:B25),0))-ROW(B2),0)You can copy this formula to the cell just below each of your other two-year ranges. Note that the same technique works even if the data has some gaps.
---------------------------------------------
Double Drop-Down Validation in Excel
http://www.pcmag.com/article2/0,1759,1585456,00.asp
Captured Text Selection:
Is there a way to make the drop-down list that appears for cell validation conditional on the value in another cell? Suppose one cell contains a drop-down list containing categories like Cabinets, Appliances, and Flooring. I want another cell to show a drop-down list of various cabinets when Cabinets is selected, of appliances when Appliances is selected, and so on.Bob FusariIf your spreadsheet doesn't already have them, create a separate list of each type of product. Highlight each list in turn and choose Insert Name Define from the menu, using the category name as the name for this range. For our simple example, we'll put the three categories in the first three columns of a worksheet, with the category names at top. Then select the row of category names and use Insert Name Define to name that range Categories.Let's suppose the user will choose a category in cell E2 and a product in cell F2. Click in E2 and select Validation from the Data menu. Select List from the drop-down titled Allow and enter =Categories in the Source box, then click OK. This establishes a simple list-based validation rule the user can enter only values found in the Categories range, and those values appear in a drop-down list.Now click in cell F2, select Data Validation, and choose List from the Allow drop-down, as before. This time in the Source box enter =INDIRECT(E2). This selects the range named by the contents of cell E2. If E2 contains "Cabinets," the list of cabinets will be presented in F2, and so on. When the user selects a different category in E2, the list in F2 changes. It is still possible to get a mismatch if the user chooses a product and then changes the category.
---------------------------------------------------------------
Counting Unique Names in Excel, Reloaded
August 3, 2004By Neil J. Rubenking
In the article "Count Unique Names in Excel" ( www.pcmag.com/article2/0,1759,1537551,00.asp ) a reader asked about finding unique names in a list of 900,000 names. You suggested that the number may have been exaggerated, given Excel's limit of 65,536 rows. But what if the names were in multiple columns? As long as there aren't more than 65,530 unique names, you can still use a pivot table, employing a rarely mentioned feature. I have tested the approach with 232,140 (4*65,535) names and it only takes a minute or two to set up, and then only seconds to generate the pivot table (I'm running on a 3.2GHz machine with 1GB of RAM). Here's how to proceed:1. Starting with the second column, click on the column letter and hold down the Ctrl key, then one at a time click on each column to the right, selecting all columns except A once each. Then press Shift+Ctrl+Plus (the plus in the top row, not the numeric keypad). This will insert blank columns to the right of each set of names.2. Click on the column letters for each of the blank columns while holding down the control key. Then type 1 and press Ctrl+Enter, to enter 1's in all the cells adjacent to the names. (I am assuming that the names extend all the way down the columns. If they don't, use another method to select the appropriate cells.)3. Choose the menu command Data Pivot Table... and check Multiple consolidation range (a command I've never had a use for before!). Click on Next, and click on Next again, accepting the default. In the Range box of Step 2b, select the range A1:B65536 and click on Add. Continue this process for each pair of columns containing names and adjacent 1's. Click on Finish and use your count command on the pivot table output.Shane DevenshireAs noted, the Multiple consolidation ranges option in the Pivot Table wizard allows consolidation of more than the 65,536 items that can fit in a single column. The reader with the original question may indeed have been dealing with 900,000 names. We repeated the process described above using 14 columns rather than four, for a total of over 900,000 names, and the Pivot Table processed the results in under 15 seconds.On the way to exercising the Pivot Table feature, this tip employs several useful Excel features that might not be familiar. Holding Shift+Ctrl while pressing the top-row plus key will insert cells in relation to the highlighted selection. If a whole row or column is selected, it will insert a new row or column. But, as we've seen here, if multiple rows or columns are selected, it will insert new blank ones before each. Also, if you select multiple cells, enter some data, and press Ctrl+Enter, the data will be copied into all of the cells. Not everyone needs to perform pivot-table consolidation of huge quantities of data, but everyone can benefit from these handy tips to work smarter in Excel.
--------------------------------------------------------
To select Column(s) or Row(s) Using Keyboard Shortcuts: To select a column(s), select a cell or several cells in a sheet, and press Ctrl+Spacebar. To select a row(s), select a cell or several cells in a sheet, and press Shift+Spacebar. To continue selecting columns or rows, press Shift+Arrow keys.
--------------------------------------------------------
To reduce the workbook size:
1. Press Ctrl+End to find the last cell in the used area within the sheet. In the screenshot, the last cell is E17.
2. Find the last cell containing data in the sheet. In the screenshot, the last cell containing data is cell C11.
3. Delete all the rows between the cells containing data to the row of the last cell in the used area. In the screenshot, the rows to delete are 12:17.
4. Delete all columns to the right of the column of the last cell containing data, up to the column of the last cell in the area used. In the screenshot, the columns to delete are D:E.
5. To quickly delete the rows, select the first row to delete (row 12 in the screenshot), press Ctrl+Shift+Down Arrow. To quickly delete the columns, select the first column to delete (column D in the screenshot), and press Ctrl+Shift+Right Arrow, press Shift+F10 and from the shortcut menu, press Delete.
6. Repeat the steps above for each sheet in the entire workbook.
7. Press Ctrl+S to save the file.
--------------------------------------------------
If you have several PCs connected to your network, keeping the time synchronized between all of them is a snap. Designate one system to be the timekeeper. On the others, create a shortcut in the \Windows\StartUp folder with the command line C:\WINDOWS\NET.EXETIME\\MAIN/SET/YES, in which MAIN is the network name of the system that keeps the time.
--------------------------------
Stew Stryker
When you save a workbook as HTML, Excel does indeed dump in a load of extra formatting. I don’t see any way to avoid that, but you can create your unformatted table in a few simple steps.
Save the Excel file as tab-delimited text, and load the resulting file into Word. Search for tab (^t) and replace each occurrence with </TD><TD>. Search for end-of-line (^p) and replace each instance with </TD></TR>^p<TR><TD>. You now have an extra <TR><TD> on a blank line at the bottom, and the same text missing at the start of the very first line. Move the extra text to the beginning of the first line. Add the <TABLE> tag at the top and </TABLE> at the end and you’re ready to copy the document into your HTML page.
I have an Excel spreadsheet that I set up to keep track of my stock portfolio. Given a stock symbol in a cell, is there an easy way for Excel to retrieve the current stock price via a macro or some other option?
A. Caraktir
In Excel 2002, there are several different ways to get the information you want. Check to make sure you have Smart Tags enabled, then select AutoCorrect Options... from the Tools menu and click on the Smart Tags tab. Once this feature is enabled, a smart tag will appear when you point your mouse cursor at a cell containing the stock symbol. Clicking on the tag’s down arrow will present a menu of options (see screen). You can choose Insert refreshable stock price.... to put the information into a worksheet or just choose Stock quote on MSN Money to display current information in your browser.
If you locate the stock information you want on a Web page, you can import it directly into Excel. Choose Data Import External Data New Web Query... from the menu and enter the URL (for example, http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&Symbol=BORL). The Web Query window will display the page with small arrow icons marking the page elements you can import. Click on the arrows for the desired elements, which will then change to check marks. Now click on the Import button to bring those elements into your spreadsheet. You can click on the Refresh Data button in the External Data toolbar at any time to update the information.
-----------------
To gain quick access to the items in your Control Panel, add a pop-out Control Panel folder to your Start menu. To do this, right-click the Start button, and select Open. In the Start Menu window that appears, select File, New, then Folder. To name the folder, type:
Control Panel.{21EC2020-3AEA-1069-A2DD-08002B30309D}
------
Launching an EXE File
If you have a setup.exe, rather than a Windows Installer (MSI) file, you can launch it using the Wscript.Shell object.
set objShell = CreateObject( "WScript.Shell" )
lngReturn = objShell.Run ("setup.exe", 1, 1)
set objShell = Nothing
----
You can quickly move to the last cell in a contiguous block of blank or non-blank cells without leaving the current cell. You simply double-click the current cell’s border; the border you choose will determine the direction of the move. For instance, if you’re in the middle of a large block of data and you want to move to the top cell within that block of data, simply double-click the top border of the current cell. You’ll know you’re in the right place to double-click because the cursor will change to a four-headed arrow. If you want to move to the last cell to the right within that same block of data, double-click the current cell’s right border. The same is true when moving to the left or down. Double-click the left or the bottom border of the current cell, respectively.
You can get a similar effect by holding Ctrl while pressing an arrow key. If the adjacent cell in the selected direction contains data, you’ll move to the last non-blank cell in that direction. If not, you’ll move to the next non-blank cell in that direction.
----
I want to create an Excel template that includes a default header. One of the fields should be the date the file was last saved, so the file can be "version controlled" using that date rather than the current date.
Peter Marlin
This should be easy, but it isn’t. Unfortunately, when you open an Excel file, the date that the file was last modified changes to the current date. Thus, any attempt to extract the date the file was last modified using VBA’s DateLastModified property sim
Powered by Post2Blog Express
posted by g8rpal at 1:16 PM
0 Comments:
Post a Comment
<< Home