working "down" through worksheets

B

brian mcnamara

good morning. I'm looking for a function or macro to combine info from
various spreadsheets and return pieces of each to a diifferent
worksheet. worksheets A-C are account number worksheets. value in
column A1-A25 are sub account-numbers. I would like worksheet D to
"look in worksheets A-C, look in columns A1-A25 and return specific
values. I'm getting stuck on having excel move successively from one
worksheet (only through the last populated cell) to the next worksheet
(also only through the last populated cell) throught the next
worksheet. Any thoughts?

-Brian
 
P

Pete_UK

You can probably use vlookup to do this, although you will need to
supply some more details if you want some specific help. In worksheet D
you will need to enter an account number (eg in cell A1), then in B1
you could have a formula along the lines of:

=IF(ISNA(VLOOKUP(A1,'Sheet A'!$A$1:$B$25,2,0)),
IF(ISNA(VLOOKUP(A1,'Sheet B'!$A$1:$B$25,2,0)),
IF(ISNA(VLOOKUP(A1,'Sheet C'!$A$1:$B$25,2,0)),"Not found",
VLOOKUP(A1,'Sheet C'!$A$1:$B$25,2,0)),
VLOOKUP(A1,'Sheet B'!$A$1:$B$25,2,0)),
VLOOKUP(A1,'Sheet A'!$A$1:$B$25,2,0))

This will return whatever is in column B for a match with column A in
either sheets A B or C, otherwise "Not found" is returned.

Hope this helps.

Pete
 
B

brian mcnamara

Pete,

Thanks for the suggestion. I've considered this route (although not to
this extent) but with 225+ worksheets the IF statement becomes pretty
extensive. I tried to tailor down my description for lack of providing
too much information. Will Excel understand 'Sheet B'! +1? the
worksheets aren't sequential by any means but do fall within a range,
per se. Let me try explaing it one more time as I don't think I did a
proper job the first time.

given worksheets 1-225 exist each with the same cellss A11:A300. Cell
A8 is unique to EACH worksheet. Column A contains numeric values that,
again, are not sequential but do fall within a defined range. column B
and C will eventually be pulled into the "grouping" worksheet using
VLOOKUP for sure. Grouping worksheet will return each worksheets Cell
A8 and Cell A11, cell A8 and Cell A12, Cell A8 and Cell A13 ,etc...
With each worksheet being uniquely named, I'm trying to find the most
efficient way to "run through" all of the worksheets and return the
necessary values/columns/cells. Movement from one worksheet to the
next seems not as straightforward as it does from one row (or column)
to the next. Is this any clearer?

-Brian
 
P

Pete_UK

The INDIRECT( ) function will allow you to retrieve data from an
address passed to it (expressed as a string), so you could build the
string up in one column then use this to be passed to INDIRECT. Assume
your sheets are numbered in some way, eg Sheet 1, Sheet 2, Sheet 3 etc
and you have one summary sheet called "Grouping". Just to show it
working, enter "Sheet" in A1 of Grouping, "Cell" in B1, A8 in D1 and
Other in D1 as headings, then enter the following formulae:

A2: ="'Sheet "&(ROW()-1)&"'"
B2: ="!A"&(ROW()+9)
C2: =INDIRECT(A2&"!A8")
D2: =INDIRECT(A2&B2)

There is an apostrophe between the two " in A2 formula. Copy these down
and in column C you will retrieve the value from cell A8 of each sheet,
whereas in column D you will retrieve A11, A12, A13 etc from the sheets
in turn, as requested. Maybe you can adapt this to suit what you want
to do.

Hope this helps.

Pete
 
Top