Short takes: Excel tips to display the worksheet name and validate data; editing Microsoft Project files stored on SharePoint; and an XPS to PDF conversion service

Posted on By Mark Wilson
This content is 9 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Another collection of mini-posts based on recent IT trials and tribulations…

Excel tips to display the worksheet name in a cell and to validate data

Last week, I was working on an Excel spreadsheet that acts as a plan for a series of tests. Each sheet has the same format, with some conditional formatting and associated logic to total up passes/fails and give a RAG score for the sheet. Those RAG scores are presented in an overview page – and data is copied between cells so that information is only populated once but appears on every sheet.  I’m quite pleased with the result but I did need to work a little on some of the tricks.

Firstly, data validation in lists (for the pass/fail). This is fairly straightforward but I usually forget how to do it so it’s worth reading the TechNet Productivity Hub post on restricting data entry in Excel with lists.

The second trick was to read the name of each worksheet and use that information in a cell (so I could name a worksheet after a set of tests, and see that name displayed as a header on the page too). Here, the SuperUser site came to the rescue and the code I needed in the cell was:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

Incidentally, I also needed to look something up that I’ve blogged about previously: if a cell shows the formula rather than the result, check the formatting is General and not Text.

Editing Microsoft Project files stored on SharePoint

Much as I try, it seems I can’t avoid working with Microsoft Project. Unfortunately, when working directly from SharePoint the files are opened read-only. The answer, it seems, is to work on a synchronised local copy – as described by Victor Butuza on his Microsoft Office SharePoint blog.

XPS to PDF conversion web service

Every now and again, I find myself wanting to create a PDF from an email, just to upload a receipt to Xero (the expenses system I use at work). Unfortunately Xero isn’t happy with XPS files – and Windows 8.1/Outlook don’t create PDFs, but a quick Internet search turned up XPS2PDF, a simple, fast and apparently secure way to convert my files.  There’s an API for those who want to make the conversion programmatically too.

Using the Microsoft Project calendar to block out time when people are not available

Posted on By Mark Wilson
This content is 9 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

I hate Microsoft Project.

I mean, as a tool it’s OK, but it’s idiosyncratic and time-consuming to use; and even copying/pasting information is not as straightforward as it should be. Besides which, far too many people confuse a Gantt Chart with a project plan… and I blame Microsoft Project for that…

When I was at Fujitsu, I avoided having Project on my PC. If I didn’t have a license, I couldn’t edit plans… I could only view them. Unfortunately I can’t get away with that any more and, tonight, I lost most of the evening to some edits that went wrong with tasks getting split across days (I think I changed the working hours to reflect the hours we really work… but that messed something else up).

Anyway, I digress. Something I did find this evening was a really useful article describing how to change the working days for a Microsoft Project calendar. Using this I could not only add bank holidays that were missing in the standard calendar, but add the days that I’m not available to work on the project – for example because of annual leave, or other client commitments – so that the plan couldn’t allocate tasks to me on days I’m not booked to that customer.  You can also edit dates that people are available to work on a project directly (I don’t like referring to people as “resources”) but that doesn’t take into account odd days here and there of non-available time.

Next time though, I’ll leave editing the plan to the Engagement Managers…

Viewer for Microsoft Project (.MPP) files

Posted on By Mark Wilson
This content is 19 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Last week I needed to view a colleague’s project plan (in Microsoft Project) to make sure I hadn’t been stitched up ensure that all the activities had been captured in the correct sequence and within a realistic time frame. Because I didn’t have Microsoft Project installed on my PC, I had to go through the correct processes to get a licence allocated and the software installed.

I could have just got the CD out of my drawer and installed an illegal copy, but I was “being good” and my honesty cost my Manager’s budget £223.35 – and that’s with a heavy volume licence discount.

Literally two days after the software was installed, I attended an event where I was given a copy of Seavus Project Viewer. For anyone who’s not aware of this product’s existence (I wasn’t), it is an application which allows Microsoft Project (.MPP) files to be viewed by users who don’t have a copy of Microsoft Project installed. At only $39, this would have been substantially more cost-effective than licensing Microsoft Project so I thought I’d blog about it and save someone else from spending the money if they only need read-only access to project plans.