Part Two of our Validation follow-up [Episodes #1583 and #1606], Bill continues to explain and demonstrate the Validation for selecting client Data including how Duplicate are handled and why they will remain in tact in the Excel Database.
...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 for MrExcel podcast – Truly Unnecessary Validation.
Oh hey in yesterday's netcast, having this discussion at YouTube, talked about how we can have a drop down here for customer, and then once we choose the customer offer only the addresses in that area and I said -- you know, hey the thing that bothers me more is the fact that you have duplicates here and so I suggested using remove duplicates out here and then rowed back.
I said, well no no wait, I’m not going to have any duplicates here because anytime I have a duplicate I’m going to add something to the name so that way they can choose you know, whether it's this one or that one.
So remember I, yesterday had two Cozy Corners -- I made a Cozy Corner New and Cozy Corner Original, or you know, something like that.
So that way when they choose from this drop-down, the person choosing from the drop-down is going to be able to figure out, you know, which Cozy Corner they're talking about and I said -- oh okay great, that eliminates the need for this column E over here but it also eliminates the need for any kind of a validation in the address; all we have to do is as soon as this is no longer not blank, then just do a simple VLOOKUP.
Once you assure me that everything is unique over here then it's a simple little VLOOKUP.
So what we have, I’m using the ISBLANK function.
Check to see if B2 is blank, if it's blank then give me quote- quote ("") -- in other words, give me nothing.
So if we've chosen nothing here, don't give me anything, undo.
But as soon as it's non-blank then do a VLOOKUP -- now go back to VLOOKUP week, go check out B2 from customer list, A, colon (:), B.
=IF(ISBLANK (B2),"", VLOOKUP(B2,customerlist!A:B,2,FALSE)) So in other words, that whole column, it will automatically grow as you add more items to the list; we want the second column, and then false to say that it's an exact match.
So Cozy Corner Café New: 1621 Tropical Trail.
If we would instead come here and choose Cozy Corner Café Original: 421 Courtenay Parkway.
So it's getting the right one, so you know -- hey if these names are going to be unique here then there's no reason to do dependent validation, no reason to do validation at all, no reason even have them choose that as soon as they choose the name.
You can just fill in the address with the VLOOKUP and you're good to go.
Of course as I mentioned yesterday, none of these addresses are real everything here was made up.
There is only one Cozy Corner and there's not a new and original.
Well, highly recommended if you’re ever in Merritt Island stop by for breakfast and tell them MrExcel sent you.
Hey, I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
Learn Excel for MrExcel podcast – Truly Unnecessary Validation.
Oh hey in yesterday's netcast, having this discussion at YouTube, talked about how we can have a drop down here for customer, and then once we choose the customer offer only the addresses in that area and I said -- you know, hey the thing that bothers me more is the fact that you have duplicates here and so I suggested using remove duplicates out here and then rowed back.
I said, well no no wait, I’m not going to have any duplicates here because anytime I have a duplicate I’m going to add something to the name so that way they can choose you know, whether it's this one or that one.
So remember I, yesterday had two Cozy Corners -- I made a Cozy Corner New and Cozy Corner Original, or you know, something like that.
So that way when they choose from this drop-down, the person choosing from the drop-down is going to be able to figure out, you know, which Cozy Corner they're talking about and I said -- oh okay great, that eliminates the need for this column E over here but it also eliminates the need for any kind of a validation in the address; all we have to do is as soon as this is no longer not blank, then just do a simple VLOOKUP.
Once you assure me that everything is unique over here then it's a simple little VLOOKUP.
So what we have, I’m using the ISBLANK function.
Check to see if B2 is blank, if it's blank then give me quote- quote ("") -- in other words, give me nothing.
So if we've chosen nothing here, don't give me anything, undo.
But as soon as it's non-blank then do a VLOOKUP -- now go back to VLOOKUP week, go check out B2 from customer list, A, colon (:), B.
=IF(ISBLANK (B2),"", VLOOKUP(B2,customerlist!A:B,2,FALSE)) So in other words, that whole column, it will automatically grow as you add more items to the list; we want the second column, and then false to say that it's an exact match.
So Cozy Corner Café New: 1621 Tropical Trail.
If we would instead come here and choose Cozy Corner Café Original: 421 Courtenay Parkway.
So it's getting the right one, so you know -- hey if these names are going to be unique here then there's no reason to do dependent validation, no reason to do validation at all, no reason even have them choose that as soon as they choose the name.
You can just fill in the address with the VLOOKUP and you're good to go.
Of course as I mentioned yesterday, none of these addresses are real everything here was made up.
There is only one Cozy Corner and there's not a new and original.
Well, highly recommended if you’re ever in Merritt Island stop by for breakfast and tell them MrExcel sent you.
Hey, I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
