Julian wants to point to the row above the current row. This formula works fine until he deletes a row then those formulas change to #REF! errors. Julian asks how to point to "the row above" without specifying a row number. Episode 1061 shows you how.
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
A great question today sent in by Julian, now Julian is from Australia but he's currently working in Mexico and he does event planning.
So you know we have something happens for some number of minutes and then something else happens for some number of minutes, so this nice little spreadsheet here to keep track of the time for the next event so the time for the next event is the time for the previous event plus the duration of the previous event and Julian says the thing that drives him crazy is that if he deletes a row, so edit, delete row, then everything changes to a reference airing let's know how he can build a formula that says; point to the cell above me without specifically referring to that cell so that way if you delete something it doesn't go away.
Before I get to that I first of all want to point out this time here and I was like well that can't possibly be a valid time but if we go into control 1, format cells, you'll see that Julian is using a custom number format with MIN in quotes that way the people who use this understand that those are minutes, very cool, OK.
No where's my solution I'm not going to point to those cells I'm going to use a function called offset, now offset is a cool function that says; hey we're going to start from a certain cell and I'm going to start from the cell that I'm in, I'm going to start from cell A3 and then we're going to go some number of rows down why don't want to go any number of rows down, I want to go minus one row in other words one row up and zero columns over and then how tall of a reference do we want when we want one row and in this case then I want two columns, two columns, now the offset here is going to return in this case two cells, so I want to take those two cells and I want to sum them, so I want to wrap that offset function and some open parenthesis close parenthesis and there's our answer will copy that down now here's the big test; let's say that we get rid of something so the second guy cancels when we do edit, delete row, everything above continues to work because what it does is it says we're going to take the item directly above us well even though we deleted the old row 6, it now understands the item directly above us, -1 row in the offset is now real five so it works perfectly.
Thanks to Julian for sending that question in and thanks to you for stopping by; we'll see you next time for another netcast from MrExcel.
A great question today sent in by Julian, now Julian is from Australia but he's currently working in Mexico and he does event planning.
So you know we have something happens for some number of minutes and then something else happens for some number of minutes, so this nice little spreadsheet here to keep track of the time for the next event so the time for the next event is the time for the previous event plus the duration of the previous event and Julian says the thing that drives him crazy is that if he deletes a row, so edit, delete row, then everything changes to a reference airing let's know how he can build a formula that says; point to the cell above me without specifically referring to that cell so that way if you delete something it doesn't go away.
Before I get to that I first of all want to point out this time here and I was like well that can't possibly be a valid time but if we go into control 1, format cells, you'll see that Julian is using a custom number format with MIN in quotes that way the people who use this understand that those are minutes, very cool, OK.
No where's my solution I'm not going to point to those cells I'm going to use a function called offset, now offset is a cool function that says; hey we're going to start from a certain cell and I'm going to start from the cell that I'm in, I'm going to start from cell A3 and then we're going to go some number of rows down why don't want to go any number of rows down, I want to go minus one row in other words one row up and zero columns over and then how tall of a reference do we want when we want one row and in this case then I want two columns, two columns, now the offset here is going to return in this case two cells, so I want to take those two cells and I want to sum them, so I want to wrap that offset function and some open parenthesis close parenthesis and there's our answer will copy that down now here's the big test; let's say that we get rid of something so the second guy cancels when we do edit, delete row, everything above continues to work because what it does is it says we're going to take the item directly above us well even though we deleted the old row 6, it now understands the item directly above us, -1 row in the offset is now real five so it works perfectly.
Thanks to Julian for sending that question in and thanks to you for stopping by; we'll see you next time for another netcast from MrExcel.
