Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use TEXT command in workbook link?
Hello.
I'm trying to create links within one workbook to several hundred others. I'd like to use the TEXT formula in my cell references (about 10 per workbook) so that I can simply copy all of my workbook names to update the cell references instead of using the REPLACE function on all of my links. Basically I will be using a cell value as part of the name of a linked workbook. For example: In Workbook_A: Workbook_1 Link1 Link2 Link3 Link4 Link5 Workbook_2 Link1 Link2 Link3 Link4 Link5 .... Workbook_400 Link1 Link2 Link3 Link4 Link5 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use TEXT command in workbook link?
Brett,
How are you entering your hyperlinks? In XL, there is a HYPERLINK() function where you would create a formula to create you hyperlinks. If you know how to concatenate, you could use this function to create a formula the will return a hyperlink in the cell. Be sure to look up HYPERLINK() in XL's Help, it is a little tricky to use. HTH, Conan "Brett" wrote in message ... Hello. I'm trying to create links within one workbook to several hundred others. I'd like to use the TEXT formula in my cell references (about 10 per workbook) so that I can simply copy all of my workbook names to update the cell references instead of using the REPLACE function on all of my links. Basically I will be using a cell value as part of the name of a linked workbook. For example: In Workbook_A: Workbook_1 Link1 Link2 Link3 Link4 Link5 Workbook_2 Link1 Link2 Link3 Link4 Link5 ... Workbook_400 Link1 Link2 Link3 Link4 Link5 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use TEXT command in workbook link?
Hi Conan
For example, my cell reference in Book2: =[Book1]Sheet1!$C$18 I would like to find a way to replace "Book1" with TEXT(A1,"") That way I can copy and paste all of my workbook names into cells A1 to A400 in Book2 and the formulas will all update. Otherwise I need to do a Find / Replace on each line of formulas to replace Book1 with Book2 and Book3, etc. Or I need to open 400 workbooks and Copy / Paste Special / Paste Link the data. Brett "Conan Kelly" wrote: Brett, How are you entering your hyperlinks? In XL, there is a HYPERLINK() function where you would create a formula to create you hyperlinks. If you know how to concatenate, you could use this function to create a formula the will return a hyperlink in the cell. Be sure to look up HYPERLINK() in XL's Help, it is a little tricky to use. HTH, Conan "Brett" wrote in message ... Hello. I'm trying to create links within one workbook to several hundred others. I'd like to use the TEXT formula in my cell references (about 10 per workbook) so that I can simply copy all of my workbook names to update the cell references instead of using the REPLACE function on all of my links. Basically I will be using a cell value as part of the name of a linked workbook. For example: In Workbook_A: Workbook_1 Link1 Link2 Link3 Link4 Link5 Workbook_2 Link1 Link2 Link3 Link4 Link5 ... Workbook_400 Link1 Link2 Link3 Link4 Link5 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use TEXT command in workbook link?
Brett,
Pardon me, I've misunderstood your original post. Yes it is possible, but there are some issues: This can be done using the INDIRECT() function, but the problem is all of your other workbooks need to be open in order for the values to be updated in your master workbook. If you have 400+, there is no way you will be able to open all of them at once. If this is something that only needs to be set up once (you won't be adding links to other books or other cells in existing books), then there is a work-around that we can do. In the formula you provided, it is referencing cell C18 in another book. If you copy this down, will it reference the SAME CELL (C18) in a different workbook? If you copy it accross, will you need it to reference cells D18, E18, F18, etc...? Conan "Brett" wrote in message ... Hi Conan For example, my cell reference in Book2: =[Book1]Sheet1!$C$18 I would like to find a way to replace "Book1" with TEXT(A1,"") That way I can copy and paste all of my workbook names into cells A1 to A400 in Book2 and the formulas will all update. Otherwise I need to do a Find / Replace on each line of formulas to replace Book1 with Book2 and Book3, etc. Or I need to open 400 workbooks and Copy / Paste Special / Paste Link the data. Brett "Conan Kelly" wrote: Brett, How are you entering your hyperlinks? In XL, there is a HYPERLINK() function where you would create a formula to create you hyperlinks. If you know how to concatenate, you could use this function to create a formula the will return a hyperlink in the cell. Be sure to look up HYPERLINK() in XL's Help, it is a little tricky to use. HTH, Conan "Brett" wrote in message ... Hello. I'm trying to create links within one workbook to several hundred others. I'd like to use the TEXT formula in my cell references (about 10 per workbook) so that I can simply copy all of my workbook names to update the cell references instead of using the REPLACE function on all of my links. Basically I will be using a cell value as part of the name of a linked workbook. For example: In Workbook_A: Workbook_1 Link1 Link2 Link3 Link4 Link5 Workbook_2 Link1 Link2 Link3 Link4 Link5 ... Workbook_400 Link1 Link2 Link3 Link4 Link5 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link text from one workbook onto another workbook | Excel Discussion (Misc queries) | |||
Pivot Table Error Message - "Command Text not set for command obje | Excel Discussion (Misc queries) | |||
Link Protected WorkBook ... and Get #N/A for Text Values! | Excel Discussion (Misc queries) | |||
How do I link one TEXT cell to others in a worksheet and workbook. | Excel Worksheet Functions | |||
command for breaking link in excel is not seen. | Excel Discussion (Misc queries) |