08 6210 8500
Pay my invoice
Solve Leading Apostrophe Problems
Access Analytic Knowledgebase Articles, Case Studies, White Papers, Success Stories and more
List Of Sheets With Hyperlinks
List of sheets with hyperlinks I regularly create a list of sheets in a contents sheet in my files. I then create hyperlinks to the sheets. Can this process be automated? By Neale Blackwood The macro below will create a list of all sheets in the current workbook. This list will start at the [...]
SUM Function To Handle Inserted Rows And Columns
SUM function to handle inserted rows and columns When I insert a row between the data and the SUM function Excel will usually amend the SUM function to include the new row. Sometimes, however, it doesn't do it. Is there a way to automatically include the row above the cell with the SUM function? [...]
Merged Cells Alternative
Merged cells alternative When copying and pasting cells, I sometimes get an error message that says: By Neale Blackwood Merged cells can cause difficulties when using Excel. There is a standard tool on the toolbar called "Merge and Center". Many people use this tool to format reports, as it "merges" two or more cells [...]
Print Title Tips
Print Title Tips How do I set up titles when printing in Excel? I can see the area I need to change but it is 'greyed out' in the Sheet tab of the Setup section of Print Preview? By Neale Blackwood This is a common problem in Excel. To put titles ie; Rows to [...]
Array Formula To SUM Rounded Values
Array formula to SUM rounded values I format many of my reports to zero decimal places. This means that in some cases the SUM function will show a different total than the displayed numbers indicated. If I format two numbers 5.5 and 7.5 with zero decimal places, they will display as 6 and 8 [...]
Refering To Cells Within A Range Name
Refering to cells within a range name I use range names for rows. How do I reference a specific cell in a named row? By Neale Blackwood When you have a range name that refers to a whole row, its value will depend on where you use the name. Assume row 2 is name [...]
Paste Linking
Paste Linking I use Paste Link frequently. Is there a quick way to access it instead of going through Paste Special each time? By Neale Blackwood Unfortunately Paste Link doesn't have a toolbar icon. However, when you drag a selection with the right mouse button there is a "Link here" option, which is the [...]
Stop Zeroes Displaying
Stop Zeroes Displaying Is it possible to stop zero values displaying in a range? By Neale Blackwood That can be achieved using a Custom Number format. First select the range, then click the Format menu and select Cells (Ctrl + 1 will open the Format Cells dialog box). Click the Number tab and select [...]
Full Path Name In Footer
Full Path name in Footer How can I put the full path name of my file in the footer without typing it? By Neale Blackwood Excel XP and 2003 allow you to easily insert the full path in your footer using the Page Setup, but unfortunately earlier versions don't. In Excel 97 and 2000 [...]
View Values And Formula At The Same Time
View Values and Formula at the same time Is it possible to view cells' values and formulae simultaneously? By Neale Blackwood, 1 Dec 08 There is a technique that lets you see the formula and the value at the same time. This is often useful when creating or debugging a spreadsheet. Open a file [...]
Multiply A Range Of Cells By A Factor
Access Analytic Knowledgebase Articles, Case Studies, White Papers, Success Stories and more
