Anyone here good with Excel?

[fantolay]

I am trying to copy and paste data from a number of excel spreadsheets into a separate sheet.

I'm researching stocks, and each stock has an excel sheet you can download with a number of key metrics about that stock. They are organized by what the statistic is on the rows, and then the columns are for which quarter.

Of the massive amount of data on each of these sheets, I am only trying to copy and paste info from like 8 cells into my separate excel spreadsheet. Because each of the spreadsheets about each stock is formatted in the same way, so the cells I am copy and pasting are in the same location in each spreadsheet, there has to be a fast way or some macro to automatically copy all of them at once without me having to individually do it.

Can anyone shed some light onto this?

 

[Albatross]

http://www.ozgrid.com/forum/showthread.php?t=144550
http://stackoverflow.com/questions/...s-from-different-workbooks-into-one-final-des

 

[ringtail]

Since you have only a small quantity of cells, just do this manually.
If the source (stock data) sheets are the same layout from day to day, then you only need to set this up one time, save it, and in future it' should update itself.

Open both your destination and your data siurce worksheets.

go to your DESTINATION spreadsheet and in each cell where you want to bring data from a source sheet,
enter an equal symbol and then go to the source sheet and click the cell to be imported, then click Enter.

You wind up with a formula in each of your Destination sheet cells (which Excel will automatically create) that looks something like this:

='[SourceSheetName.xlsx]'!$E$80

 

[postmark]

I am trying to copy and paste data from a number of excel spreadsheets into a separate sheet.

I'm researching stocks, and each stock has an excel sheet you can download with a number of key metrics about that stock. They are organized by what the statistic is on the rows, and then the columns are for which quarter.

Of the massive amount of data on each of these sheets, I am only trying to copy and paste info from like 8 cells into my separate excel spreadsheet. Because each of the spreadsheets about each stock is formatted in the same way, so the cells I am copy and pasting are in the same location in each spreadsheet, there has to be a fast way or some macro to automatically copy all of them at once without me having to individually do it.

Can anyone shed some light onto this?

I would setup a macro that would scan your input file directory (where you download the stock files to) and then get the data you want from each and dump it into your current excel sheet. I would need more info on what you need to get it running though.

 

[Binky]

You could do this a little more dynamically with an indirect formula, which you could use to create easily changeable links to the data you download. The indirect function requires the links to be valid (unlike normal external links that can go without updates and the old values are still there), so this is not useful if the data files will be deleted or moved.

For example, you could set your 8 formulas in a single column to pull data from a file called MSFT.xlsx. The indirect formulas could quickly copied to other columns and instantly changed to link to INTC.xlsx.

http://www.cpearson.com/excel/indirect.htm