spreadsheet Links

Peter100

Well-known Member
Joined
Apr 21, 2002
Messages
765
I have a workbook named Master.xls which is a master template.
I have workbooks for each month named JanData.xls - Dec.xls

I want to link the data from cell A1 in JanData.xls into cell A1 of
Master.xls which is simple enough.

The link would look like ='[JanData.xls]Sheet1'!$A$1

But what I want to be able to do is replace the [JanData.xls] part of the link
with a cell reference in Master.xls which would be a text entry so that the link
could be changed to read from FebData.xls - MarData.xls etc as desired.

Anyone any Ideas ?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
OK, suppose the cell B2 contains the text:

'FebData.xls

(note the single quote so Excel recognises it as text) Then you can use this formula:

=INDIRECT(CONCATENATE("[",B2,"]Sheet1!$A$1"))
This message was edited by philR on 2002-05-09 02:37
 
Upvote 0
Sorry Phil

Can' make that one work

I'm using Excel 97 does that make any difference
This message was edited by Peter100 on 2002-05-09 04:58
 
Upvote 0
I should have added: This will only work if you have the FebData.xls spreadsheet open at the time. For some reason, I am having difficulty getting it to do it when FebData.xls is closed, although I know it can be done. You basically have to include the pathname in the concatenate statement. Or, if there is a possibility that some of the sheets will be in different directories, include the pathname in B2 instead, and get rid of the square brackets in the concatenate statement. However, you need some single quotes in there somewhere as well, and I can't quite get it to work at the mo. I will keep trying. Let me kno if you solve it first.
 
Upvote 0
Hi Phil
I was just going to reply saying exactly what youv'e said "BOTH BOOKS HAVE TO BE OPEN"

which is a bit of a snag along with it appears that they have to reside in the default Excel save directory.

I'll keep working on it but if you come up with an answer PLEASE let me know.
 
Upvote 0
Could you explain the overall concept of the master file. Do you want the cell with jan.xls info replaced with the cell from feb.xls info, or do you want to keep jan info in master.xls and bring feb.xls info into seperate cells?


Paul
 
Upvote 0
I have a master sheet for each month that contains 31 sheets (one for each day of the month) each sheet contains approx 200 data cells.

Sepearately, someone produces each month a data file containing all the data information in identical format.I then in the master do many calculations and graphs related to it.

What I want to do is bring in the relevant months data into all 31 sheets by simply changing the text entry from JanData.xls to FebData.xls

There is no real problem with the relevant months data file having to be open although it would be nice.

The bigger problem I see is that the formula will not copy and paste relatively and I am going to have to amend each cell manually (I can you search and replace to an extent)

what would, onthinking about it be better was if each sheet could link directly to the relevant data sheet in its entirety (they are identical)

Any further Ideas ?
This message was edited by Peter100 on 2002-05-09 07:44
 
Upvote 0
=INDIRECT("["&$A$1&"data.xls]Sheet1!"&ADDRESS(1,COLUMN(A:A),4,1))

this may help if you have "jan" "feb" etc as your cells to refer to in A1

ADDRESS(1 points it to destination row 1
COLUMN a:a RETURNS "1" AND IS TRANSLATED TO A, assuming your first destination column is A..... just change these two appropriately

ie if your first bit of info sits in G7, change it to

&ADDRESS(7,COLUMN(G:G),4,1)

this should then copy over and down as needed

downsides :

1) you may notice recalc times shhot up because of the volatile functions

2) scource workbooks still need to be open for indirect to work
 
Upvote 0
On 2002-05-08 23:10, Peter100 wrote:
I have a workbook named Master.xls which is a master template.
I have workbooks for each month named JanData.xls - Dec.xls

I want to link the data from cell A1 in JanData.xls into cell A1 of
Master.xls which is simple enough.

The link would look like ='[JanData.xls]Sheet1'!$A$1

But what I want to be able to do is replace the [JanData.xls] part of the link
with a cell reference in Master.xls which would be a text entry so that the link
could be changed to read from FebData.xls - MarData.xls etc as desired.

Anyone any Ideas ?

I used the HYPERLINK function ...

path name in cell A1
File name with extension in cell B1
sheet name and cell name in cell c1

Then use the following formula for hyperlink ...

=HYPERLINK(A1&B1,C1)

This will work even when you change any of the entries in cells A1 B1,or C1 and even if the workbooks are closed.

please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top