Thanks Mr.Max ,its working fine.Is there any other way to switchoff display
of zero values ?.Because in my sheet A:N columns contain certain functions
that yeild zero value,and by unchecking zero values via the route you
suggested ,my entire sheet zero values becoming invisible.Pl suggest the
other method to to tackle the zero values from M1:S1250.Thanks once again.

"Max" wrote:

One way

A1 contains the DV to select the sheetname
(Ensure the sheetnames match exactly what's on the tabs)

Put in M1:
=OFFSET(INDIRECT("'"&$A$1&"'!A1"),ROWS($A$1:A1)-1,COLUMNS($A$1:A1)-1)
Copy across and fill down as required, to S1250

For a neater look, switch off display of zeros in the sheet via:
Click Tools Options View tab Uncheck "Zero values" OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TUNGANA KURMA RAJU" wrote in
message ...
I am looking for a formula --- to lookup a value from A1(which has a
datavalidation list of sheetnames inthe w/book) and extract data from that
sheet range A1:G1250 to current sheet. example A1=Smith
formula I want in M1=Smith!A1:G1250
If I drag fill handle of M1 all data should come to M1:S1250 range.