The better part of three years ago, I created a tool — in actuality, a spreadsheet — for use with my job. I've since gone on to create several tools, which over time I evolved and developed, and which have now been reduced to their final state, with just two used for my entire personal workflow relative to data I use with my job.
A key part of their functionality is the VLOOKUP function. For those reading this thread just to see what it's about, VLOOKUP is a standard spreadsheet function which lets you turn a spreadsheet into a reasonably functional database by letting you set up a given field and then supply you with content based on what you enter into that field. As you may appreciate, the more sophisticated your design, the more sophisticated the results can be.
The tool I need help with — for which I have also posted queries on LibreOffice's online help community — is the one I use to plan my weeks out.
For my job, I travel to a store a day, five days a week, with a crew, and we audit that store's inventory. The company I work for is not really good about helping its employees and it certainly isn't good at all with providing useful tools for us, so I've taken all the stores in my state that are part of my business unit, gone over the address list and meticulously verified the addresses to every single store (many of the addresses in our official company store list have errors of various kinds, a number of which are just flat-out wrong, and it's just not considered a priority to respond to requests to fix addresses) and then, having verified the addresses, I have used Google's Maps tool to calculate the distance and drive time from my home to every single one of them. This list is the better part of 600 stores long.
I have six sheets within the file. The first is a data entry interface, allowing me to enter up to four weeks' worth of stores, along with the date for the first day of each week, and provisions for typing in the names of the people I am working with.
The second, third, fourth, and fifth sheets are basically weekly report forms which then give me what district the store is a part of, what the address is, what the phone number is, who the district manager is, what the physical size of the store is, how much time I should budget to drive there and how many miles that will be, and then how much I can bill the company for for both drive time and mileage, either for a round trip (which is the norm) or a one-way distance for special purposes. (At this point, I print the thing out and use that for a physical record but also as the basis to build a set of Google Calendar events.)
The sixth sheet is a massive data table holding all of the store data, and is the place from which VLOOKUP summons everything for the four weekly report sheets.
The Problem (And What I Need Help With):
Recently, I've seen fit to create a new version of this tool because there's now certain other information I need to be able to retrieve automatically. I redesigned the layout of the report pages to better suit the change and account for things I've learned along the way. Because of layout changes, the exact cells I am using VLOOKUP with has changed, and I've fully accounted for that. I've copied-n-pasted a working VLOOKUP "formula" and then simply tweaked which cell for it to look at, and which column's data I want displayed. And, it works: perfectly.
I can summon data in this way for anything I want from columns 2 - 13. It works like a champ. The moment I specify column 14 and beyond, I get a 502 error.
I have even experimentally taken the original formula from the original file and created another spot within that original file and entered the equivalent change, and I get a 502 error there as well. I did this because I wanted to rule out there being anything specific to the new file.
Here's the formula I'm using. It also includes an IF statement because I want the cell to be empty when no data has been entered on the initial master data entry page for that particular day, in any of that particular day's no-data-containing cells.
Code: Select all
=IF(H6=0,"",(VLOOKUP(H6,$'Data Lookup'.A:$'Data Lookup'.M,6,0))) <--- This formula returns valid data. =IF(H6=0,"",(VLOOKUP(H6,$'Data Lookup'.A:$'Data Lookup'.M,14,0))) <--- This formula returns "Err:502".
If necessary, I will be happy to send a copy of this file to someone with dummy equivalent data instead of sensitive internal data, and they can mess around with the file and perhaps discover something I've overlooked, or just wouldn't even know is a problem.
Thanks so much in advance, folks!