Pulling Worksheet Data from Many Tabs

[Landzir101]

Not exactly sure what this is called or what is needed, so I apologize in advance if there is a thread devoted to it (please direct me to it if that be the case!). But what I am trying to do is populate one worksheet (in the same workbook) with data from many (ie 500) separate tabs. An example is as follows:

On the summary data worksheet, we will call Price Highs, I am trying to return one column of data from each of the 500 worksheets (aka their Price Highs). This is so that I can have each tabs (symbols) data next to one another for easy formula dragging and analyzing. Up to this point I have been using the Find/Replace function, but with this many worksheets I would obviously have to do it 500 times!

Wondering if there was a Macro/Code/Formula that is available to do such tedious work? Any help is greatly appreciated! (Just ask if you need more of an example or information)

 

[Darren Bartrup]

Taking a second look at the images now they're up, it looks like the values in column A are dates, although Excel doesn't recognise that date format.

=INDIRECT(ADDRESS(MATCH($A4,INDIRECT(B$3 & "!E:E"),0),MATCH($B$1,AA!$1:$1,0),,,B$3))

This will work when pasted into cell B4, if you enter the dates in column A in a recognised format (e.g. 6/29/2009).

You can also change the value in cell B1 to Open, Low, Close or Volume and it will show those figures.

Edit: Just remembered, INDIRECT is a volatile function. May slow down if used lots, so may be able to get the same result using INDEX instead?
Too tired to figure it out at the moment.