function indirect doesn't work with data validation



I  try  linking  datavaldition with  function  INDIRECT but  it  doesn't  show  anything  in  column B  after select  from  data validation in  col  A  in SHEET1 based on  data  in sheet  SH   ,actually    I  searched  in the  internet  and  showed   some  videoes  ,but   I  don't  find  what  I  make  mistake  ,there  is  missed. 

so  I truly  appreciate  if  any  body  guide  me  the  right  way  to do that 



Selected Answer

In your drop-down in A2 you have "AA", "BB", "CC" and "DD". None of these strings are named ranges. Therefore =INDIRECT(A2) just returns A2.

Do the following.

  1. Set range BRAND1 = F2:F3, BRAND2 = G2:G3, BRAND3 = H"H3 and Type1 = I2:I3.
  2. In the validation of A2 change the list range to =She!F1:i1

Now the drop-down in A2 will list the ranges "BRAND1", "BRAND2", "BRAND3" and "Type1". and the drop-down in B2 will list the items contained in the 4 ranges using the list  =INDIRECT(A2).



@ Variatus 
the drop-down in A2  should  show  AA,BB,CC,DD   then  when select AA  should  show  in   COL B  ASD1     and  if   I  select    BB should show ASD2  and so  on 
MAKLIL (rep: 14) Mar 28, '21 at 8:32 am
The drop-down in A2 must show the name of an existing named range. I have shown you how to do it. Now it's your turn to execute. If we always do it for you you will never learn.
Simple: If you want "AA" to be in A2 you need a named range called "AA", and the list from that range will appear in B2.
Variatus (rep: 4544) Mar 28, '21 at 8:45 pm
before    I add  my   discussion  actually       I    amended  and  named  the  range  AA   but   I no  know    where  is  my  mistake    it  doesn't  work   but  after   I  read    your  last  comment       I  completly  agree  with  you  about  I  should    do   the  steps   myself    logicly    if  you  did  it  for me   I don't    take  advantage  at all  and becauase    I need   how  do  that   to   I    able  to  adapt   what    I need   , finally     I     follow   your  steps    again  and   works  excellantly  ,   so   your  answering  is  winner  because  it's  efficiant  and  doesn't contain more  formulas  or  codes   
many  thanks  for  informe  me  what  I should  do
MAKLIL (rep: 14) Mar 29, '21 at 2:25 pm
Thanks for your feedback. Teaching is a rewarding business and you gave me the reward. Have a great day!
Variatus (rep: 4544) Mar 29, '21 at 8:33 pm
Add to Discussion


Based on your discussion point below, I suggest you delete the data validation formula (for B2 in Sheet 1) and in that cell add the formula (with inverted commas within the INDIRECT part) as follows:


You'll then see the type related to the brand in A2 will then appear in B2 (e.g. ASD-4 if A2="DD"). Note that the named range BRAND is already defined in "sh".

(The second argument of INDIRECT defines the referencing syle; luckily you're using A1 style referencing which is the default so you don't need to specify TRUE for the second argument).

Actually INDIRECT does nothing in that formula. This works the just the same:


Revision: I hadn't spotted that the tables in sheet "sh" had unequal numbers of Brands. The attached file has a single table (with named ranges BRAND and TYPE) so the formula in B2 is simplied to: 


As you add rows directly below the table, it expands and the selections in A2 include the new BRAND row (and B2 can show the new TYPE) since data validation in A2 now refers to BRAND.

EXTRA (works only in Excel 365): I've now added a second workbook (T with table and data validation v0_b.xlsm) where SHEET1 allows you to set the Brand in A3, the chose from the types in B3 (data validation limits those to the items matching that in the table in sheet "sh" under the heading Product data). The price from that table is also displayed (and if you put a quantity in D3, the total appears in E3. Change B3's choice to AA (from the  dropdown list) and you'll see the products in the dropdown of B3.

The data validation in B3 is however quite complex. The Custom formula fro data validation is simply:


That refers to sheet "sh" where, under the Data validation heading, cell G3 has the (array) formula:


where, in turn, cell G2 is the formula:

That produces a text list of matching products like ASD-2,ASD-7,ASD-8,ASD-9 for BB. the FILTERXML... formula creates the array from G3 down, a series of matching types. 

Note that in SHEET1, the data validation formula now refers to the (named) table under Master brand list in sh. This has it's own data validation (to prevent you adding the same brand a second time (i.e. they are unique values). Under Product data, ydata validation prevents you adding a line (or chnaging a line) to a brand which is not already in that Master brand list in sh- that way cell A3 will always have the brands list (with other details) under Product data.

Also I had to add a simple workbook event macro (below) to force the selection in B3 to be blanked when A3 changes (otherwise you'd get a price for a type not associated with the new Brand selection):

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A3")) Is Nothing Then Exit Sub
Range("B3").Value = ""

End Sub

Hope this is what you need.



thanks  ,to  understand what  I'm trying  about  your suggestion  I  tried  but  it  gives me  the  same  values  in COL A   but  it  should  gives  me  the  value  in COL B so  if  I select AA should  show ASD1and  if   I select  BB should  show ASD2  and so on 
MAKLIL (rep: 14) Mar 28, '21 at 8:39 am
Thanks Maklil.

Please see my revised Answer.
John_Ru (rep: 1002) Mar 28, '21 at 9:49 am
many  thanks  But  I forgot   mentioned    this    and  I'm sorry about it someties  repeate   the  items in  COL a in sheet SH  for  instance   AA repeat twice   and  contain   in column  B  ASD1  and  ASD6  then  when  select  AA in COL A  then  the  formula  just show  in  COL B  ASD1   and  ignore ASD6  in SHEET1
MAKLIL (rep: 14) Mar 28, '21 at 10:15 am

Hope my second revision makes sense/ does what you want.
John_Ru (rep: 1002) Mar 28, '21 at 11:01 am
thanks  again , you  seem  to you  don't  understand  me   I'm talking  about   if  the  item  repeat  more  than  one  time   ,  let's  suppose  in  row2 in col A  is  AA  and  COL  B is  ASD1    and  if  repeat   again  in  row 6   in COL A  is  AA  and  COL B  is ASD6   then  in  sheet1   when  select AA   in COL A should  show  two  choices  in COL B    ASD1,ASD6 .
by  the  way   I prefer  rivision   the  first  answer   it's  perfect  just  this  part   which  explained  for  you  it 's  the peoblem 
 I hope   you  understand  me 
MAKLIL (rep: 14) Mar 28, '21 at 3:01 pm
I'm not quite clear. Please explain how you need to use this in the real world (e.g. do you want to find all of the types offered per brand, possibly as data validation choices again) and I will look at it tomorrow.
John_Ru (rep: 1002) Mar 28, '21 at 3:24 pm
I added  a new file   calls  T1   please  check  it     I  put   some  results   in COL A,B  and D,E and  G,H  in sheet1    when  select  item  from   COL A,D,G   then  should  show  many  choices   in COL B,E,H  based on  repeated items  in COL A in sheet SH to  understand  it   compare   each   two  columns  togethers  in  sheet1   with COL A,B in sheet  SH      I put  some  repeated items     becuase  this  the  problem       if  it's  not  repeated   it's  ok  it  works  as  your  first  answering
MAKLIL (rep: 14) Mar 28, '21 at 3:48 pm

Please see the text starting EXTRA in my Answer (and the additional file)
John_Ru (rep: 1002) Mar 29, '21 at 10:26 am
first  I would  thank  you   about  following  for  my  problem   but   I  think  you  forgot    I  use office version 2016   and    I note   you  use  two functions (FILTER & TEXTJOIN)    I  don't think  theses functions  are  available  in  this  version   it  gives  me  error   in  COL G  after   I select  brand  in COL A   in SHEET1     then   I can't   select  from  TYPE
MAKLIL (rep: 14) Mar 29, '21 at 11:39 am

Sorry about that, I moved to Microsoft 365 and forgot that those functions weren't available to you with Office 2016. I don't think I could aceive the same in Excel 2016 (without spending much more time).

I'll edit my Answer to refer to Excel 365 (for other users).
John_Ru (rep: 1002) Mar 29, '21 at 12:21 pm
I've editted my Answer (to make that reference)
John_Ru (rep: 1002) Mar 29, '21 at 12:23 pm
thanks  very  much    for  your  time  and  assistance  actually   after   many  tries   about  found  my  problem   with   Variatus' answer   I've  found  how  works  despite  the  steps  are  simple  and  clear   I no  know  why  It takes from  me  more  time  my  apologies  if  I  make  disturbing you
MAKLIL (rep: 14) Mar 29, '21 at 2:32 pm

I'm not clear what you're trying to say, sorry.

I just read your commnent on the solution from Variatus. I'm pleased that you could use his method (or at least I think that's what it means).
John_Ru (rep: 1002) Mar 29, '21 at 4:21 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login