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/ex...010062413.aspx
http://office.microsoft.com/en-in/ex...010342609.aspx
http://www.contextures.com/xlFunctions05.html
Gord
On Sun, 21 Jul 2013 11:03:25 +0100, Walter Briscoe
wrote:
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.