Input range changes while deleting row, how to solve?

[Jeroen Kluytmans]

Hi,

I have a list on Sheet2 in range A8:A1000, on Sheet1 I have defined a
drop down list with A8:A1000 of sheet1 as input range. Several macros
are running on Sheet2, like deleting a row and shifting the rows below
one position up, this works fine...but:

The input range of the drop down list on Sheet1 changes because of
these actions, can I fix this input range no matter what actions I
preform on Sheet2?

Thanks
Jeroen Kluytmans

 

[JE McGimpsey]

Use a dynamic range:

http://cpearson.com/excel/excelF.htm#DynamicRanges

 

[Jeroen Kluytmans]

Use a dynamic range:

http://cpearson.com/excel/excelF.htm#DynamicRanges

This is what I tried but I can't get it fixed, I do not want to be the
range dynamic, I want is always to be the same, this seems to change the
range also when I delete a row. Maybe I program my control not
correctly, but I can't get it done.

Kind regards,
Jeroen Kluytmans

 

[JE McGimpsey]

Then use INDIRECT().