Linking a cell to another workbook cell based on a variable name
 

Hi all,

Is there any way that I can link a cell in Workbook A to a cell in Workbook
B(sheet1) based on a variable entry (workbook name) in Workbook A

Workbook A contains two cells

A1 = Workbook B (this is the variable name)
B1 = I want this to be the same as cell B1 in Workbook B

I will then want to copy the formula in Workbook A cell B1 to other cells so
that B2, B3 etc.. will contain the data from the respective cells in Workbook
B

Hope the above makes sense

Brian
Workbook B will contain date in cell B1 = John Smith

Brian wrote:
Hi all,

Is there any way that I can link a cell in Workbook A to a cell in Workbook
B(sheet1) based on a variable entry (workbook name) in Workbook A

Workbook A contains two cells

A1 = Workbook B (this is the variable name)
B1 = I want this to be the same as cell B1 in Workbook B

I will then want to copy the formula in Workbook A cell B1 to other cells so
that B2, B3 etc.. will contain the data from the respective cells in Workbook
B

Hope the above makes sense

Brian
Workbook B will contain date in cell B1 = John Smith
------------------

Try:

[ ] = [WorkbookName]SheetName!A1

Good luck...

Bill

Thanks for the reply, but I can't get this to work.

I know that I can use Paste Link to copy a cell from one Workbook to another
and this will be linked. But I need to be able to have a formula in a cell
that will copy the contents of another workbooks cell based on a workbook
name that will be in a cell in the first workbook.

"Bill Martin -- (Remove NOSPAM from addre" wrote:

Brian wrote:
Hi all,

Is there any way that I can link a cell in Workbook A to a cell in Workbook
B(sheet1) based on a variable entry (workbook name) in Workbook A

Workbook A contains two cells

A1 = Workbook B (this is the variable name)
B1 = I want this to be the same as cell B1 in Workbook B

I will then want to copy the formula in Workbook A cell B1 to other cells so
that B2, B3 etc.. will contain the data from the respective cells in Workbook
B

Hope the above makes sense

Brian
Workbook B will contain date in cell B1 = John Smith
------------------

Try:

[ ] = [WorkbookName]SheetName!A1

Good luck...

Bill

Try:

[ ] = [WorkbookName]SheetName!A1

Good luck...

Bill


---------

The above does work. What error statements are you getting? My guess
is that you've perhaps not put the proper workbook name into the
formula? It should be the entire file name on your disk. Something like:

Workbook1.xls

And if it's not stored in your default Excel work folder, then you'd
need the entire file name. Something of the general form:

c:\MyDocs\Excel\Workbook1.xls

Bill

You are right, it does work.

But I am looking for a way to reference a cell that will contain the filename:

so instead of =[WorkbookName]SheetName!A1

I would like =[Cell A1]SheetName!A1 where Cell A1 will be the filename

Not sure if that makes it any clearer

Brian

"Bill Martin -- (Remove NOSPAM from addre" wrote:


Try:

[ ] = [WorkbookName]SheetName!A1

Good luck...

Bill


---------

The above does work. What error statements are you getting? My guess
is that you've perhaps not put the proper workbook name into the
formula? It should be the entire file name on your disk. Something like:

Workbook1.xls

And if it's not stored in your default Excel work folder, then you'd
need the entire file name. Something of the general form:

c:\MyDocs\Excel\Workbook1.xls

Bill

Brian wrote:
You are right, it does work.

But I am looking for a way to reference a cell that will contain the filename:

so instead of =[WorkbookName]SheetName!A1

I would like =[Cell A1]SheetName!A1 where Cell A1 will be the filename

Not sure if that makes it any clearer

Brian

----------------

You need to use the address format I gave you, and combine it with an
INDIRECT() statement. Look in the Excel help system for detailed info
on INDIRECT.

Basically you build up a text version of the address you need and insert
that into the INDIRECT() statement. For example, if your workbook name
is stored as text in cell A5, then:

[ ] = INDIRECT("[" & A5 & "]SheetName!A1")

Bill

Just to add to Bill's response...

=indirect() won't work with closed workbooks.

Harlan Grove wrote a User Defined Function that can pull from a closed
workbook:

http://groups.google.co.uk/groups?se...wsranger.co m

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Brian wrote:

Hi all,

Is there any way that I can link a cell in Workbook A to a cell in Workbook
B(sheet1) based on a variable entry (workbook name) in Workbook A

Workbook A contains two cells

A1 = Workbook B (this is the variable name)
B1 = I want this to be the same as cell B1 in Workbook B

I will then want to copy the formula in Workbook A cell B1 to other cells so
that B2, B3 etc.. will contain the data from the respective cells in Workbook
B

Hope the above makes sense

Brian
Workbook B will contain date in cell B1 = John Smith

--

Dave Peterson