Consistent Sheet column header rows

[Walter Briscoe]

It took me some time to find that I have broken a hidden constraint in a
file that I have:

I have many sheets which need to have consistent column titles.

In Sheet1, I might have
A B C D E F G
1 x x x Club Diamond Heart Spade

In sheet2, I might also have
A B C D E F G
1 x x x Club Diamond Heart Spade
where D1 is =Sheet1!$D$1, etc.

If I insert a column before D in sheet 1,
D1 becomes =Sheet1!$E$1, etc.

Further data in sheet 2 expects corresponding columns to have the same
numbers in both sheets.
e.g. D2 is =VLOOKUP($B$2,rangename,COLUMN())
That expectation quietly breaks.

I hope data validation can be used to stop me moving columns in Sheet1.
How, please?

I googled validation in titles and found Debra Dalgleish mentioning
grouping worksheets, which is likely to be useful.

I tried Data Validation. I got "You may not use references to other
worksheets or workbooks for Data Validation criteria" when I tried to do
a cross sheet reference. A reference to a name works, but the definition
of the name changed when I inserted a column.

 

[Gord Dibben]

Walter

Take a look in help at the INDIRECT function and/or search this forum
for posts using that function.

Some links to look at.

http://www.cpearson.com/excel/indirect.htm
http://office.microsoft.com/en-in/excel-help/indirect-function-HP010062413.aspx
http://office.microsoft.com/en-in/excel-help/indirect-function-HP010342609.aspx
http://www.contextures.com/xlFunctions05.html


Gord

 

[Walter Briscoe]

Gord,
Thanks for the reply.
A salient quote from <http://www.cpearson.com/excel/indirect.htm> is
"Another useful feature of the INDIRECT function is that since it takes
string argument, you can use it to work with cell references that you
don't want Excel to automatically change when you insert or delete rows.
Normally, Excel will change cell references when you insert or delete
rows or columns, even when you use absolute referencing. ..."

I will apply that new bit of knowledge to my problem.

One trouble with INDIRECT is that it is a volatile function - a workbook
using INDIRECT is marked as changed as soon as it is opened. I will look
at putting .saved = True in an auto_open function to get round that. (I
don't expect the extra time in opening the file will worry me.) A better
solution may be to use INDEX. Thanks for giving me a challenge.

In message <[email protected]> of Thu, 25 Jul
2013 17:26:19 in microsoft.public.excel.newusers, Gord Dibben