This seems to be a straight forward question that I can't find an answer for.
I'm inputting a list of products in Excel. Our store has set Departments and Classes inside those departments.
I have one column marked "Department" which pulls (using data validation) from a NAMED LIST from another Sheet. [That is, in Validate: Settings, the source says "=departments" which is what I named that list of departments].
This works perfectly fine and I understand data validation. I have the entire Department column set to use data validation to show this list on each new record. Works every time and works well.
Here's the problem. The Classes are dependent on the Department. To make this SIMPLE imagine I have 2 departments (I really have approximately 40 departments and 100s of classes):
Each department has it's own classes of products:
To store these, I have column B on the second sheet with all the Classes for "Computers" and named that list "computers". I have the three monitors classes listed in Column C on the other sheet and named that list "monitors"
If the user picks the department Computers for a new product, the next column, Class should show the possible classes for Computers (Laptop, Desktop...)
From everything I've read, I should do an INDIRECT() lookup... I can do that pretty easily for individual cells in the column. I can go to row 4 and click on the Class cell and put in this formula for data validation:
=INDIRECT($AF$4)
In this case "AF4" is a calculation which has the LOWER of the department with SUBSTITUTING the spaces in the name with underscores (then I use that same naming convention in my lists). For example:
=LOWER(SUBSTITUTE(I17," ","_"))
As I explained, that works for individual cells. It does NOT work for the entire column. That is, I can't select the whole column and put in that formula... it is always attached to AF4 (or whatever I put in there). I need it to change for EACH ROW.
I've tried all these - none of them work.
=INDIRECT(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,4))
=INDIRECT(ADDRESS(ROW(), "AF"))
=INDIRECT(INDIRECT("AF"&ROW()))
Almost every time I get an error saying "The source currently evaluates to an error - Continue?" which means it never works :-(
I hope that's enough info for some Excel expert out there to help me.
Thanks!
OK, I got it working with your example. I entered the departments in column A of Sheet2 and the products in column B, as such:
Computers Laptop
Computers Desktop
Computers Rack Mount
Computers Server
Monitors LCD
Monitors CRT
Monitors OLED
I then set up a dynamic named range for column A to decrease the lookup load on Excel. I named the range "DeptsForProducts". It is important that you not only repeat the department name for each product row but also that the columns be sorted such that all products for the Computers department are together, all products for the Monitors row are together, etc.
Then, return to your original sheet (I used Sheet1; column A holds the validation for Department and column B is where I would like the validation for Products to appear). Select all of column B, open the data validation window, change the Allow: to List, and enter the following formula:
=OFFSET(Sheet2!$B$1,MATCH(A1,DeptsForProducts,0)-1,0,COUNTIF(DeptsForProducts,A1))
You will get the "Source currently evaluates to an error" message again; ignore it and click Yes. Now, any cells in column B with Computers in column A will have a dropdown list of Laptop, Desktop, Rack Mount, or Server; any cells in column B with Monitors in column A will have a dropdown list of LCD, CRT, or OLED; and any cells with a blank or invalid department will have no dropdown list available.
Tested in Excel 2013.