Data validation Reference getting changed if creating a copy of sheet

[amitmodi_mrt]

Hi, I have a sheet which has Drop downs created with Data validation option. whenever I create a duplicate sheet of this, it is changing the Reference of the drop down in the duplicate sheet and i m not able to see all the options..

Please note that earlier I had given different reference range in data validation and now i have changed the reference range and in duplicate sheet it is taking previous reference ranges and not the new one. I cleared my temporary files also, but still i facing same issue.

Any suggestions what is going wrong in my sheet..?

Regards,
Amit.

[oeldere]

Please Login or Register  to view this content.

What is the reference in the Ooiginal sheet.

[amitmodi_mrt]

Hi oeldere,

Earlier it was...

=OFFSET(Ref!$L:$L,MATCH($E$4,Ref!$K$2:$K$100,0),0,COUNTIF(Ref!$K$2:$K$100,$E$4),1)

which i changed to..
=OFFSET($S:$S,MATCH($E$4,$R2:$R$100,0),0,COUNTIF($R$2:$R$100,$E$4),1)


Similarly: in another cell, earlier it was
=Ref!$B$2:$B$3

Which I changed to
=Ref!$B$2:$B$4


But in both cells it is changing to old reference range when i am creating a duplicate sheet (by Move or Copy option)

[amitmodi_mrt]

Hi oeldere,

Earlier it was...

=OFFSET(Ref!$L:$L,MATCH($E$4,Ref!$K$2:$K$100,0),0,COUNTIF(Ref!$K$2:$K$100,$E$4),1)

which i changed to..
=OFFSET($S:$S,MATCH($E$4,$R2:$R$100,0),0,COUNTIF($R$2:$R$100,$E$4),1)


Similarly: in another cell, earlier it was
=Ref!$B$2:$B$3

Which I changed to
=Ref!$B$2:$B$4


But in both cells it is changing to old reference range when i am creating a duplicate sheet (by Move or Copy option)

[oeldere]

=OFFSET(Ref!$L:$L

In your new code you don't refer to the sheetname !!

[amitmodi_mrt]

No, because it was not working in MS Office 2007 version, hence i kept in the same sheet

[oeldere]

In that case use a defined name.

formula => define name.