Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 279
Default Consistent Sheet column header rows

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.
--
Walter Briscoe
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
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.

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 279
Default Consistent Sheet column header rows

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 of Thu, 25 Jul
2013 17:26:19 in microsoft.public.excel.newusers, Gord Dibben
writes
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.


--
Walter Briscoe
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Consistent Header Size MarieG Setting up and Configuration of Excel 0 August 3rd 09 05:52 PM
Search for a column based on the column header and then past data from it to another column in another workbook minkokiss Excel Programming 2 April 5th 07 01:12 AM
Select all rows except header row in one column Meltad Excel Programming 5 March 23rd 07 05:05 PM
keep column's information consistent in different worksheets in same row [email protected] Excel Programming 0 June 29th 06 12:57 AM
Creating a Dynamic Named Range Using Sheet Name and Column Header burl_rfc_h Excel Programming 8 February 13th 06 11:53 PM


All times are GMT +1. The time now is 07:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"