In need of List Guru
 

In a spreadsheet I am working on I am trying to create several drop down list
in order to filter data. The Auto Filter option works well, but when sorting
certain columns, some cells containing absolute values lose their data as
they are shifted throughout the column. For example, I have a cell "organic"
in the column titled "products" it contains a dropdown list with the value
"$B$13:$B$17" (the dropdown list contains "veggies, tofu, rice cheese etc")
or something like that. When I use the sort option to say, sort a different
column, "organic" is shifted up and does not keep the data that I applied to
it (but that data remains in that cell, just with a different name). Why is
this and is there a way around it? What am I doing wrong? Any help would be
MUCH appreciated.

In need of List Guru
 

Hi

Instead of using ranges like $B$13:$B$17, give these ranges a name - Organic
etc.
Then, in Data Validation, List=INDIRECT($A1)
assuming column A is where your products are located.

The dropdowns will then always be relative to the value that is entered in
column At gets sorted in the list.

--
Regards
Roger Govier

"megspullingherhairout"
wrote in message ...
In a spreadsheet I am working on I am trying to create several drop down
list
in order to filter data. The Auto Filter option works well, but when
sorting
certain columns, some cells containing absolute values lose their data as
they are shifted throughout the column. For example, I have a cell
"organic"
in the column titled "products" it contains a dropdown list with the value
"$B$13:$B$17" (the dropdown list contains "veggies, tofu, rice cheese
etc")
or something like that. When I use the sort option to say, sort a
different
column, "organic" is shifted up and does not keep the data that I applied
to
it (but that data remains in that cell, just with a different name). Why
is
this and is there a way around it? What am I doing wrong? Any help would
be
MUCH appreciated.

In need of List Guru
 

The dropdowns will then always be relative to the value that is entered in
column At gets sorted in the list.

should have read

The dropdowns will then always be relative to the value that is entered in
column A no matter where it gets sorted in the list.


--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

Instead of using ranges like $B$13:$B$17, give these ranges a name -
Organic etc.
Then, in Data Validation, List=INDIRECT($A1)
assuming column A is where your products are located.

The dropdowns will then always be relative to the value that is entered in
column At gets sorted in the list.

--
Regards
Roger Govier

"megspullingherhairout"
wrote in message
...
In a spreadsheet I am working on I am trying to create several drop down
list
in order to filter data. The Auto Filter option works well, but when
sorting
certain columns, some cells containing absolute values lose their data as
they are shifted throughout the column. For example, I have a cell
"organic"
in the column titled "products" it contains a dropdown list with the
value
"$B$13:$B$17" (the dropdown list contains "veggies, tofu, rice cheese
etc")
or something like that. When I use the sort option to say, sort a
different
column, "organic" is shifted up and does not keep the data that I applied
to
it (but that data remains in that cell, just with a different name). Why
is
this and is there a way around it? What am I doing wrong? Any help would
be
MUCH appreciated.