11. Updating Formulas with New Data Part 2
To finish off the course, we update our search panel formulas to automatically include any new company records added to our sales dataset.
Dynamic ranges for updating formulas part 2
- If you know the dataset will change over time, create named ranges for each column with OFFSET included
- Put the OFFSET formula in your named range to keep formulas readable
- Updating your named ranges with OFFSET may result in some temporary errors until all the updated named ranges are complete
CTRL + F3: Open the Name Manager dialog box
ALT + N: Create a new Name
F2: Jump inside formula
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select additional cell / character
CTRL + →: Move to the last cell in the data region
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
In the last lesson we learned how to automatically extend our formulas to include new data. Using the offset command, we can now add July to this list, give it a revenue, and then perform a lookup that will automatically include July.
Let's now apply this functionality to our sales data set.
I've added a new transaction for the company Open Roads, which is the largest transaction in the data set, and assigned to the sales person Baines, but unfortunately it’s not appearing in our search panel.
So as we saw in the previous lesson, the best way to add offsets to a data set is using named ranges, because it keeps the formula much more readable in our search panel.
Starting with the sales rep panel, we'll need to make changes to the revenues range, the sales people range, and the company name.
So we'll press Ctrl + F3 to bring up the Name Manager box, and we’ll start with the named range revenues.
I'll press F2 to enter the Refers to box, and I'll move the Name Manager just to the right so you can see the column in question.
I'll now update the formula with “=offset” and I'll first select the reference cell, which will simply be the revenue header.
Next I'll select the position of the array, which is one row down and zero columns across.
I'll then enter the height of the array, which will be COUNTA, the full C column, and then minus one to remove the header.
And the array will be one column wide, and we’ll close the bracket and finish.
Let's now update the sales people range.
So again I'll press F2, and I'll write “=offset”, and then select the reference.
Again, I'll just move the Name Manager box to the left so you can see the column in question.
And the reference will be the sales person header.
Next I'll put in the position of the array, which is one row down and zero columns across.
Again, I’ll use COUNTA to count the height of the array, and we’ll simply select the sales person column and we'll take one away.
And finally, the array will be one column wide.
So we’ll close the bracket, press enter and ESC.
And now we can update two of the calculations in our sales rep panel.
I'll press F2 to enter the formula, and replace the existing array with sales people.
And the number of customers has now been increased from 11 to 12.
Let's also make this change for total sales.
Press F2 to enter the formula, and the first array will be replaced with revenues.
And the second array with sales people.
And the total sales has increased accordingly.
Now let's make the change for our top 5 customers.
Again, we enter the formula with F2, the first array is replaced with revenues, second array with sales people, press Ctrl + Shift + Enter because this is an array formula.
We then copy this formula for the remaining 4 cells.
Unfortunately, this has caused errors in our top 5 customers, so we’ll need to change that formula as well to fix this.
So we must first update the company’s name, named range, so I'll press F2 to enter the Refers to for this formula, “=offset”, again the reference will simply be company name, the array is one row and zero columns away.
COUNTA, the whole column, minus one and it’s one column wide.
And then press Enter and Esc to finish.
You can now see that the top 5 customers have been updated and Open Roads is ranked number one, which proves the formula is calculated correctly.
What's more, if I add another row to my data set off camera, and then return to our search panel, this new company is now also included in our formulas.
You might have noticed that an N/A error appears in the State panel when we updated our arrays.
And this often happens when you have offset and named arrays combined with standard arrays.
If you have data sets that you know will need to be updated in the future, the best policy is often to create named arrays with offsets for all columns in the data set, before you start building your formulas.
Anyhow, let's quickly fix this by creating a new named array for the State column So I'll press Ctrl + F3 to bring up the Name Manager, and Alt + N for a new name.
I'll call this STATES, and I'll go to the Refers to box and create an offset formula.
My reference as always will be the header, the position is one row down and zero columns across.
And the height of the array is COUNTA, select the full column, close the bracket, minus one, and the array will be one column wide.
Press Enter and now we have our new name in place.
We now need to put this new name into our formula.
So I'll press F2, and then in this first array we’ll replace it with States.
Ctrl + Shift + Enter and we’ve now fixed the error.
Unfortunately, the formula is still referencing the wrong top customer.
I’d like this to be Costa Airways.
So we’re going to need to update some of the other formulas in the State panel as well.
Let's start with this number.
So I'll enter the formula and I'll replace this with revenues.
I'll replace the next array with States.
Ctrl + Shift + Enter to finish, and now we are referencing the right top customer.
I'll quickly update the total sales and the number of customers for this panel as well.
So again, I’ll enter the formula, change this to Revenues, and change the next array to States.
And I'll do it one more time, placing this array with States.
And now this panel is completed.
As an exercise, try to replace the arrays in the sales by month and sales by day of the week panels with offset, testing with the April month which is when the Open Roads transaction occurs.
With this course on lookups and database functions now complete, you should be more than ready to move onto more complex analysis of data, safe in your ability to pull any information you need from the records in your data set.