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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"suraneniaditya" wrote in message
...

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