How do I reverse the columns in a Named Range?
Posted on 2011-10-09
I have several Named Ranges of Text/Numerical lists in a Lookups tab as the following template:
In other tabs which reference the text values in dropdown lists (via Data Validation). On closing the file I ise VBA wipe out the dropdown lists and use temporary VLookups to replace the text with their number values in the various tabs so they can be scraped into an SQL database. I figured that part out - so far so good. (BTW - I'm not developing the database, just the Excel portion.)
The problem is that when the file is reopened I need to change those numerical values back to their text equivalents, but of course I can't use the same VLookup functions because a VLookkup can't look at a colmn to the left of the value you're looking up. All the solutions I can come up with are klunky: a Reverse Lookups tab linking to the main Lookups tab but with the columns switched, a third column after the range linking to the text value in the third column, etc.
There has to be something better!
I'd love to use VBA to take an existing range and create a new range, but with the columns switched as follows:
Do my VLookup on this virtual range an move on!
Anybody go an answer?