Today Bill looks at using Data -from another Worksheet in the Current Workbook- using the =OFFSET Function to locate and retrieve the Data. Setting a starting point, each individual record in the Database can be printed - all without the use of a Macro. Follow along with Episode #1548 to see how it's done. This is Part III 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 1548, Get Next Record with Offset.
Hey, welcome back to MrExcel netcast.
I’m Bill Jelen.
All week, we’ve been talking about Mike’s problem where he gets data that he pasted into the data worksheet every day, and then needs to print one page per record.
And Monday and Tuesday, episode 1546 and 1547, we talked about a couple of ways to do this.
I’m going to talk about a third way.
I’m going to make a copy of this data worksheet.
Fast way to do that is hold down the CTRL key while I drag the tab to the right.
So, now I have data, and then report.
So, over here, on the report, I think this is where I’m actually going to build these formulas that point to the right spot.
So, I’m going to point all the extra rows here.
I just need to have one row that’s getting the data from the data worksheet.
And I’m going to enter couple of rows here where I specify the record number that I want.
So, record number, now, that means that, because I’m printing the printed report from back here.
Let’s see if we do CTRL + P. Yeah, it’s smart enough to know that print range still remained after I did that copy and paste.
Of course, it did one and I even have to check that.
We don’t need this print report down here anymore, edit, clear, all.
Alright.
Here’s a little bit of magic that we’re going to do to be able to grab data from the data worksheet and bring it over to the report worksheet.
I’m going to use a function called =OFFSET().
It has to start from a specific place.
And we’re always going to start from cell A1 on the data worksheet.
So, it’s coming =OFFSET(DATA!A1.
And I’m going to allow that to move as I copy across.
So, it’s going to move from A to B to C and so on.
And then, it wants to know how many rows down from there.
Do I want to go-- Well, I want to go one row down?
Yeah.
So, that’s cell B1.
And again, I’m going to press F4 here to specify, I want to go the number of rows down indicated in cell B1.
That’s it.
I don’t need to go up any columns over, height or width.
So, I’ll put the =OFFSET(DATA!A1,$B$1,0).
Height and width are just assumed to be one or the original dataset.
So, the size of the data-- so, as we copy that across, we now have a formula using offset instead of index, which goes back and grabs data from the data worksheet.
So, I would print the first one.
Change the 1 to a 2.
And you see, the OFFSET function now goes-- start from cell A1 goes down two rows.
So, in other words, we’re returning the data from row 3, 0 columns over.
And I get all the data from the next row.
After I print this one, change to A3, and then print, change to 4, and then change 5, change to 6, and then change to 7.
The reason I like this one a little bit better, as I get more data, you know, even if I’m expecting 10 or 15 records a day.
Eventually, there’s going to be a huge data, or get 25 records, or 30 records, or something.
Alright.
And then, it’s going to overwrite my data.
By keeping it separate, I can paste the data here each day and then come back to the report, and run through, and print each of these.
Now, tomorrow, I’m going to show you a macro that would make this very, very simple.
Once we paste that data, it could go through and do the print.
Again, I want to thank for you stopping by.
We’ll see you next time for another netcast of MrExcel.
Learn Excel from MrExcel podcast episode 1548, Get Next Record with Offset.
Hey, welcome back to MrExcel netcast.
I’m Bill Jelen.
All week, we’ve been talking about Mike’s problem where he gets data that he pasted into the data worksheet every day, and then needs to print one page per record.
And Monday and Tuesday, episode 1546 and 1547, we talked about a couple of ways to do this.
I’m going to talk about a third way.
I’m going to make a copy of this data worksheet.
Fast way to do that is hold down the CTRL key while I drag the tab to the right.
So, now I have data, and then report.
So, over here, on the report, I think this is where I’m actually going to build these formulas that point to the right spot.
So, I’m going to point all the extra rows here.
I just need to have one row that’s getting the data from the data worksheet.
And I’m going to enter couple of rows here where I specify the record number that I want.
So, record number, now, that means that, because I’m printing the printed report from back here.
Let’s see if we do CTRL + P. Yeah, it’s smart enough to know that print range still remained after I did that copy and paste.
Of course, it did one and I even have to check that.
We don’t need this print report down here anymore, edit, clear, all.
Alright.
Here’s a little bit of magic that we’re going to do to be able to grab data from the data worksheet and bring it over to the report worksheet.
I’m going to use a function called =OFFSET().
It has to start from a specific place.
And we’re always going to start from cell A1 on the data worksheet.
So, it’s coming =OFFSET(DATA!A1.
And I’m going to allow that to move as I copy across.
So, it’s going to move from A to B to C and so on.
And then, it wants to know how many rows down from there.
Do I want to go-- Well, I want to go one row down?
Yeah.
So, that’s cell B1.
And again, I’m going to press F4 here to specify, I want to go the number of rows down indicated in cell B1.
That’s it.
I don’t need to go up any columns over, height or width.
So, I’ll put the =OFFSET(DATA!A1,$B$1,0).
Height and width are just assumed to be one or the original dataset.
So, the size of the data-- so, as we copy that across, we now have a formula using offset instead of index, which goes back and grabs data from the data worksheet.
So, I would print the first one.
Change the 1 to a 2.
And you see, the OFFSET function now goes-- start from cell A1 goes down two rows.
So, in other words, we’re returning the data from row 3, 0 columns over.
And I get all the data from the next row.
After I print this one, change to A3, and then print, change to 4, and then change 5, change to 6, and then change to 7.
The reason I like this one a little bit better, as I get more data, you know, even if I’m expecting 10 or 15 records a day.
Eventually, there’s going to be a huge data, or get 25 records, or 30 records, or something.
Alright.
And then, it’s going to overwrite my data.
By keeping it separate, I can paste the data here each day and then come back to the report, and run through, and print each of these.
Now, tomorrow, I’m going to show you a macro that would make this very, very simple.
Once we paste that data, it could go through and do the print.
Again, I want to thank for you stopping by.
We’ll see you next time for another netcast of MrExcel.
