-1

How can I get data from named ranges together in another tab to show results from a sheet with data using a drop down menu to chose a name and get the range of the named ranges?

The example sheet.

As you can see, the data sheet contains all data and the value tab should be the one where I use a name from the drop down list to get the results next to it which is not working because I am missing the part how to do that.

Community
  • 1
  • 1
Errious
  • 53
  • 1
  • 10
  • Do you have a tut where i can see how this works ? And in gdocs you have this kind of drop down menus without programming, but i remove the tag, sorry. – Errious Jun 09 '18 at 18:19
  • There are differing numbers of elements associated with each drop down. You would probably design a front end that catered to the selection with the greatest number of items and then use Index Match and potentially offset formulas along with Iferror so retrieve items from the second sheet – QHarr Jun 09 '18 at 18:46

2 Answers2

2

It might not be the simplest way, but for your example sheet you can use this formula:

=ARRAYFORMULA(indirect("data!B"&match($B$2;data!$A:$A;0)&":C"&match(VLOOKUP("*";indirect("data!A"&match($B$2;data!A:A;0)+1&":A"&match($B$2;data!$A:$A;0)+100);1;FALSE);data!$A:$A;0)-1))

What it does is it finds the exact phrase chosen from the dropdown list and gets the row number of that cell. This row number is then used for the first part of the arrayformulas reference.

To get the last row number for the arrayformula, it searches the next 100 cells in that column for the next non-empty cell. When the next non-empty cell is found, it uses the row number of it and subtracts 1 to get the other reference for the arrayformula.

After getting both the row numbers it combines them to the arrayformula with the "indirect" function.

Creepster
  • 61
  • 3
  • Wow, this is very good so far, only one little thing, seems the last request from the drop down is not working, is this because of the next 100 cells ? – Errious Jun 10 '18 at 00:51
  • Add any text, like 'end', to the end of data column A (row 2636) and it works for the last request. – Ed Nelson Jun 10 '18 at 14:23
  • I already realized how to... but thank you very much, sometimes you sit on a problem and you can not longer see the easy things :-) – Errious Jun 10 '18 at 18:25
0

If you want to use named ranges like you have set up for some requests, you can use this in C2:

=indirect(B2)
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29