{"id":5805,"date":"2015-08-17T12:00:33","date_gmt":"2015-08-17T11:00:33","guid":{"rendered":"http:\/\/www.markwilson.co.uk\/blog\/?p=5805"},"modified":"2015-08-17T11:25:04","modified_gmt":"2015-08-17T10:25:04","slug":"short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service","status":"publish","type":"post","link":"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm","title":{"rendered":"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"},"content":{"rendered":"<p>Another collection of mini-posts based on recent IT trials and tribulations&#8230;<\/p>\n<h3>Excel tips to display the worksheet name in a cell\u00a0and to validate data<\/h3>\n<p>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 &#8211; and data is copied between cells so that information is only populated once but appears on every sheet.\u00a0 I&#8217;m quite pleased with the result but I did need to work a little on some of the tricks.<\/p>\n<p>Firstly, data validation in lists (for the pass\/fail). This is fairly straightforward but I usually forget how to do it so it&#8217;s worth reading the TechNet Productivity Hub post on <a href=\"http:\/\/blogs.technet.com\/b\/hub\/archive\/2011\/06\/09\/restrict-data-entry-in-excel-with-lists.aspx\">restricting data entry in Excel with lists<\/a>.<\/p>\n<p>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, <a href=\"http:\/\/superuser.com\/questions\/541766\/how-can-i-display-the-name-of-the-current-worksheet-in-a-cell\">the SuperUser site came to the rescue<\/a> and the code I needed in the cell was:<\/p>\n<p><code>=RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1),1))<\/code><\/p>\n<p>Incidentally, I also needed to look something up that I&#8217;ve blogged about previously: <a href=\"https:\/\/www.markwilson.co.uk\/blog\/2013\/01\/formulas-shown-in-excel-instead-of-the-results.htm\">if a cell shows the formula rather than the result, check the formatting is General and not Text<\/a>.<\/p>\n<h3>Editing Microsoft Project files stored on SharePoint<\/h3>\n<p><a href=\"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/using-the-microsoft-project-calendar-to-block-out-time-when-people-are-not-available.htm\">Much as I try<\/a>, it seems I can&#8217;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 &#8211; <a href=\"http:\/\/blogs.technet.com\/b\/victorbutuza\/archive\/2014\/07\/25\/mpp-files-stored-in-sharepoint-open-read-only-in-project-client.aspx\">as described by Victor Butuza on his Microsoft Office SharePoint blog<\/a>.<\/p>\n<h3>XPS to PDF conversion web service<\/h3>\n<p>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&#8217;t happy with XPS files &#8211; and Windows 8.1\/Outlook don&#8217;t create PDFs, but a quick Internet search turned up <a href=\"https:\/\/xps2pdf.co.uk\/\">XPS2PDF<\/a>, a simple, fast and apparently secure way to convert my files.\u00a0 There&#8217;s an API for those who want to make the conversion programmatically too.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Another collection of mini-posts based on recent IT trials and tribulations&#8230; Excel tips to display the worksheet name in a cell\u00a0and 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 &hellip; <a href=\"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">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<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[218],"tags":[73,188,130,16],"class_list":["post-5805","post","type-post","status-publish","format-standard","hentry","category-technology","tag-file-formats","tag-excel","tag-project","tag-useful-websites"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>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 - markwilson.it<\/title>\n<meta name=\"description\" content=\"Some Microsoft Excel tips; a Microsoft Project\/SharePoint tip; and a useful web service for XPS to PDF document conversion\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"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 - markwilson.it\" \/>\n<meta property=\"og:description\" content=\"Some Microsoft Excel tips; a Microsoft Project\/SharePoint tip; and a useful web service for XPS to PDF document conversion\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm\" \/>\n<meta property=\"og:site_name\" content=\"markwilson.it\" \/>\n<meta property=\"article:published_time\" content=\"2015-08-17T11:00:33+00:00\" \/>\n<meta name=\"author\" content=\"Mark Wilson\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@markwilsonit\" \/>\n<meta name=\"twitter:site\" content=\"@markwilsonit\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Mark Wilson\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/2015\\\/08\\\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/2015\\\/08\\\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm\"},\"author\":{\"name\":\"Mark Wilson\",\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/#\\\/schema\\\/person\\\/98f61365e7c39d6be942174b8c4de468\"},\"headline\":\"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\",\"datePublished\":\"2015-08-17T11:00:33+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/2015\\\/08\\\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm\"},\"wordCount\":414,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/#\\\/schema\\\/person\\\/98f61365e7c39d6be942174b8c4de468\"},\"keywords\":[\"File formats\",\"Microsoft Excel\",\"Microsoft Project\",\"Useful Websites\"],\"articleSection\":[\"Technology\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/2015\\\/08\\\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/2015\\\/08\\\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm\",\"url\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/2015\\\/08\\\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm\",\"name\":\"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 - markwilson.it\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/#website\"},\"datePublished\":\"2015-08-17T11:00:33+00:00\",\"description\":\"Some Microsoft Excel tips; a Microsoft Project\\\/SharePoint tip; and a useful web service for XPS to PDF document conversion\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/2015\\\/08\\\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/2015\\\/08\\\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/2015\\\/08\\\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"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\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/\",\"name\":\"markwilson.it\",\"description\":\"get-info -class technology | write-output &gt; \\\/dev\\\/web\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/#\\\/schema\\\/person\\\/98f61365e7c39d6be942174b8c4de468\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/#\\\/schema\\\/person\\\/98f61365e7c39d6be942174b8c4de468\",\"name\":\"Mark Wilson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/i0.wp.com\\\/www.markwilson.co.uk\\\/blog\\\/uploads\\\/image-4.png?fit=800%2C800&ssl=1\",\"url\":\"https:\\\/\\\/i0.wp.com\\\/www.markwilson.co.uk\\\/blog\\\/uploads\\\/image-4.png?fit=800%2C800&ssl=1\",\"contentUrl\":\"https:\\\/\\\/i0.wp.com\\\/www.markwilson.co.uk\\\/blog\\\/uploads\\\/image-4.png?fit=800%2C800&ssl=1\",\"width\":800,\"height\":800,\"caption\":\"Mark Wilson\"},\"logo\":{\"@id\":\"https:\\\/\\\/i0.wp.com\\\/www.markwilson.co.uk\\\/blog\\\/uploads\\\/image-4.png?fit=800%2C800&ssl=1\"},\"description\":\"A Chartered IT Professional, with recent experience in technology leadership, IT strategy and practice management roles, Mark Wilson is an Enterprise Architect in the Advisory and Management Group at risual. During a career spanning more than two decades, Mark has gained widespread recognition as an expert in his field including both industry and national press exposure. In addition to certifications from Microsoft, VMware, Red Hat, The Open Group and Axelos, Mark held a Microsoft Most Valuable Professional (MVP) award for three years and is now part of the MVP Reconnect programme. Mark is also well-known on social media and maintains an award-winning blog.\",\"sameAs\":[\"http:\\\/\\\/www.markwilson.co.uk\\\/\",\"https:\\\/\\\/www.instagram.com\\\/markwilsonuk\\\/\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/markawilson\\\/\",\"https:\\\/\\\/x.com\\\/markwilsonit\",\"https:\\\/\\\/www.youtube.com\\\/channel\\\/UCWHlZCoHRTocdvtrOJ2IL4A\"],\"url\":\"https:\\\/\\\/www.markwilson.co.uk\\\/blog\\\/author\\\/mark-wilson\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"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 - markwilson.it","description":"Some Microsoft Excel tips; a Microsoft Project\/SharePoint tip; and a useful web service for XPS to PDF document conversion","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm","og_locale":"en_GB","og_type":"article","og_title":"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 - markwilson.it","og_description":"Some Microsoft Excel tips; a Microsoft Project\/SharePoint tip; and a useful web service for XPS to PDF document conversion","og_url":"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm","og_site_name":"markwilson.it","article_published_time":"2015-08-17T11:00:33+00:00","author":"Mark Wilson","twitter_card":"summary_large_image","twitter_creator":"@markwilsonit","twitter_site":"@markwilsonit","twitter_misc":{"Written by":"Mark Wilson","Estimated reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm#article","isPartOf":{"@id":"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm"},"author":{"name":"Mark Wilson","@id":"https:\/\/www.markwilson.co.uk\/blog\/#\/schema\/person\/98f61365e7c39d6be942174b8c4de468"},"headline":"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","datePublished":"2015-08-17T11:00:33+00:00","mainEntityOfPage":{"@id":"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm"},"wordCount":414,"commentCount":0,"publisher":{"@id":"https:\/\/www.markwilson.co.uk\/blog\/#\/schema\/person\/98f61365e7c39d6be942174b8c4de468"},"keywords":["File formats","Microsoft Excel","Microsoft Project","Useful Websites"],"articleSection":["Technology"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm","url":"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm","name":"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 - markwilson.it","isPartOf":{"@id":"https:\/\/www.markwilson.co.uk\/blog\/#website"},"datePublished":"2015-08-17T11:00:33+00:00","description":"Some Microsoft Excel tips; a Microsoft Project\/SharePoint tip; and a useful web service for XPS to PDF document conversion","breadcrumb":{"@id":"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.markwilson.co.uk\/blog\/2015\/08\/short-takes-excel-tips-display-worksheet-name-validate-data-editing-microsoft-project-files-stored-sharepoint-xps-pdf-conversion-service.htm#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.markwilson.co.uk\/blog"},{"@type":"ListItem","position":2,"name":"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"}]},{"@type":"WebSite","@id":"https:\/\/www.markwilson.co.uk\/blog\/#website","url":"https:\/\/www.markwilson.co.uk\/blog\/","name":"markwilson.it","description":"get-info -class technology | write-output &gt; \/dev\/web","publisher":{"@id":"https:\/\/www.markwilson.co.uk\/blog\/#\/schema\/person\/98f61365e7c39d6be942174b8c4de468"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.markwilson.co.uk\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/www.markwilson.co.uk\/blog\/#\/schema\/person\/98f61365e7c39d6be942174b8c4de468","name":"Mark Wilson","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/image-4.png?fit=800%2C800&ssl=1","url":"https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/image-4.png?fit=800%2C800&ssl=1","contentUrl":"https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/image-4.png?fit=800%2C800&ssl=1","width":800,"height":800,"caption":"Mark Wilson"},"logo":{"@id":"https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/image-4.png?fit=800%2C800&ssl=1"},"description":"A Chartered IT Professional, with recent experience in technology leadership, IT strategy and practice management roles, Mark Wilson is an Enterprise Architect in the Advisory and Management Group at risual. During a career spanning more than two decades, Mark has gained widespread recognition as an expert in his field including both industry and national press exposure. In addition to certifications from Microsoft, VMware, Red Hat, The Open Group and Axelos, Mark held a Microsoft Most Valuable Professional (MVP) award for three years and is now part of the MVP Reconnect programme. Mark is also well-known on social media and maintains an award-winning blog.","sameAs":["http:\/\/www.markwilson.co.uk\/","https:\/\/www.instagram.com\/markwilsonuk\/","https:\/\/www.linkedin.com\/in\/markawilson\/","https:\/\/x.com\/markwilsonit","https:\/\/www.youtube.com\/channel\/UCWHlZCoHRTocdvtrOJ2IL4A"],"url":"https:\/\/www.markwilson.co.uk\/blog\/author\/mark-wilson"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":8502,"url":"https:\/\/www.markwilson.co.uk\/blog\/2023\/01\/formatting-cells-in-excel-if-they-match-a-value-in-another-column.htm","url_meta":{"origin":5805,"position":0},"title":"Formatting cells in Excel if they match a value in another column","author":"Mark Wilson","date":"Thursday 12 January 2023","format":false,"excerpt":"A few months ago, I wrote about using Excel to sort a table of names that had been created in Word. And last year, I mentioned a dynamic report I'd created with a colleague, stringing together a few formulae, some data validation and some conditionally formatted cells. Well, today, that\u2026","rel":"","context":"In &quot;Technology&quot;","block_context":{"text":"Technology","link":"https:\/\/www.markwilson.co.uk\/blog\/topic\/technology"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/Screenshot-2023-01-12-175952.png?fit=771%2C553&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/Screenshot-2023-01-12-175952.png?fit=771%2C553&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/Screenshot-2023-01-12-175952.png?fit=771%2C553&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/Screenshot-2023-01-12-175952.png?fit=771%2C553&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":1570,"url":"https:\/\/www.markwilson.co.uk\/blog\/2009\/12\/writing-a-macro-to-e-mail-a-worksheet-from-an-excel-workbook.htm","url_meta":{"origin":5805,"position":1},"title":"Writing a macro to e-mail a worksheet from an Excel workbook","author":"Mark Wilson","date":"Monday 14 December 2009","format":false,"excerpt":"I spent most of today trying to catch up with my expenses (in order to plug the rather large hole in my bank balance before Christmas).\u00a0 I work for a large IT company, and to say that our systems are antiquated would be an understatement: I understand that my expense\u2026","rel":"","context":"In &quot;Technology&quot;","block_context":{"text":"Technology","link":"https:\/\/www.markwilson.co.uk\/blog\/topic\/technology"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":8386,"url":"https:\/\/www.markwilson.co.uk\/blog\/2021\/11\/step-back-from-the-problem-and-think-about-what-good-looks-like.htm","url_meta":{"origin":5805,"position":2},"title":"Step back from the problem and think about what &#8220;good&#8221; looks like","author":"Mark Wilson","date":"Thursday 11 November 2021","format":false,"excerpt":"A few weeks ago, I sat down with a Chief Information Officer (CIO) who has a problem.\u00a0He's in the middle of a messy \"divorce\" (professionally, not personally). He is transitioning services from a shared services agreement with another public sector body to a new managed service. His own organisation's IT\u2026","rel":"","context":"In &quot;Business&quot;","block_context":{"text":"Business","link":"https:\/\/www.markwilson.co.uk\/blog\/topic\/business"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/20211110_205846-scaled.jpg?fit=1200%2C900&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/20211110_205846-scaled.jpg?fit=1200%2C900&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/20211110_205846-scaled.jpg?fit=1200%2C900&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/20211110_205846-scaled.jpg?fit=1200%2C900&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.markwilson.co.uk\/blog\/uploads\/20211110_205846-scaled.jpg?fit=1200%2C900&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":4965,"url":"https:\/\/www.markwilson.co.uk\/blog\/2013\/07\/business-intelligence-required.htm","url_meta":{"origin":5805,"position":3},"title":"Business intelligence required&#8230;","author":"Mark Wilson","date":"Tuesday 16 July 2013","format":false,"excerpt":"Up and down the country, businesses are running on Excel, instead of using a proper business intelligence (or even management information) system. The one I look after is no different but, as I pieced together yet another spreadsheet last weekend, I learned a few Excel tips that might be useful\u2026","rel":"","context":"In &quot;Technology&quot;","block_context":{"text":"Technology","link":"https:\/\/www.markwilson.co.uk\/blog\/topic\/technology"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":163,"url":"https:\/\/www.markwilson.co.uk\/blog\/2005\/09\/one-method-of-opening-strange.htm","url_meta":{"origin":5805,"position":4},"title":"One method of opening strange attachments from trusted sources","author":"Mark Wilson","date":"Sunday 11 September 2005","format":false,"excerpt":"Whilst I was on holiday last week, a professional photographer friend of ours sent me a list of gear that he is selling now that he has switched from Nikon to Canon (come on Nikon, can we have a full-frame image sensor in a digital SLR please...). Unfortunately, he is\u2026","rel":"","context":"In \"E-mail\"","block_context":{"text":"E-mail","link":"https:\/\/www.markwilson.co.uk\/blog\/tag\/e-mail"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":310,"url":"https:\/\/www.markwilson.co.uk\/blog\/2005\/11\/how-to-make-outlook-default-e-mail.htm","url_meta":{"origin":5805,"position":5},"title":"How to make Outlook the default e-mail program","author":"Mark Wilson","date":"Wednesday 30 November 2005","format":false,"excerpt":"Today, I finally got around to rebuilding the notebook PC that I use for e-mail, blogging, web site maintenance and general home IT (I keep all my digital media work on another, desktop, PC). I'm taking this opportunity to try out Mozilla Firefox 1.5 (released yesterday) and to give the\u2026","rel":"","context":"In \"E-mail\"","block_context":{"text":"E-mail","link":"https:\/\/www.markwilson.co.uk\/blog\/tag\/e-mail"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.markwilson.co.uk\/blog\/wp-json\/wp\/v2\/posts\/5805","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.markwilson.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.markwilson.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.markwilson.co.uk\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.markwilson.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=5805"}],"version-history":[{"count":4,"href":"https:\/\/www.markwilson.co.uk\/blog\/wp-json\/wp\/v2\/posts\/5805\/revisions"}],"predecessor-version":[{"id":5809,"href":"https:\/\/www.markwilson.co.uk\/blog\/wp-json\/wp\/v2\/posts\/5805\/revisions\/5809"}],"wp:attachment":[{"href":"https:\/\/www.markwilson.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=5805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.markwilson.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=5805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.markwilson.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=5805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}