I have information held in a separate worksheet and the name of the worksheet
is changing constantly. Within a cell on my current worksheet I have the
file details of where the information I require is to be obtained. I want to
create a formula that takes the file details from the cell above and adds it
to the formula so it knows where to find the information i require

Hi Paul
You want to use the INDIRECT Worksheet Function
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Paul Bond" wrote in message ...
I have information held in a separate worksheet and the name of the worksheet
is changing constantly. Within a cell on my current worksheet I have the
file details of where the information I require is to be obtained. I want to
create a formula that takes the file details from the cell above and adds it
to the formula so it knows where to find the information i require

Do you mean that the worksheet (tab) name is constantly changing, or
that the workbook (file) name is changing? If the sheet name is being
changed, you can overcome this by using a named range for the
information. If the filename is being changed, then the cell in your
current worksheet that holds this will need to change also.

You are a bit vague in describing the "information" you require. Is
this a single cell whose value you want to return, or could it be a
table that you want a lookup formula to access? You can refer to a
location in another file by means of:

[filename.xls]sheet_name!cell_reference

but depending on how you want to access this you will probably also
need to make use of the INDIRECT( ) function.

Hope this helps.

Pete

The contents of a cell in my spreadsheet contains a file and worksheet
reference eg
[china garden week 74.xls]Sheet1!$D$15
The contents of this cell is updated by a formula each time the week number
changes. Therefore the number "74" is always changing

I want to enter a formula to return the value in the worksheet cell stated
above without having to amend the formula each time the filename changes.

I hope this makes more sense. Thanks for your help

"Pete" wrote:

Do you mean that the worksheet (tab) name is constantly changing, or
that the workbook (file) name is changing? If the sheet name is being
changed, you can overcome this by using a named range for the
information. If the filename is being changed, then the cell in your
current worksheet that holds this will need to change also.

You are a bit vague in describing the "information" you require. Is
this a single cell whose value you want to return, or could it be a
table that you want a lookup formula to access? You can refer to a
location in another file by means of:

[filename.xls]sheet_name!cell_reference

but depending on how you want to access this you will probably also
need to make use of the INDIRECT( ) function.

Hope this helps.

Pete

Ok, thanks for coming back with more detail. As you have spaces in your
filename, you will need to wrap apostrophes around this and the sheet
name so that you have:

'[china garden week 74.xls]Sheet1'!$D$15

Is this something your formula in this cell can (be amended to) do? If
so, and assuming the formula is in cell A1, then you could enter this
formula in the cell where you want the extracted data to appear:

=INDIRECT(A1)

Be warned, however, that INDIRECT( ) cannot get data from a file which
is not open.

Hope this helps.

Pete

Thanks Pete. The next problem will be that none of the files that I want to
link to will be open. Is there anyway around this?

"Pete" wrote:

Ok, thanks for coming back with more detail. As you have spaces in your
filename, you will need to wrap apostrophes around this and the sheet
name so that you have:

'[china garden week 74.xls]Sheet1'!$D$15

Is this something your formula in this cell can (be amended to) do? If
so, and assuming the formula is in cell A1, then you could enter this
formula in the cell where you want the extracted data to appear:

=INDIRECT(A1)

Be warned, however, that INDIRECT( ) cannot get data from a file which
is not open.

Hope this helps.

Pete

Hoping this is not too off-subject, but I'm having a problem with a
similar formula. I've created the formula by using concatenate to
create the reference to a cell in another spreadsheet, and then copied
and Paste/Special/Values the result of the Concatenate. I end up with
what I want, a formula such as ='c:\[385.xls]sheet1'!$l$4 but Excel
doesn't recognize the formula until I select the cell, hit F2 to edit,
and press Enter. Until I do that it just displays the formula itself
and not the result. I'd rather not do F2, Enter, several thousand
times to get it to recognize all my formulas. Is there another way to
get it to recognize the formulas?

Thanks.

Is your macro populating the cell with
cell.formula as opposed to cell.value
.value is the default.

Select an empty cell (no content, no spaces, no formulas
to use cell M1 it must test True for =ISBLANK(M1)
Copy that cell, then
Select the range you want to fix, can be the entire column(s)
Edit, Paste Special, Add

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

wrote in message oups.com...
Hoping this is not too off-subject, but I'm having a problem with a
similar formula. I've created the formula by using concatenate to
create the reference to a cell in another spreadsheet, and then copied
and Paste/Special/Values the result of the Concatenate. I end up with
what I want, a formula such as ='c:\[385.xls]sheet1'!$l$4 but Excel
doesn't recognize the formula until I select the cell, hit F2 to edit,
and press Enter. Until I do that it just displays the formula itself
and not the result. I'd rather not do F2, Enter, several thousand
times to get it to recognize all my formulas. Is there another way to
get it to recognize the formulas?

Thanks.

I think I'm working more simply than what you may think. I'm not using
macros and wouldn't know where to begin doing it that way.

What I have is a range of numbers in column A (say, 1 through 1000).
Cell B1, I have =CONCATENATE("='c:\[",A1,".xls]sheet1'!$l$4"), B2
reference A2 and so on... So, I have that formula in column B, 1
through 1000. I then copy B1:B1000 and Paste, Special, Values in
column C, which gets me to where Excel just displays the formula in
column C until I select each cell and do the F2, Enter.

I tried pasting from column B into a blank cell (tested with ISBLANK)
using the Paste Special, Add and ended up with a #VALUE! error and cell
contents of =0+(CONCATENATE("='c:\[",H18,".xls]sheet1'!$l$4")

I hope you got rid of the =0+ bit.

If you still have the formulas visible rather than the result,
highlight column C, go to Data | Text-toColumns then click Finish.
This should give you the results you are looking for, rather than
having to edit 1000 cells.

Hope this helps.

Pete

Thanks, Pete! That did it.