Search Not Just Numbers

Loading...

Tuesday, 14 October 2014

Excel Tip: Using an Override List to Tweak Imported Data

This week's post is not so much learning a new Excel function, as learning a concept that you can use to enable you to import data and correct it, without overwriting the imported data. This is important, so that you can refresh the imported data later!

Before we get into that though, just a quick reminder that the 20% discount offer on Mynda Treacy's Excel Dashboards course expires on Thursday, so if you're interested you need to get over there now.

Excel is a great tool for reporting on data held in an external database (this could be anything, such as an accounting system, ERP/MRP software or maybe a CRM system). However, sometimes we need the ability to edit or override the imported data with some tweaks that we wish to see included in the final reports.

In most cases, I would encourage any changes to be made in the external database, then the data to be refreshed, however this is not always possible. This can particularly be the case with transactional data (such as orders, invoices, etc.). Once an order is complete, most systems won't allow you to edit it, so if you need the order to be reflected differently in the final reports, this will need to be done in the spreadsheet.

You could over-type the imported data to make the changes, but this would be overwritten next time you refreshed the link to the external data. Far better to hold the changes in the spreadsheet to be applied to the data as it comes in. This is where I use what I call an override list.

As long as the imported data has a unique reference that you can link the adjustments to, you can use this approach. Simply have a separate sheet in the spreadsheet where you enter the references of any records that you want amended alongside the amendments. You can look up these amendments  in calculated columns alongside the data.

Say that you are importing a list of sales orders but that they are not always allocated to the correct salesperson in the external database. You could have an override list with the following two columns:

  • Sales Order No
  • Salesperson
Then for any orders that you needed to correct, you could type that order number and the salesperson that you wish to be allocated to that order.

Then all you need is a calculated column alongside the imported data that uses the new value if the Sales Order in question is on the list or the original salesperson if not.

Assuming that the imported data is in a table where two of the headings are Sales Order No and Salesperson, and the override list as described above is in the first two columns of a sheet called Override, then the following formula will work in the calculated column (which we might call CalcSalesperson:

=IF(COUNTIF(Override!$A:$A,[@[Sales Order No]])=0,[@Salesperson],VLOOKUP([@[Sales Order No]],Override!$A:$B,2,FALSE))

This uses an IF statement to check whether this row's Sales Order No appears in the first column of the Override sheet (The COUNTIF counts the occurrences of the Sales Order No in that column). If this returns zero, then the Salesperson field is used, otherwise the amended Salesperson is looked up (using VLOOKUP) from the override sheet.


Any references to the data (in PivotTables or SUMIF formulae for example) can now refer to CalcSalesperson, rather than Salesperson.

The specific formula will vary depending on what you are trying to adjust, but the principle remains the same - and the changes will be applied every time that particular Sales Order No appears in the imported data.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

5 comments:

  1. An ingenious solution as always, Glen. Thanks for sharing.
    A related technique that I use a lot involves refreshing the Data from the External Source, then Deleting the Data Connections, and finally saving the Workbook with a timestamp in the name, such as "sales_141024.xlsx", for example.

    ReplyDelete
  2. To reduce the particular workload, you'll have a quick minimize and also submit the issue to be able to on the web purchasing community forum or perhaps sites. fake chanel coco You'll definitely get the response super fast. Studying posts concerning low-cost clutches at the same time, many modest companies market their particular on the web store in this way. fake cartier You can not basically count on reliable sites just like craigs list, nearly all of their particular items are usually costly and will be offering constrained lower price at the same time. You can even make an effort to sign up for your online purchasing internet site and also programmed acquire bargains or perhaps special discounts if they have got advertising bargains to promote. dior replica One more means of having the traditional low-cost clutches will be simply by replacing. prada replica Sure, it is possible to try this on the web, in the event you very own traditional designer purse and also would like a fresh a single regarding modify, this could really do the proper alternative to suit your needs. prada replica Just before we all move deeper with all the suggestions regarding the way to acquire low-cost clutches, there is certainly preventive determine that you must absorb just before totally immersing oneself directly into on the web purchasing.

    ReplyDelete
  3. The studies support this as well gucci replica. Laughter truly is the best medicine, providing a boost to the immune system andcardiovascular system, replica louis vuitton and zapping stress. Laughter has been shown to improve the specific cells of the immune system involved in battling bugs and cancer louis vuitton replica. There is also a relaxation of the blood vessels that occurs with laughter that can help to dior replica lower blood pressure. An added benefit iwc replica? Laughter, including fake laughter, has been shown to burn calories similar to some forms ubout replica of cardiovascular exercise.

    ReplyDelete
  4. Ambani is yet another Indian who has been replica watches diligently growing his inherited fortune, this time in the petrochemical sector. He elevated his fortune by almost $10 billion in 2009, catapulting him from #7 to #4.Nonetheless you have many replica tag heuer watches choices obtainable. Nevertheless as a woman it's very challenging to decide on the right handbag suppose you have tons of preferences at hand. Assuming that you are a bride and considering to consider a purse then make certain it complements your shoes and the flower bouquet you have.He sure hit that description of Lawrence on the head. Lawrence is fierce, stunning and on fire. Congratulations, Jennifer! Cannot wait to see a lot more of your campaign.Make sure you choose and use doll clothing patterns that incorporate fake watches uk, shoes, hats and jewellery to develop that 'total search.' When most men and women believe about creating doll garments, they don't think about the impact of swiss fake watches equipment. It's often the decision of doll equipment that completes the outfit... or makes it ordinary. That's why we integrated these have to-have accessories on our 5 Prime Trends listing.Secondly, the great track record. Christian Dior is popular all in excess of the globe. Needless to say, the handbags of Dior are also fabulous. And also Dior handbags ae trendy. The patterns and designs are usually leading the newest trend. It's greatest identified as an practically iconic form of standing and a symbol for the extremely wealthy and trendy.

    ReplyDelete
  5. I'm do not know how to sew, but read your article
    ---------
    Signature:
    games for kids , jogos do friv ,
    jogos de friv , juegos de autos , baixar whatsapp

    ReplyDelete