Recent content by 1726

There is also an RTRIM function which removes trailing spaces Cheers

Matt "the buttons naturally disappear as I scroll down" I tend to put such buttons in the Freeze Panes region so that they stay on the screen. Not an answer to your original problem but hope you find this of use. Cheers

Use an Action rather than a Hyperlink. (Next to Hyperlink on Insert Ribbon) The Hyperlink option in Actions includes "Last Viewed Slide" A more obscure option can be achieved using Custom Slide Shows. A normal Hyperlink to a Custom Slide Show can be set to automatically set the calling slide...

Use: Insert > Quick Parts > Field Select: MacroButton field Click: Field Codes Button Replace: 1st parameter with None - this is a non-existent name of a macro to run when the field is clicked! Add: a second parameter which will act as the on screen prompt. e.g. Enter Your Name Back in the...

You can control the Colours used to display the spreadsheet in both versions of Excel: Look at: File, Options, Save Here you will find the section entitled: Preserve visual appearance of the Workbook Cheers

Oops - major typo in formula!! Try SUM(IF(B1:H1=B52:H52,1,0)) Sorry!

Use an Array Formula: =SUM(B1:H1=B52:H52,1,0) To enter an array formula press Shift+Ctrl+Enter in place of the normal Enter key Formula bar will show {=SUM(B1:H1=B52:H52,1,0)} - typing the brackets will not work Array formulas allow you to perform multiple calculations - i.e. compare...

You may have manual recalculation turned on If so pressing F9 will force a manual recalculation and should give the correct results. Unless there is a good reason for manual calculation you can turn on automatic recalculation using the Calculation Options in the Formulas tab in the Ribbon. Cheers

Assuming your data starts in row 2. Select cells in column V from V2 onwards Set conditional formula of: =X2 Excel will automatically adjust the cell reference for each cell in the selection. This formula is in itself a condition - provided the data stored in column X are logical values -...

Select region at active cell expands current selection to select a range bordered on all sides with blank rows/columns - or edge of spreadsheet. This will work with a single filled cell. Keyboard: Ctrl * However if you use the * on the main keyboard you may need to use: keyboard: Shift...

A more advanced formula which will detect if the division is possible or not would be: =IF(ISERROR(B2/$I$12),"",B2/$I$12) This detects if the division creates an error, if it does it leaves the cell blank, if not it performs the calculation. You could replace "" with a zero, or enter an error...

Use: =B2/$I$12 in F2 then copy down column. the Sum function is not required for simple arithmetic. The $ symbols create an Absolute cell reference which is not changed when the formula is copied/filled to other cells. cheers

When activating worksheet protection select: Use AutoFilter in the Sheet Protection dialog box cheers

Try: Menu Bar: View, Status Bar On/off toggle for the page selection symbols at foot of screen. Cheers

Try: =RIGHT(A2,LEN(A2)-FIND("-",A2)) This will return all the characters after the first - character. cheers