Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
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 "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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
"TUNGANA KURMA RAJU" wrote:
Thanks Mr.Max, its working fine. Glad it worked ! (Go easy, pl drop the "Mr" bit <g) 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:A 1)-1,COLUMNS($A$1:A1)-1)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
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**, copypaste 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
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 "Max" wrote in message ... "TUNGANA KURMA RAJU" wrote: Thanks Mr.Max, its working fine. Glad it worked ! (Go easy, pl drop the "Mr" bit <g) 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:A 1)-1,COLUMNS($A$1:A1)-1)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
Thanks once again,I am greatful to you.
"Max" wrote: "TUNGANA KURMA RAJU" wrote: Thanks Mr.Max, its working fine. Glad it worked ! (Go easy, pl drop the "Mr" bit <g) 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:A 1)-1,COLUMNS($A$1:A1)-1)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
You're welcome !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TUNGANA KURMA RAJU" wrote in message ... Thanks once again, I am grateful to you. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
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" wrote: 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**, copypaste 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
data extraction
Thanks for posting back ..
Go with whatever works well for you over there <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TUNGANA KURMA RAJU" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ranking query | Excel Discussion (Misc queries) | |||
data extraction | New Users to Excel | |||
Importing Data | Excel Worksheet Functions | |||
Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |