Data Vaidation and Inserting Rows

  • Thread starter Thread starter cwl123456
  • Start date Start date
C

cwl123456

Hi All,

I am having a little bit of trouble with data validation and inserting
rows in Excel 2000 (it's fine in Excel 2003).

The data validation formula is:
=INDIRECT(ADDRESS(ROW($A$42),MATCH($F$2,$41:$41))&":"&ADDRESS(ROW($A$41)+COUNTA(INDIRECT(ADDRESS(ROW($A$42),MATCH($F$2,$41:$41))&":"&ADDRESS(ROW($A$42)+20,MATCH($F$2,$41:$41)))),MATCH($F$2,$41:$41)))

There is a table starting at row 41. That row contains a list of
headings eg)

41 A B C D
42 ========================
43 a b c d
44 e f g h
45 i j

such that if F2 = A then the validation options are "a,e,i"

If a row is inserted (above row 41) then the table shifts down one row
and everything works fine (in excel 2003). Someone running Excel 2000
who inserts a row above row 41 also causes the shift but doesn't
automaticaly correct the formulas.

Any help is much appreciated

thanks,

Chris
 
one way:

If the table need not be visible/editable, put the table on a second
sheet.

Name the table, say "table", including the headers (make it dynamic if
necessary:

http://cpearson.com/excel/named.htm#Dynamic

). Then use something like

=OFFSET(table, 2, MATCH($F$2,OFFSET(table, 0, 0,
1,))-1,COUNTA(OFFSET(table, 2, MATCH($F$2,OFFSET(table, 0, 0,
1,))-1,ROWS(table)-2,1)),1)
 
Back
Top