Can I automatically reference the same cell in 30 diff files

[malkhazov]

I have two somewhat related questions.

Q1. I download data into an Excel spreadsheet from an outside data provider every market trading day. The spreadsheet never changes but the name of the file changes everyday (new date = new name). At the end of the month, I need to string together daily figures into one monthly figure as part of a return calculation. Currently, I either cut and paste manually or link to one of the daily files and manually cange the name of the file in the link for all the other dates. Is there someway to write a formula or VBA code that will automatically reference the same cell but in 30 different Excel spreadsheet (maybe someway to select a group of files and ask Excel to get the value the same cell in all these files).

Q2. I have a single Excel spreadsheet that has a bunch of tabs(worksheets) that are identically formated but the data changes everyday and ofcourse each tab has a different name. I also have a summary page that has to show a value in the same cell for each of the tabs in the spredsheet. I currently either cut and paste or link to the cell in one tab and manually change the name of the tab in the link about 25 times. Is there an automatic way to do this. The number of tabs in the spreadsheets fluctuate between 20 and 25 on a monthly basis.

 

[jdc]

Re: Can I automatically reference the same cell in 30 diff f

Hi

Just clarifying your description of your problem, re question 1, is the spreadsheet that has a new filename each day, is it just one worksheet or several.

If it was just one worksheet, I would attempt to copy and paste the contents of it into a new workbook with a worksheet for each day.

Regarding what you mention as "string together daily figures into one monthly figure" do you mean a total from each sheet with a Grand total on the final sheet?

If this is so, I would make sure the cells for each column total are at the top of each column and in the same position on each row of each worksheet, then the easy part for doing the total would be by selecting all the sheets except the last sheet, the total sheet and inserting this formula in the first cell that relates to the first column.

=SUM(Sheet1:Sheet25!A1)

then drag the formula across.

For Question 2, you can create a list of all the tab names using this code, it needs to be on a fresh new sheet at the beginning of the workbook because it'll overwrite anything in column 1.

The following code will provide a list of tab names that you could create a list of links with and then transpose them across a row if you wish, the code follows:

Code***********************

Sub CreateIndex()

Dim WS As Worksheet
For Each WS In Worksheets
Cells(WS.Index, 1).Value = WS.Name
Next WS

End Sub

*******************************

Insert this code on a worksheet rather than on a module.

HTH

John

 

[Von Pookie]

Duplicate post. Please keep to one thread:
http://www.mrexcel.com/board2/viewtopic.php?t=127220