offset match validation formula

[nd4spd]

column c filters (CODE) don't seem to give correct data when filtered " =OFFSET($K$3,MATCH(B4,$K$4:$K$11,0)-0,-2,COUNTIF($K$4:$K$11,B4),1)" please find attached sample file.
thanks

[:) Sixthsense :)]

Refer the below links for step by step instructions.

http://www.contextures.com/xlDataVal02.html

http://www.contextures.com/xlDataVal13.html

[nd4spd]

sixthsense i tried using the same scenario as per your link http://www.contextures.com/xlDataVal13.html provided but not sure where the formula is going wrong ???

[:) Sixthsense :)]

Please check the attached file for details

[nd4spd]

hi sixthsense thanks for the solution but slight issue the actual file has more than 500 names and is not in sorted order. So i am not sure it would be feasible to name name ranges. I used pivot table to extract unique NAME and use that as a filter and would wanna use that dependant to filter all the CODE. if you could help ??

[Tony Valko]

Explain in words what you're trying to do.

[nd4spd]

well all i am trying to do is extract CODE info that matches the NAME. The CODE is dependant on the NAME.

The workbook consists of 3 sheet tabs Data, Pivot table and Report. The Data sheet consists of 15000 lines and needs to be updated monthLY. The following main headers are vital in Data sheet for reporting and extracting the information CODE (column C), NAME (coloumn K), DESCRIPTION (column J). The CODE is unique in each line whereas the the NAME can be repeated several times and but for filteration NAME & description are much easier to understand.

So far i manage to make a unique NAME List from Pivot Table and have validated into the Report sheet for filteration. Now I need to be able to create a dependant list with a dependent description.

i hope the below simple example would help understand.


DATA TABupdated monthly without alteration to structure)
CODE DESCRIPTION NAME
XYZ SILVER A007
F789 GOLD A007
T2E BRONZE B005
Y92S PLATINUM C789
RUUT COPPER B005


PIVOT TABLE TAB: (refreshed everytime data is updated)
NAME
A007
B005
C789


REPORT TAB (FILTERS):
NAME CODE DESCRIPTION

[Tony Valko]

So, you want a list of the unique names?

[nd4spd]

unique NAMES i managed to get from pivot table. All i want is a list of CODE that are associated with that name. e.g on filteration to A007 it should give me filter option XYZ , F789

[Tony Valko]

Sorry, not following you.

If you filter on A007 then the codes XYZ and F789 will be displayed.

[nd4spd]

thats correct Tony

[Tony Valko]

Ok, so what are you wanting to do?

[nd4spd]

i want to be able to filter (XYZ and F789) Code based on if A007 is selected in sheet report.

DATA TAB updated monthly without alteration to structure
CODE DESCRIPTION NAME
XYZ SILVER A007
F789 GOLD A007
T2E BRONZE B005
Y92S PLATINUM C789
RUUT COPPER B005


PIVOT TABLE TAB: (refreshed everytime data is updated)
NAME
A007
B005
C789


REPORT TAB (FILTERS):
NAME CODE DESCRIPTION

[Tony Valko]

I'm still not sure what you want to do. Here's my best guess...

nd4spd.xlsx

The formula on Sheet1 in column B is an array formula.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[nd4spd]

Hi Tony thanks looks promising but I want to be able to have a drop down in column B in each cell referring to A is that possible.

[Tony Valko]

Ok, I think I understand now.

If A2 = A007 then you want a drop down list in B2 with the codes that correspond to A007.

Yeah, that's possible but it takes some work to setup.

See this:

Dependent Drop Down Lists

[nd4spd]

Please could you help wen u get a chance as i did have a look at that link before but ended up making a mess with too many names and formula validation errors.

[Tony Valko]

OK...

Add a new worksheet and name it something like Lists. It's going to hold all the source data for the drop down lists.

In row 2 enter all the names and below the names enter all the corresponding codes:

For example, A2:A6 =

A007
XYZ
F789
erwr
TEST

B2:B4 =

B005
T2E
RUUT

C2:C3 =

C789
Y92S


Give each one of these lists a defined name that is the same as the name in row 2.

It looks like some of your names are the same as some cell addresses, for example, name C789. That's also a cell address so Excel won't let you create a defined name C789. So, we'll add an underscore to the beginning of the range names.

Name: _A007
Refers to: =Lists!$A$3:$A$6

Name: _B005
Refers to: =Lists!$B$3:$B$4

Name: _C789
Refers to: =Lists!$C$3

Now, setup the drop down lists...

On the Report sheet...

Select cell B2
Goto Data Validation
Allow: List
Source: =INDIRECT("_"&A2)
OK out

Drag copy cell B2 down as needed.

Here's your file with this implemented:

dropdown dependant(1).xlsx