Ethan sends in a question about optionally allowing long cell values to spill over into the next column. Episode 616 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, we have a question sent in by Ethan.
And I'm not sure that, I have a solution to Ethan's question without using a macro.
Let's take a look at Ethan's problem.
He says I have some data in Column B, and and sometimes the data is short data in Column B.
But sometimes it's longer data really long data and it spills over into column C.
And in column C has a formula, and that's pulling some sort of a value.
So, we'll copy this down.
He says, you know, obviously the problem is when the data is really long in column B.
I can't see it because it's obscuring the data in Column C.
And what I would like to do is optionally, if the data in Column B is really long, not have the formula in Column C.
Is there any way to put an IF function here?
That says you know if the data in Column B is too long, don't display anything and see so that way we can still see the data that spills over.
Well, unfortunately, this doesn't work.
We try IF function equal IF the length LEN of B2 is let's say greater than 20, then I want to show nothing.
So, two quotes otherwise I want my formula two times E2.
The problem is that even though we put nothing in as the result.
It's not an empty cell anymore and Excel will only allow Column B to spill over, when column C is an empty cell.
Now, there would be one very obscure, very difficult use of the camera tool where we set up a different dynamic range for every single row.
But let's assume that we have 100 rows, it would take you forever to do this.
I think this is one where it's much easier to use a macro to solve the problem.
So, I'm going to try Macros about once a week.
So, basically what we want to do is loop through all the data in column B.
If that data is longer than let's say 20, then we clear the formula and see otherwise write the formula and see.
I'll switch over the macro editor with [ alt + F11 ] and I'll use insert module.
I'll call this fix them and what will say is for each cell in range B2 to let's see...
We happen to have six records, today of course.
You could make this B as wide as you want or as long as you want.
If the length of cell dot value is greater than 20 then cell dot Offset.
So, in other words, works currently in column B.
Let's go down 0 rows and over 1 row.
Clear and if next cell.
So, this tiny little macro will basically run through all of the records in column B.
If it's longer than 20, it's going to clear the formula and see allowing the record to spill.
So, I'll hit all the [ F8 ] to run that macro called it fix them, click [ run ], and now you'll see that for any of the places where column B was longer.
We're now seeing the end of the data.
So, there you have it a short little macro.
Perhaps five lines, to solve Ethan's problem.
It's a place where the macro language actually allows us to do something, that would be very impractical to go through and do manually but very, very quick to do in Excel.
So, thanks to Ethan for sending in that question.
Thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today, we have a question sent in by Ethan.
And I'm not sure that, I have a solution to Ethan's question without using a macro.
Let's take a look at Ethan's problem.
He says I have some data in Column B, and and sometimes the data is short data in Column B.
But sometimes it's longer data really long data and it spills over into column C.
And in column C has a formula, and that's pulling some sort of a value.
So, we'll copy this down.
He says, you know, obviously the problem is when the data is really long in column B.
I can't see it because it's obscuring the data in Column C.
And what I would like to do is optionally, if the data in Column B is really long, not have the formula in Column C.
Is there any way to put an IF function here?
That says you know if the data in Column B is too long, don't display anything and see so that way we can still see the data that spills over.
Well, unfortunately, this doesn't work.
We try IF function equal IF the length LEN of B2 is let's say greater than 20, then I want to show nothing.
So, two quotes otherwise I want my formula two times E2.
The problem is that even though we put nothing in as the result.
It's not an empty cell anymore and Excel will only allow Column B to spill over, when column C is an empty cell.
Now, there would be one very obscure, very difficult use of the camera tool where we set up a different dynamic range for every single row.
But let's assume that we have 100 rows, it would take you forever to do this.
I think this is one where it's much easier to use a macro to solve the problem.
So, I'm going to try Macros about once a week.
So, basically what we want to do is loop through all the data in column B.
If that data is longer than let's say 20, then we clear the formula and see otherwise write the formula and see.
I'll switch over the macro editor with [ alt + F11 ] and I'll use insert module.
I'll call this fix them and what will say is for each cell in range B2 to let's see...
We happen to have six records, today of course.
You could make this B as wide as you want or as long as you want.
If the length of cell dot value is greater than 20 then cell dot Offset.
So, in other words, works currently in column B.
Let's go down 0 rows and over 1 row.
Clear and if next cell.
So, this tiny little macro will basically run through all of the records in column B.
If it's longer than 20, it's going to clear the formula and see allowing the record to spill.
So, I'll hit all the [ F8 ] to run that macro called it fix them, click [ run ], and now you'll see that for any of the places where column B was longer.
We're now seeing the end of the data.
So, there you have it a short little macro.
Perhaps five lines, to solve Ethan's problem.
It's a place where the macro language actually allows us to do something, that would be very impractical to go through and do manually but very, very quick to do in Excel.
So, thanks to Ethan for sending in that question.
Thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
