Forum Discussion
Lookup from Bottom cell to Top cell
Unfortunately, it's not possible to change the data source format as my colleagues would refuse that. It's prepared in Excel and pasted into Outlook. However, as I have gathered from all your comments that It's really difficult to perform such a task, I have come up with my own solution. It's not ideal and requires lots of labor which is against Excel's nature and I wanted to avoid that and that's why I asked you experts to step in. My observation has been that in programming, there is always a solution to everything but It's not always that easy.
My solution:
Since the data source will always be in the same format, I'm creating direct cell references in another sheet and organizing the table in a vlookup or index match friendly way. It's a lot of work and not really ideal but it's a solution.
You wrote:
The data comes from an e-mail. It's how it's formatted in the e-mail. And copied as is in the file. Again it'll always be in the same format as I uploaded in the sample.
And before we go any further, I need to ask--which is what I'd do if we were sitting down face-to-face--whether there's any possibility to change that source data? Frankly, that's what I'd want to do. That current array of colorful data is just not laid out in a manner that makes it readily searchable for what you are wanting to retrieve, described in this sentence:
So, in another sheet, I'll enter employee's name then I need to look up their lunch hour, shift and team.
I'll grant you that it's arrayed in such a manner that it is somewhat appealing for a human to look at , going in reverse--knowing their Team (and name) already, using that to find their own lunch break.
But going in, via Excel, starting with the name (as you are seeking to do) and finding the team and lunch hour.... that's a different task, a task for which this is not at all a helpful array.
Hence my question: is there any possibility to change that source data?
Do you know how it's generated in the first place? Is there a database somewhere, and an extraction routine running on that database to produce this layout? Could you (we) get back to that original source?
Unfortunately, it's not possible to change the data source format as my colleagues would refuse that. It's prepared in Excel and pasted into Outlook. However, as I have gathered from all your comments that It's really difficult to perform such a task, I have come up with my own solution. It's not ideal and requires lots of labor which is against Excel's nature and I wanted to avoid that and that's why I asked you experts to step in. My observation has been that in programming, there is always a solution to everything but It's not always that easy.
My solution:
Since the data source will always be in the same format, I'm creating direct cell references in another sheet and organizing the table in a vlookup or index match friendly way. It's a lot of work and not really ideal but it's a solution.- Your solution sounds familiar to the one that I would have been proposing and if it was done in the right way, when a new set of data is received, if you paste it into the workbook in which you have setup your solution, you will not need to repeat that manual work.
You wrote:
Unfortunately, it's not possible to change the data source format as my colleagues would refuse that.
To which I'd suggest that maybe "colleagues" is the wrong term, since it usually implies a degree of collegiality or cooperation in the advancement toward shared outcomes. Doesn't require that, however, so you can continue describing them that way. Or maybe you could build on it and see if they would, in fact, be able to be flexible and collegial..... Of course, it all depends on how important your task is in the grand scheme of things.
You also wrote of your solution:
Since the data source will always be in the same format, I'm creating direct cell references in another sheet and organizing the table in a vlookup or index match friendly way. It's a lot of work and not really ideal but it's a solution.
Actually, if your colleagues won't flex their format, your solution is quite reasonable, so long as the data is always in the same format, with no additional rows or columns ever being added.
