Variable link in a reference

[Vincent_df]

Hello everyone!

I hope someone here can help me... I want to make an overview file which gathers data from different excel files. Just one problem: due to historic reasons we create a new file each day, so if I want to create an overview of one year i have to enter 365 references, which is a bit much.

Cell A1 contains a formula like this:

Please Login or Register  to view this content.

linking to the file where i want to get one number.

Cell A2 will be the same reference but with the date changed (it's always in the same location and same format):

Please Login or Register  to view this content.

and so on... Is there a way I can make inserting the reference automatically? So that I don't have to enter it until 2017 and beyond?

I tried something with concatenate but then it's just text and no longer a reference...

[6StringJazzer]

I think you're going to need to do this with macros. You can use INDIRECT but that would require the 365 files to be open for the formulas to update and I am guessing that is not practical for you.

My approach would be to write a macro that writes the formulas into the corresponding cells for you. It could update the file to add formulas up to the current date. You do not want to run this code unless the referenced file exist! If a file does not exist then Excel will prompt you to locate it when you try to populate the formula.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Here's another approach using the Edit Links functionaility

Put your A1 formula in another cell and name it "LinkFormula"

Now in C1:C365 list all your file names

Then run the following macro

Please Login or Register  to view this content.

Obviously edit the path "C:\Users\Richard\Desktop" to your path
The G6 value will be listed in A1:A365

[Vincent_df]

Hey both of you thanks for the fast reply.



I've only tried Jeff's way for now and it works (although I indeed have to cancel the prompt on sundays (no file on sundays :D) but that is not an issue. )

This helps me insert all the data up until today but tomorrow I still have to manually add the new data (and this every new day), or run the whole macro again (it took quite some time).
First part of the solution would be to create an 'update' macro just for the previous week.

Please Login or Register  to view this content.

But then he would overwrite existing data in cell A1. And even if I'd change the macro so he adds it at the bottom of the list he would create duplicate values for all the data already in it. and still prompt me for sundays

[6StringJazzer]

[...]I indeed have to cancel the prompt on sundays (no file on sundays :D) but that is not an issue. )

You did mention 365 days. The code can be modified to exclude Sundays.

This helps me insert all the data up until today but tomorrow I still have to manually add the new data (and this every new day), or run the whole macro again (it took quite some time).
First part of the solution would be to create an 'update' macro just for the previous week.

Please Login or Register  to view this content.

But then he would overwrite existing data in cell A1. And even if I'd change the macro so he adds it at the bottom of the list he would create duplicate values for all the data already in it. and still prompt me for sundays

It can be modified to just add days at the end of the list. I am surprised that it took quite some time because for me it was maybe 1 second.

I don't have time to update now but will get back to you.

[Vincent_df]

Company servers; not that fast.

I tried to exclude sundays but I just created a lot of syntax errors I'm afraid. As to the part about adding it to the end, I have no clue how to start there.
In any case, thanks alot for the first step already!

Vincent

[6StringJazzer]

Here is an update that will skip Sundays. It also starts in the next available row to fill up to today's date, rather than starting from the beginning.

However, I'll say again that this takes under a second for me to run for a whole year, so I don't know why you are experiencing "it took quite some time". What OS and hardware are you using?

Please Login or Register  to view this content.

[Vincent_df]

Wow, thanks. I can understand the code although I'd never be able to make it myself without VBA training. (or any language training for that matter).

I'm running a company laptop, 64bit Win7, 8GB ram, Intel i5 CPU (2.7Ghz) which is more than fine but the C:/ drive is on the company network, which is tremendously slow . (my local drive is L:/)

I'll try the code now. Already a great thanks for the help!



First run it works great! second run, when there already is data, it gives a Run-time error '13' Type mismatch. Debugger put this in yellow:

Please Login or Register  to view this content.

OR the next time it puts this in yellow:

Please Login or Register  to view this content.

[Vincent_df]

I have tried to change a few things but can't seem to find the reason for the type mismatch. Anyone?

[6StringJazzer]

I have been very busy the last few days but I will take a look at this tomorrow if nobody else drops in. I tested it before I gave it to you and I did not have these problems, but I'll try a few different things to see if I can get the same error.

[Vincent_df]

Thanks it happens when i load data in an empty sheet and then delete the last few entries to simulate only running the macro every few days. Then when I rerun i get the fault.

I've changed the syntax of the arguments into alternative ways of saying the same but i just create more errors

[6StringJazzer]

I do see an error in the code that will cause your problem. I also see another error. Changes highlighted.

Here is a revised version of the code which I tested in a setup like yours. Because C is a network drive, the I/O latency is going to make this slow as Excel verifies the external references when they are created. Suppressing screen updates might help, but I don't have a networked drive I can test this on to find out.

Please Login or Register  to view this content.

[Vincent_df]

the missing ' I had already corrected. Your other changes have indeed resolved the issue !!! Thanks so much


V