Posted 8 September 2017, 2:22 pm EST
We have a spread with multiple sheets. In each sheet, we have a varying number of columns, but each sheet also contains columns of data that users will refereence using formulas. For example, in sheet 1, we may have 3 "descriptive" columns and then numbers begin in column 4. In sheet 2, we may have 4 "descriptive" columns and the numbers begin in column 5. This means that the label for the first number column in sheet 1 is D, while in sheet 2, it is E. To make it easier for the user, we would like to standardize that the first number column in each sheet is always labeled "AA". Meaning, in sheet 1, the column labels would be A, B, C, and then jump to AA. In sheet 2, the colun labels would be A, B, C, D, and then jump to AA. I wrote a function to automatically increment the column labels to change D to AA and E to AB (in sheet 1) and so on. I then called a function to set the column label. However, formulas reference the column's true index. That is, the 4th column in sheet 1 is still D internally. Is there a way I can fake the column labels and have the formulas deal with them via the new name?
I don't really want to add empty, invisible columns as this will cause a lot of junk data in my database app and could impact performance.