Search a range with 3 tables according to dropdown value

0
votes

I'm quite new to macros/formulae of excel. I have a sheet (Sheet1) exported which has the approvers list.

  • I also have 3 tables ( A, B, C)

In Sheet2 which has like below values:

  • Table A: {aaa,bbb,ccc}
  • Table B: {xxx,yyy,zzz}
  • Table C: {d12,e12,c12}

I need to search Column Q in sheet1 with either one table as per the dropdown menu.

If I select Table A, then I need to have all rows with text aaa OR bbb OR ccc in Column Q. I used the below formula, but I can search only one table at time.

I can't select through dropdown menu.

=SUMPRODUCT(--ISNUMBER(SEARCH(A[ID],Q51)))>0

This returns true or false. I have to keep changing the formulae for each table. Can I do that through drop down menu.

1
vbaexcel

1 Answers

0
votes

Use INDIRECT : place INDIRECT(Right(myRange, 1) & "[ID]") in the place of A[ID].

myRange is the address of the cell where the drop-down is placed; i.e. could be D1 for example, and the formula becomes:

=SUMPRODUCT(--ISNUMBER(SEARCH(INDIRECT(Right(D1, 1) & "[ID]"),Q51))) > 0