Hey, welcome back to the MrExcel netcast, I’m Bill Jelen. A great question today sent in by Brett Winson. Brett is from Australia, he has a series of dates, so January 1, 2010 here and he used this great formula =date of the year of the date of us one plus the month of the date of us and one and when we copy that down it accurately will fill in the first of each month including, I’m always amazed here in January where he is actually asking for the first of the 13th month of 2010 and it has no formula for that now.
Brett went through and formatted this, control one. It will format it as a date where we show three digit month name. It actually has this one here, and he had three Ms four Ys January 2010.
Alright, so far so good, but he needs to do a VLOOKUP back in the report and he needs to have the words, “opening balance” appear opening balance, and so he came here and he joined in quotes, opening balance and percent alright, and then everything else change into a value he want to copy that down.
He said, “Alright, how can I make those words appear and have it all work out?” So, here’s the solution that I have. We’re going to go back into the custom number format, so control one and here in the custom number format, in quotes we’re going to put, opening balance and the date code that we used before and that will get to appear correctly.
Now, unfortunately, if you’re actually going to do a VLOOKUP for this, the VLOOKUP is not going to work very well. We’re going to have to do =VLOOKUP and here in the first part, what we’re going to look up? We’re going to look up the text of the cell comma and then repeat here the custom number format from before, so two quotes to get a single quote to appear, opening balance. Two quotes to finish that off, and then three Ms, four Ys and what this function is going to do is convert that date into something.
It looks like opening balance January 2010, and then when he goes back to his table that has those actual amounts it’s going to work, so there you have it. Very complicated solutions and then it should be relatively easy. If you just name to VLOOKUP look right custom number format will do it correctly, but then to get that VLOOKUP to work, use the text function.
Alright, I want to thank you for stopping by. Thank Brett for sending that question. We’ll, see you next time for another netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services