Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format cell in column B based on value in the next cell (column c) | Excel Discussion (Misc queries) | |||
How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions | |||
changing value of a cell based on another cell color | Excel Discussion (Misc queries) | |||
linking cell value in one workbook to a cell in another workbook | Links and Linking in Excel | |||
Delete Cell Value Based on Another Cell T or F | Excel Discussion (Misc queries) |