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

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

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

 

[Guest]

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

 

[Guest]

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

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

 

[Guest]

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

 

[Dave Peterson]

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/[email protected]

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