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.
