Applications Archives - Pat Howe's Blog

Using Excel to calculate league position

Last year, I started writing an article on using Excel for a football league to work out the positions in the league table based on the results. I never finished it but then during the current pandemic and the uncertainty over completing the current football seasons I started playing with Excel again. So, this is not really a technology article, although in the Excel examples, I have used some useful Excel functions. This is also not an important article considering the global crisis surrounding the COVID-19 pandemic. Those of us

Reverse engineering and generating databases in Visio

This article shows you how to connect to a database and populate a diagram with its tables, relationships and properties. For this I will use an Access database but it could be any data source where you have its driver installed. I want to represent a relational database structure on a diagram but I want it to show primary and foreign keys, unique indexes and the relationship structure between tables. I also want it to contain any parameters, constraints or other logic associated with the tables. I also want to

My Web Minder email changes

This is a message for My Web Minder hosting customers that are using the mailbox feature of their accounts. A security update is taking place right now on all mail servers to help prevent non-legitimate accounts (spammers) from using these servers. It started yesterday and will continue all of the week until next weekend. The default port for SMTP (simple mail transfer protocol), i.e. outgoing mail traffic is being switched off. Some mail clients already try a different port first but then settle for 25. A mail client is the

Hidden advanced options in Microsoft Outlook

Since Microsoft released the 2016 edition of its Outlook product, setting up a new email address has become very easy. Unless of course, you need to access the advanced options for a custom set-up. All is not lost, however, as you can still get to the advanced options just not through Microsoft Outlook. I don’t know who decided to drop it, but I find this sort of feature removal a reason for not upgrading. I suppose, Microsoft Office had become so good by 2003, that they had to start tinkering.

Using a simple organisational chart in Microsoft Visio

I have been using Visio for many years, going right back to the time before it was part of the Microsoft Office suite. Each year, I upgraded and features have come and gone. Sometimes I can see an improvement but usually I long for the features that have been dropped. My favourite version I think was the one that came with top level of Visual Studio 2003, I think it was called Visio 2003 for enterprise architects. That had a neat database feature where you could generate your database SQL

Negatives and switching image colours in Photoshop

I quite often forget how to switch between a negative and positive image or how to do an exact colour replacement when using Adobe Photoshop. Each time, I need to, which is not very often, I spend a bit of time working it out. As this has just happened to me, I thought I would make a note of the steps and make that into an article. I needed to switch colours in a 2-colour logo to from blue on a white background to white on a blue background. This

Consolidating many Excel sheets into one master sheet

A while back we had a situation where we had collected a set of information from various sources and wanted to get all of it into the same Excel Worksheet and I was asked if I could help. There were about 60 spreadsheets all with the same columns but with a variable number of rows and of course, different data. The requester had linked all 60-odd spreadsheets to a master Workbook and wanted to know how to get the information from each transferred to a single master sheet that he

Using Excel’s Paste Special Operations

In this article, I broadly cover Microsoft Excel’s Paste Special functions with an example using the Operations of Paste Special. Before we get to an example some background is needed. Each cell in Microsoft Excel is made up of three parts; values (data), formula and format. Copying and pasting from one cell (or range of cells) to another copies all three exactly as they are. The formula remains the same (but relative to the new cell position), the values/data remains the same (unless it is formula driven) and the format

Convert or flip data between rows and columns in Excel

One of the things that I have to do periodically and always forget how to do is converting rows of data to columns or columns of data to rows. This quite often occurs when I’m collecting data from different sources and want to put them all in the same format in Microsoft Excel. From the 2007 version of MS Excel (I think), there has been a neat feature to enable you to do this. The reason that I always forget how to convert from columns of data to rows and

Applying conditions to AVERAGE, SUM and COUNT functions in Microsoft Excel

This is my first article on Microsoft Excel in over two years and continues the series with descriptions and examples of applying conditions to the popular Excel SUM, COUNT and AVERAGE functions. I started writing this article about 6 months ago when one of my friends asked me how to count the number of occurrences in a spreadsheet where a condition in another column was true. I had thought that I had already written such an article and was about to point her to it but it wasn’t there. I

« Previous

Cookie notice