extract data from one cell in multiple worksheets

[dm2222]

I am fairly new to excel and I am looking for the best way to extract data from multiple worksheets. I have somewhere near 200 worksheets in a workbook. What I would like to do is to extract the number in say "B2" from each worksheet in the workbook and display each of those values in a column of values on a new sheet. Like I said I am very new to excel and I appreciate any help in advance.

[DGagnon]

if you have a list of your sheet names, this can be easily done with indirect, where cell A2 contains the name of your sheet, use the bellow.

=INDIRECT("'"&A2&"'!B2")

[dm2222]

Yes that works perfectly. Thank you! Now, suppose I wanted to display a value that was in a different row in each sheet, but that was the Totals of a list of numbers. Is it possible to put a VLOOKUP in the place of "B2" so that instead of displaying what is in "B2" it will search the 1st column in the table array until it finds (in my case) "Project Total" and then display the number that is in the 5th column of the table? I have tried this and it gives a ref error. (Imagine that) Is this not possible or is there a problem with my formula?

=INDIRECT("'"&B2&"'!(=VLOOKUP($L$2,'23001572'!A14:E43,5,FALSE")

I have the words "Project Total" in L2 and in every sheet, the words "project total" appears in column A, and the value I want displayed is in column E in the row that "project total" appears.

Tell me if I am crazy please.
I am very new to excel, and in searching for answers to my issues I am starting to realize how powerful this program is! (in the right hands ofcourse)

[DGagnon]

sure, you can build it out like this

=VLOOKUP(INDIRECT("'"&B2&"'!$L$2"),INDIRECT("'"&B2&"'!A14:E43"),5,0)