Excel - linking via a cell entry

[cantfindausername]

I'm not sure if this is possible, but my way of thinking is "why not", so thought I'd see if someone here knows how to do it.

I'm making up a template for a report and need it to retrieve data from many other workbooks.

Thats not a problem, or indeed difficult, but can prove time consumming when having to individually pick out the cells that you want retreived.

As this is being handed over to other people I would like to make it as simple to use as possible by automating as much as possible.

So, for an example:

Cell A1 will be another workbooks name with a hyperlink attached so they can click it and see what the document is.

Cell B1 is a certain cell (say C3) from the workbook that is linked via cell A1. So my entry in cell B1 would be: ='H:\Templates\Dummy Project\[BatchJobs.xls]Tests'!$C$3
Cell B2 would be: ='H:\Templates\Dummy Project\[Anotherproj.xls]Tests'!$C$3

I have many different cells that are picked up in the same way as above and from many different workbooks.

-------

So my question is, can the B1 formula be created using the link in A1 so they dont have to go in and manually link each cell they want retrived.

My god I hope this makes sense to someone

Thanks in advance
Ant

 

[xlbo]

Depends if other workbooks will be open

INDIRECT will convert a string to a reference but will not update whilst the workbook being referred to is closed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cantfindausername]

Thanks Geoff. The workbooks will more often then not be closed.

Should probaly have stated that in my original post. Any ideas for that anyone?

TIA
Ant

 

[xlbo]

I guess the other alternative is to write some code that loops through the cells and does the link creating for you - wouldn't be that hard - concatenate 2 cells text together, add an = and pop it in as the formula property of the cell...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cantfindausername]

Thanks. Any ideas where I could get something like that from? or even just some starting point info?

Thanks,
Ant

 

[xlbo]

Sorry - you posted in the VBA forum so I assumed you knew VBA.....??? If you know VBA, I can certainly give you some pointers - if you do not, it may well be worth your while starting to learn as it can make tasks like this sooooo much easier

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cantfindausername]

yeah see I knew VBA would be whats used, but how is where I fall over. I'll start having a read up on it I guess.

 

[xlbo]

Best bet to start off with is to use the "Macro Recorder" (Tools>MAcros>Record New Macro)

Choose to store it in "This" workbook and when you are done recording your actions, press the stop button and then Alt+F11 will open up the Visual Basic Editor where you will be able to see the code generated that will replicate your actions.

Start from there, record what you need to do and someone here will almost certainly be able to show you how to tweak it to be dynamic and work not just for the circumstance you have now but for many that may come up in the future...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cantfindausername]

Thanks very much for the info.