Autofilter is very useful in editing Member Sheets. By turning on autofilter, you can filter the members that are displayed based on any combination of properties. This makes it easy to find and verify information on the sheet when you are working with large member sheets.
Highlight the top row of the Member Sheet, which contains the column headings (the property names). Select Tools > Autofilter > On .
Excel Editing Features
Moving or Copying a Range of Cells
1. Highlight the range of cells you want to copy.
2. Point to the border of the selection (place the cursor on the box around the cells -- the cursor will change from a cross to an arrow).
3. To move the cells, drag the selection to the upper-left cell of the paste area. Microsoft Excel replaces any existing data in the paste area. (To copy the cells, rather than move them, hold down CTRL as you drag.)
4. To insert the cells between existing cells, hold down SHIFT (if moving) or SHIFT+CTRL (if copying) as you drag.
5. To drag the selection to a different sheet, hold down ALT and drag over a sheet tab. Fill in a Series of Numbers, Dates, or Other Items
This lets you easily create new rows or columns that automatically increment. For example, if just have a column heading "Jan", and use the fill technique below, you can automatically drag the cell to create Feb- Dec in the adjacent columns. Excel intelligently increments the cell values based on whether the cells contain numbers, dates, time, etc.
1. Select the first cell in the range you want to fill, and then enter the starting value for the series. 2. To increment the series by a specified amount, select the next cell in the range and enter the
next item in the series. The difference between the two starting items determines the amount by which the series is incremented.
3. Select the cell or cells that contain the starting values.
4. Drag the 'fill handle' over the range you want to fill. The fill handle is the little square in the corner of the box that highlights the selected range.
5. To fill in increasing order, drag down or to the right. 6. To fill in decreasing order, drag up or to the left.
To specify the type of series, use the right mouse button to drag the fill handle over the range, and then click the appropriate command on the shortcut menu. For example, if the starting value is the date JAN-2002, click Fill Months for the series FEB-2002, MAR- 2002, and so on; or click Fill Years for the series JAN-2003, JAN-2004, and so on. Conditional Formatting in Excel
Format / Conditional Formatting can be used to vary the appearance of a cell based on a value or result of a formula. This can be used in conjunction with properties in BPC, to automatically format rows or columns in a report based on a property of the members in the row or column.
For example, if the Accounts have a style property, the Style value for each account can be retrieved into the definition area of a report using the EVPRO function in BPC for Excel. You can give an account a style value based on whether it is base level input, summary level total, or some total level in between.
Conditional formatting can be used to vary the formatting of a row based on the Style value retrieved. The format will then automatically be applied to a row based on the account in that row, so that if you change
Publishing Office Documents to BPC Web
You can publish any Microsoft Office document to your BPC Web desktop. To facilitate this publishing you should set up a Webfolder that points to the application set directory on the server.
To set up a Webfolder in Windows NT:
1. From the desktop, select My Computer. 2. Select Web Folders.
3. Select Add Web Folder.
4. Enter the server name, URL or IP address for your BPC Web server, followed by the application set directory (the application set name), for example:
http://BPC/Server/GlobalMotors
5. You can now reference this Webfolder location when you publish to the Web from Microsoft Office products.
Publishing from Microsoft Word
1. Select File > Save as Web Page.
Note, if you do not have this menu option, you can need to click the vv at the bottom of the menu to display additional commands. To set you menus so they always show all available commands, use Tools / Customize / Options and un-check "Menus show recently used commands first."
2. Enter the desired filename. The default extension will be .htm. 3. Select Web Folders (icon on left or drop-down at top).
4. Select the Web folder that points to your BPC application set location.
5. Select the appropriate directory. You should select one of the following directories, as these are the ones that are available from within the BPC Web when you add a Web publication to your desktop:
AppSetPublications - use this for publications that apply to all applications within the AppSet, that is, publications that are general to the company.
[application] - use an application directory, for example BUDGET2001, for publications that are specific to an application.
_private - underneath _private are directories by user. Each of these directories is available only to that user.
6. Select Save.
Publishing from Microsoft PowerPoint 1. Select File > Save as Web Page.
(Note, if you do not have this menu option, you can need to click the vv at the bottom of the menu to display additional commands. To set you menus so they always show all available commands, use Tools / Customize / Options and un-check "Menus show recently used commands first."
2. Enter the desired filename. The default extension will be .htm. 3. Select Web Folders (icon on left or drop-down at top).
4. Select the Webfolder that points to your BPC application set location.
5. If you want to select special PowerPoint publishing options, such as publishing only selected slides and other Web options, select Publish..., set the desired options, and then press Publish. 6. To save the entire PowerPoint file to the Web without special options, select the appropriate
directory and select Save. You should select one of the following directories, as these are the ones that are available from within the BPC Web when you add a Web publication to your desktop:
[application] - use an application directory, for example BUDGET2001, for publications that are specific to an application.
[site] – use a site directory, for example HQ, for publications that are specific to a site.
_private - underneath _private are directories by user. Each of these directories is available only to that user.