Thanks, David. -Vic
On Fri, Apr 15, 2011 at 10:41 AM, David Bullock <[email protected]> wrote: > Hi list, > > Here's a hack which allows an application to refer to columns by a stable > name. (Caveat: I have not yet tried it): > > 1. Create named ranges for each of the heading cells for the columns which > your code wants to reference; > 2. Create a worksheet called 'Names' (lately, you can hide this sheet), > which has columns "Key" and "HeaderValue" > 3. For each column your application wants to refer to, enter a row in the > 'Names' sheet, where 'Key' is some arbitrary name your application will use > to refer to the column, and 'HeaderValue' is a formula invoking the > reference. > 4. When your application (re)configures itself at runtime, fetch the > 'Names' worksheet and find out what values the header cells of the columns > have at the moment. > 5. Make the necessary translation in your code when writing queries and > interacting with data in a row. > > Using this approach, users of your sheet can do things which might be > beneficial to them: > > * move columns around > * change the header value to whatever makes most sense to them > > You're still at the mercy of your users if they: > > * delete the column (and therefore the named range) - oops! > * commandeer a column for a different semantic use by cut/paste of foreign > values into the column > > ... but it's difficult to see how these operations are going to be > beneficial to them, and we can expect they will generally avoid these. > > I hope it helps someone. Let me know if it works out for you or not. > > cheers, > David. >
