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)

 

[ellisbobby]

I believe there is a way. I'm a novice at excel but maybe this will work for you. I have a workbook that has a tab for each day of the month in it. One sheet shows the monthly totals. I take the monthly totals from each day in the workbook by using this formula:

=SUM('05-25'!C2)

I take the data by clicking on the cell I want the info in, then I click the function button. Click SUM then I switch workbooks and go to the first one I want added. Then I hold down shift and click the very last workbook I want added. This takes the sum of all the cells in the workbook that from cell C2.

Is that what you're looking for?

 

[Landzir101]

Not quite. It would perfectly if when you were to drag the fill handle to the right it would have the actual TABS as the changing cells. Ex: There are 5 tabs named 1, 2, 3, 4 & 5. You set the cell in the summary TAB (separate from the five...so the 6th) to a specific cell in worksheet 1 (=1!A1 for example)...what I NEED it to do (which it obviously doesn't the way I am trying to do it) is that say you were to drag the cell with the formula in it to the left, it would then go from 1! to 2! and capture the values across the tabs streaming from left to right.

I do not believe a formula can do this...but in fact a macro would have to be written. Thanks for your help though! Any other ideas shoot em by me!

 

[Fazza]

Hi Landzir.

Please post some sample data and further description to clarify the requirement.

Regards, Fazza

 

[Landzir101]

Here is a picture of what I am trying to do/data:

As you can see I simply populate a master spreadsheet with a certain cell from all the other spreadsheet (in this example it is the High Price). Since each high price is located on a different tab, I was looking for an easy way to grab the data from the tabs...without having to type in each symbol / find & replace.

Each tab the data is coming from looks like this...

Let me know if this helps or if you need more!

 

[Landzir101]

I know it is something along these lines, but I am just not smart enough to figure it out

Sub GetData()
'
' GetData Macro
'

'
ActiveCell.FormulaR1C1 = "=AA!R[-2]C[5]"
Range("B4").Select
Selection.AutoFill Destination:=Range("B4:B31"), Type:=xlFillDefault
Range("B4:B31").Select
Range("C4").Select
ActiveCell.FormulaR1C1 = "=AAPL!R[-2]C[4]"
Range("C4").Select
Selection.AutoFill Destination:=Range("C4:C31"), Type:=xlFillDefault
Range("C4:C31").Select
End Sub

What this does is simply populate the column for AA & AAPL from their respective tabs. What I do not know how to do is make it more broad in scope. For instance to get the macro to read cells in B3, B4, B5...etc (where the tab names are) and then populate the column underneath it...

 

[Fazza]

Landzir, I only see an image holder for the posted image. Can you try again using an HTML add-in from a sticky thread at the top of the page? I do suspect what is required isn't too difficult, but I don't understand the setup and details well enough to offer an answer. You might check if using the INDIRECT function helps. Regards, Fazza

 

[swat2]

I still can't see the image, but I assume you have sheets with Tickers in them and in each of those sheets you need to find a max value or something of the sort. Then you want to take that max value and put it into a summary sheet beside it's designated ticker.

If you have a list of all your sheets or ticker names in "column a" of the summary sheet, you can use the "indirect" formula to refer to them in a formula.

Here's mine, I put in this formula in "column B" in the summary sheet beside the ticker names in "column A": it finds the maximum value in A1 through A26 on the sheet specified in A1 (in this instance IBM or CIT) or something.

=MAX(INDIRECT(A1&"!A1:A26"))

 

[Darren Bartrup]

Getting a bit late here, so haven't worked out a full solution and can't see the pics you posted.

I take it your sheets follow a naming convention, such as Sheet1, Sheet2, etc...
This will put a reference to Sheet1!A1 if put in column 1, Sheet2!A1 in column 2, etc..

=INDIRECT("Sheet" & COLUMN() & "!A1")

As I said, not a complete solution but might help

 

[Darren Bartrup]

Managed to download your images - checked the element and grabbed the url from there.

=INDIRECT(F2 & "!G2")

Put in cell B4 and drag across.