Can I automatically reference the same cell in 30 diff files

Status
Not open for further replies.

malkhazov

New Member
Joined
Jan 26, 2005
Messages
49
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

jdc

Active Member
Joined
Nov 23, 2004
Messages
263
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
 
Status
Not open for further replies.

Forum statistics

Threads
1,147,508
Messages
5,741,571
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top