Today in Episode #1547, Bill creates a special section in the worksheet to set up a means to move through the individual records in the Database more effectively. Part II of IV.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1547, Get Next Record.
Well, hey. Welcome back to another MrExcel netcast. I’m Bill Jelen.
All this week, we’re talking about how this problem that Mike sent.
He has a database.
He gets this data every day, pastes the data in, and then needs to print one report per record.
And yesterday, on episode 1546, we set up these formulas but then discovered that as we delete row two, all the formulas change into reference error.
So, I’m going to do CTRL+Z to get that back.
Here’s my solution, first solution.
For this, I’m going to insert a whole bunch of rows above where we’re going to paste the data and I’m going to copy the headings up to this special section.
And because I want to make the formulas come with the data, I’m going to take this data.
I’m going to cut, CTRL + X, and then, paste, CTRL + V. And what that should do is that it should force us to have our formulas now pointed in this row 5, special row, instead of down here into the database.
Now, that I’ve done that, I need to get that data back into the database.
I’m going to copy and paste.
So, the cut is a way to have the formulas follow along.
Could we have just taken the data that was previously in row two.
Cut it and paste it to row 3 to have the formulas go.
Yes, but then, we would have the wrong data there that would have been just a mess.
Alright.
What I need to do is I need to tell Excel that we want to have the data in special row 5 come from a different place.
One way I could do is just point to the first row, copy that across, print this first record, and then, point to the next row.
However, if I’m going to do this every single day, this could become a very tedious thing.
So, I’m going to do a little bit of extra work.
I’m going to say record number.
And I’m going to ask for a second record.
Counting the headings is the first record.
I’m never actually going to print that one.
And right here, I’m going to build a formula that says =INDEX.
The INDEX function is kind of a rare function of all of these answers down here, A8 to A19.
And then, which row we want.
I want the second row.
But, I’m not going to type the two.
I’m going to click on the two, press the F4 key that puts the dollar signs in that.
We’re always pointing to that record number, =INDEX(A8:A19,$B$1).
And as I copied that across, I’m now set up a cool formula that will point to a different row depending on what record number I do so.
Imagine this.
I come in.
I paste the data to this data.
I put the number two here.
And then prints this printed report.
I, then, changed the two to a three.
And I’m now able to print the data from the second record.
Print that one.
Change it to a four.
Click print.
Change to a five, print, six, print, seven, print, eight, print, and so on, right down through the list.
So, this extra row here-- this row, I’m going to kind of set up once and just keep there all the time.
It will allow us to navigate down through this pasted data and move the next record into our report.
Okay, I want to thank you from stopping by.
See you next time for another netcast of MrExcel.
Learn Excel from MrExcel podcast episode 1547, Get Next Record.
Well, hey. Welcome back to another MrExcel netcast. I’m Bill Jelen.
All this week, we’re talking about how this problem that Mike sent.
He has a database.
He gets this data every day, pastes the data in, and then needs to print one report per record.
And yesterday, on episode 1546, we set up these formulas but then discovered that as we delete row two, all the formulas change into reference error.
So, I’m going to do CTRL+Z to get that back.
Here’s my solution, first solution.
For this, I’m going to insert a whole bunch of rows above where we’re going to paste the data and I’m going to copy the headings up to this special section.
And because I want to make the formulas come with the data, I’m going to take this data.
I’m going to cut, CTRL + X, and then, paste, CTRL + V. And what that should do is that it should force us to have our formulas now pointed in this row 5, special row, instead of down here into the database.
Now, that I’ve done that, I need to get that data back into the database.
I’m going to copy and paste.
So, the cut is a way to have the formulas follow along.
Could we have just taken the data that was previously in row two.
Cut it and paste it to row 3 to have the formulas go.
Yes, but then, we would have the wrong data there that would have been just a mess.
Alright.
What I need to do is I need to tell Excel that we want to have the data in special row 5 come from a different place.
One way I could do is just point to the first row, copy that across, print this first record, and then, point to the next row.
However, if I’m going to do this every single day, this could become a very tedious thing.
So, I’m going to do a little bit of extra work.
I’m going to say record number.
And I’m going to ask for a second record.
Counting the headings is the first record.
I’m never actually going to print that one.
And right here, I’m going to build a formula that says =INDEX.
The INDEX function is kind of a rare function of all of these answers down here, A8 to A19.
And then, which row we want.
I want the second row.
But, I’m not going to type the two.
I’m going to click on the two, press the F4 key that puts the dollar signs in that.
We’re always pointing to that record number, =INDEX(A8:A19,$B$1).
And as I copied that across, I’m now set up a cool formula that will point to a different row depending on what record number I do so.
Imagine this.
I come in.
I paste the data to this data.
I put the number two here.
And then prints this printed report.
I, then, changed the two to a three.
And I’m now able to print the data from the second record.
Print that one.
Change it to a four.
Click print.
Change to a five, print, six, print, seven, print, eight, print, and so on, right down through the list.
So, this extra row here-- this row, I’m going to kind of set up once and just keep there all the time.
It will allow us to navigate down through this pasted data and move the next record into our report.
Okay, I want to thank you from stopping by.
See you next time for another netcast of MrExcel.
