data extraction

[Guest]

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.

 

[Biff]

Hi!

One way:

Select the range M1:S1250

Enter this formula in the formula bar as an array using the key combo of
CTRL,SHIFT,ENTER:

=INDIRECT(A1&"!A1:G1250")

Biff

 

[Max]

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

 

[Guest]

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]

Thanks Mr.Max, its working fine.

Is there any other way to switchoff display of zero values? ..

We could use an IF construct:
=IF(OFFSET(..)=0,"",OFFSET(..))
to return blanks: "" if the OFFSET returns a zero
(but at an increase to the calc load, of course)

Put instead in M1, and fill across/down:
=IF(OFFSET(INDIRECT("'"&$A$1&"'!A1"),ROWS($A$1:A1)-1,COLUMNS($A$1:A1)-1)=0,"
",OFFSET(INDIRECT("'"&$A$1&"'!A1"),ROWS($A$1:A1)-1,COLUMNS($A$1:A1)-1))

---

 

[Max]

In case you might to try Biff's multi-cell array suggestion again. Believe
it works just as well, and ... it's definitely more concise / perhaps more
efficient? <g>, but we need to array-enter it all at once into the range
M1:S1250 as per Biff's steps ..

Here's a slightly revised version of Biff's suggestion (a multi-cell array)
to similarly return blanks if the INDIRECT(..) evaluates to zero:
=IF(INDIRECT("'"&A1&"'!A1:G1250")=0,"",INDIRECT("'"&A1&"'!A1:G1250"))

To register/confirm the multi-cell array formula:

Select M1:S1250**, copy>paste the formula into the *formula bar*,
then press CTRL+SHIFT+ENTER (instead of just pressing ENTER)

**A quick way to select large ranges is to type the range: M1:S1250
into the namebox [box with the droplist just to the left of the equal
sign/formula bar], then press ENTER to select the range

---

 

[Biff]

If you're not already using a specific format such as
Date/Time/Currency.....

Format the cells as CUSTOM 0;-0;;@

Or, use conditional formatting. It would be more efficient than doubling up
on the formula.

Using either of the above, just be aware that the 0's are still in the
cells. You just can't see them.

Biff

 

[Guest]

Thanks once again,I am greatful to you.

We could use an IF construct:
=IF(OFFSET(..)=0,"",OFFSET(..))
to return blanks: "" if the OFFSET returns a zero
(but at an increase to the calc load, of course)

Put instead in M1, and fill across/down:
=IF(OFFSET(INDIRECT("'"&$A$1&"'!A1"),ROWS($A$1:A1)-1,COLUMNS($A$1:A1)-1)=0,"
",OFFSET(INDIRECT("'"&$A$1&"'!A1"),ROWS($A$1:A1)-1,COLUMNS($A$1:A1)-1))

 

[Max]

You're welcome !

 

[Guest]

Hi ! Max,No doubt Biff's multi cell array suggestion is very good,it has
taking lot of time for calculating cells,I tried it ,I used yours formula
with "if" that gives balnk for zero value cells.This one is faster than
Biff's multi cell array formula.My thanks to both of you.

 

[Max]

Thanks for posting back ..
Go with whatever works well for you over there <g>