From the Not Just Numbers blog:

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”.