View Single Post
  #2   Report Post  
Old July 26th 13, 01:26 AM posted to microsoft.public.excel.newusers
Gord Dibben[_2_] Gord Dibben[_2_] is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default Consistent Sheet column header rows

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.