linking to a downloaded spreadsheet

[RFJ]

I regulary download a spreadsheet which has a filename format of
Data_Date_Time.xls

For example SurveyData_290905_1105.xls

Each downloaded file has identical column structure but a variable number of
rows.

I have another spreadsheet analysis.xls that does all the calculations.

I'm trying to work out the best way of being able to download the latest
data file and then for the analysis to be done on that file - without having
to set up all the ranges and links from scratch.

Suggestions will be welcome (although writing VBA is well beyond my current
position on my learning curve).

TIA

Rob

 

[Bernie Deitrick]

Rob,

One way, without macros:

Open both workbooks, then either expand the number of rows in your file analysis.xls (if there
aren't enough) by inserting rows within the referenced ranges - by this I mean that if you have a
formula that references A1:A100, and you need 200 rows, select cell A99 and insert the 100 needed
rows so that the reference in the formula will become A1:A200. Then copy the data from your survey
file, and paste it OVER the data existing in the analysis file.

Check the formulas in your calc'd cells to ensure that their references were properly updated. Once
you become comfortable with the technique, it is fast and easy.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Open the file you are downloading to and go to EDIT/LINKS and you can change
the name of the file you are linking to in one easy step for the entire
worksheet. Your worksheet has to be in the Unprotected mode.

 

[Pete_UK]

Imagine in your Analysis file that you have a sheet with the
calculations on it and another sheet (let's call it DATA) which has the
latest downloaded information, such that all the calculations in the
Analysis sheet refer to the DATA sheet.

Once you receive a new download file, open this and the analysis file
in the same Excel window and use Window | Arrange | Horizontal to see
both files. Drag the tab for the sheet from the latest download into
the window for the analysis file - this will move the sheet into the
analysis file and will close the download file window (if it is the
only sheet).

Rename the sheet you have just copied in, eg to DATA_1, then select the
calculations sheet, click on the intersection between the row and
column identifiers in order to highlight every cell, then CTRL-H (Find
& Replace) and enter:

Find What: DATA!
Replace with: DATA_1!

then click Replace All.

You can now delete the original DATA sheet, then rename the DATA_1
sheet to DATA - the formulae will adjust automatically.

You can avoid having to resize all the ranges in your formulae by
making them large enough to accommodate all the rows that you might
expect, eg if you have A1:A250 in one formula, then make it A1:A1000.

Hope this helps.

Pete