spreadsheet Links

[Peter100]

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 ?

[philR]

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 ]

[Peter100]

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 ]

[Peter100]

Phil
Sorry about Sorry
Tried it again and it worked

Thanks very much

[philR]

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.

[Peter100]

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.

[EBK]

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

[Peter100]

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 ]

[Chris Davison]

=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

[Yogi Anand]

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!