finding data-build a formula

[formulaidiot]

I have a workbook with 22 worksheets in it.

21 of the worksheets have data in rows 3:203 and columns A-C.

I am trying to build a formula on the 22nd worksheet that looks at the other 21 worksheets for information in column B and if column B = my criteria it copies the information from column A to worksheet 22.

Thanks in advance.

[NBVC]

If you download and install the free Morefunc.dll addin from this site: http://xcell05.free.fr/english/ you will be able to use Excel's native Vlookup() function together with the special THREED() function to look over multiple sheets.

something like:

=VLOOKUP(A1,THREED(Sheet1:Sheet21!A1:B100),2,FALSE)

If you are looking for items in columns A where columns B match your criteria, you can use an Index(Match()) combination...

e.g

=INDEX(THREED(Sheet1:Sheet21!A1:C100), MATCH(B1,THREED(Sheet1:Sheet21!B1:B100),0),1)

[formulaidiot]

If you download and install the free Morefunc.dll addin from this site: http://xcell05.free.fr/english/ you will be able to use Excel's native Vlookup() function together with the special THREED() function to look over multiple sheets.

something like:

=VLOOKUP(A1,THREED(Sheet1:Sheet21!A1:B100),2,FALSE)

If you are looking for items in columns A where columns B match your criteria, you can use an Index(Match()) combination...

e.g

=INDEX(THREED(Sheet1:Sheet21!A1:C100), MATCH(B1,THREED(Sheet1:Sheet21!B1:B100),0),1)

did the install, not sure this is what I am looking for, or I don't know how to use it (see user ID)

specifically on sheet 22 (which is titled "P4") I want to pull the data from the row in column A of the other 21 sheets where column B of that row is = to P4

[NBVC]

This formulation should do it

=INDEX(THREED(Sheet1:Sheet21!A1:C100), MATCH("P4",THREED(Sheet1:Sheet21!B1:B100),0),1)

You need to adjust the ranges A1:A100 and B1:B100 to ensure all data in all sheets are covered... you can't use whole column references, though, like A:A and B:B.....(unless you're on 2007). You also may need to change the sheet names to match your actual first sheet name to last sheet name.

Also, the "P4" can be replaced with a cell reference in your summary sheet that house that string.

[formulaidiot]

ok, we're getting close...

1. do my sheets need to be labeled sequentaially or does it depend on the tab order?

each sheet is labled according to a system so the numbers are not sequential and could be letters (i.e. 053, 099, 169, MPF, etc.)

2. each sheet has up to 200 cells, if I have a total of 10 P4's among all the sheets at different column/row locations would the corresponding data in column A for the P4 row appear sequentially in the block I have this formula?

3. what would be diffeent if I wanted to look at a single sheet instead of all of them?

[NBVC]

1. The sheetnames don't have to be labeled special. The formula refers to the sheetnames as a range...so from leftmost to rightmost...

e.g
=INDEX(THREED('053:MPF'!A1:C100), MATCH("P4",THREED('053:MPF'!B1:B100),0),1)

2. This formula will only pick up the first match it comes across starting from the left-most sheet.

3. If you were looking at one sheet only, say sheet '053', then formula would be: =INDEX('053'!A1:C100), MATCH("P4",'053'!B1:B100),0),1)