Here we have a company that's looking to summarize its data. It's got three divisions, North, East and West and it's looking to pull the totals from each of those regions. We can see initially that if North has 12, East says 11 and West has 13, it's going to be 36. The first way we can do this is summing through the sheets. So I type in equals sum, open brackets and I now go to the first sheet and I click on the 12. Here's the important bit though. I now press and hold the shift key and I now click on the West tab. If we notice what happens to the formula at the top of the page, it now says North colon West, exclamation mark C11. That is first going to take C11 from North and West and any tabs in between. So if I close the brackets, it gives me 36. So that's a great and very quick shortcut. Assuming we don't insert any rows in North, East or West. Imagine I'm working on East, I insert a row between A and B. What's gonna happen now is that my 36 has become a 26. Unfortunately, what's happening is that on the East, it's not picking up the 11 in C12, it's picking up the one in C11. So let me undo that extra line.

36, great. Summing through the sheets is immensely useful but not if we're going to insert lines like we just did. So what I need is some integrity checks to make sure I'm not inserting lines. What I've got here is a column for each of my North, East and West, and I'm now going to look at sales B seven through to B10. If we go to North B seven, these are the letters. A, B, C, and D. I want the letters A, B, C, and D to appear here. So initially I think I can do this quickly. I can go to the North tab, go to the A, I can copy that down. Brilliant. I go to the East tab, same thing again, get to the A, copy it down, and lastly, go to the West tab.

So I'm thinking that's great, and if I insert a line in East, then it'll show me an error. Unfortunately, Excel is too clever for that. If I insert a row, all Excel does is it magically updates where my cells have come from. So North they've come from B seven, B eight, B nine, B10. But on East they've come from B seven, B nine. Ah, so Excel has already inserted the cell for me. What I need to do instead is include an indirect integrity check. My indirect check uses the indirect formula and I'm gonna tell it to go looking for cell C19 and I'm going to lock onto row 19.

I then want to link that to an exclamation mark so it's now the North tab and I link that to what I've got in cell B20, which is B seven. Now I want to lock onto column B, so I press once and twice, three times. And that now brings through the A, B, C, and D. If I copy this down to the right, I hope to see A, B, C, D in each column and we can see it in the East column, something has gone wrong. I can see I've got an A, I've got a blank, and then I've got a B and a C. And if I investigate what's happened there on the east, ah, it's that empty row. Fantastic. That tells me that something's gone wrong. I can highlight the row, I can delete it. If we go back to our summary tab, my integrity checks have now corrected themselves. Fantastic. Really useful as an integrity check for rows all being the same, and columns all being the same.