In need of List Guru

  • Thread starter Thread starter megspullingherhairout
  • Start date Start date
M

megspullingherhairout

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.
 
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.
 
The dropdowns will then always be relative to the value that is entered in
column At gets sorted in the list.
Click to expand...

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.
 
Back
Top