I have 3 columns of data.
Col A contains a file Pathname
Col B contains a File Name
Col C contains a Cell Reference
I want to create a formula in Col D so that the result is =A & B & C i.e. It will create a link to that specific cell address.
But I don't know how to do it.
Can anybody help please
Will the target files be open or closed?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Closed would be easiest
In that case, you have no alternative but to use a macro-enabled file. Does that cause problems? All these approaches tend to be rather slow. Will you be wanting to copy across many cells?
Macro Enable is fine. The idea is there will be about 50 entries in the first 3 cols so I will want to copy down for all of those. Many thanks
Take a look at this first. Once the sample is doing what you want, I'll explain it...
Many thanks for that. I get the idea but I have to go out now for a couple of hours so will look at it when I get back. Cheers
Sure. Of course, change the path to reflect your machine!!
OK. I've changed the path to my name and the filename and the sheet number where the data is to sheet 7.. However I'm now locked in Excel with a message saying something about "Excel is waiting for another app to complete an OLE action"?
That's a new one on me!! No idea.... Did you create a sheet 7 on the file called "Source.xlsx" or have you leapt ahead 15 steps and started to apply this to your real sheet??? That is NOT what I told you to do....
Please check it out on MY files first.
In the meantime, to get out of the loop; CTRL+ALT+DELETE to launch task manager and kill Excel ...
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
ooops - sorry I thought I had to change the file name as well. It works fine with your Source File!
Right..... Where next? Using my destination.xlsm, try adjusting path, etc, to suit your equivalent of source.xlsx. Is your equivalent of source.xlsx on a network, or on your PC somewhere?
OK. So I had already changed the Path as I had saved Source and Destination into the same folder I am working in. When I did only that, everything works. So then I changed the file name to be the file I want to use but I I left the formula the same. When I did this, the values in the formula all changed to 0 . Which part of the formula is referencing my source file Sheet Number as I think I can follow what is happening but how does your formula know to pick up the values from Sheet 4 of the Source file? And thank you for being patient!
ps. Everything is on my PC but I'm doing this for a friend and it will eventually reside on a server.
Good morning. Thank you for your assistance on this problem. My workbook is now working as hoped thanks to the IndirectEx function (although I have no idea how!). However I have one remaining problem and hoped you could advise me. If I change data in the referenced workbooks, the values in the Destination workbook don't change automatically. Is there a way to to make this automatic or some other way to force my destination file to update?
Thanks again
With the thread marked as solved, you may not get a response - you might want to change that.
Thank you.
Yes.... I see your problem. I've only used INDIRECTEX on non-changing files... bringing over big lists based on cell references. I'll have a play at this and get back to you.
Right so. I have looked at INDIRECTEX and another commonly used workaround (Pull). The BOTH seem to have the same issue. Sadly, I am almost completely ignorant when it comes to coding. Maybe someone else can help!!
The only thing that I have found that works is to drag the formula from the first cell down to the last.... and then drag it back up again (otherwise the first cell won't update). All I can suggest is that you close this thread, repost the source and destination files in the VBA sub-forum and see if anyone can come up with a fix for you.
I can do moderately advanced formulae, but am not even in the kindergarden league with VBA!!
Many thanks for your effort. I will do as you suggest. I’ll repost to see if there is a solution but in the meantime I might just write a macro to do the drag down and back if that has the desired effect. More than I be way to skin a cat eh 😄
That's far more than I could do!!!
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Good morning Glenn. Just thought I'd let you know how I solved my problem concerning keeping my file dynamically updated. Eventually I concatenated a field containing the path to the cell on my source data but without the = sign and with xxx at the start. I then used Replace to change the xxx into = and hey presto the result was a formula linking my source data to my desination file. I would never have thought along these lines without your help in trying to get the IndirectEx to work, so thanks again for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks