vlookup from a list and return the range

[suraneniaditya]

Dear all

I have a sheet with as many as 50 named ranges in sheet 1. These names
are in a drop own list put in data validation in sheet 2.

I need to get the entire range to be returned for the name selected in
the drop down.

For eg: If I select JOHN in the drop down I Should get the range named
JOHN (which may be of 8C X 12R dimension).

I have tried IF & Vloookup combination entered as an array formula but
it is working upto 7 names only. Please help me..

Thanks

 

[Max]

Maybe you meant something like this:
=VLOOKUP(A2,INDIRECT(B2),2,0)
where B2 is the DV cell to select the named range

 

[suraneniaditya]

May be yes. but the result is not a single value it is a named range (
each range contains the details of tax payments relating to various tax
payers. which looks as follows.

Sl No Name Tax ID No Field 1 Field 2 Field 3
Field 4 Field5
1 JOY 5454
2 JOY 5454
3 JOY 5454

the other details like subtotals are to be summarised in to a seperate
template. to be precise this range of data should be returned as an
annexure giving the detailed breakup of the total summary on top.

I tried IF(B2="JOY",JOY,0) nested for 7 names and entered as an array
formula. This is working only for 7 names. but there are hundreds more.


Pls help me

Adi

 

[Max]

Maybe something like this could serve your intents

Illustrated in this sample:
http://www.freefilehosting.net/download/3iajg
Retrieving identical size defined ranges based on DV.xls

Let's say you have set-up defined ranges (Assumed identical size)
Joy =x!$A$2:$H$4
Mary =x!$A$5:$H$7
Peter =x!$A$8:$H$10
etc

In another sheet: y,
In B2 is a DV to select the identically sized defined ranges,
eg: Joy, Mary, Peter, etc

You could select a range equal to the defined ranges' size just below the DV
cell,
eg select B4:I11 (B4 active)

Then paste this into the formula bar, array-enter (press CTRL+SHIFT+ENTER):
=IF(B2="","",IF(INDIRECT(B2)=0,"",INDIRECT(B2)))
The range B4:I11 will be populated as desired, depending on the name
selected in B2