mailman
08-12-2008, 07:55 AM
Hi again everyone!
I've got a few Workbooks with about 600 references per WorkSheet (6 worksheets) to another Workbook.
Each cell (of the 600 references) are running an HLOOKUP to the master Workbook which is all fine and dandy until I have to create a new WorkBook for the following month.
At that point I'm running a Search/Replace for the full file path (b/c the previous month goes to an archive folder). This takes FOREVER!!!! ;)
My question is, what is the syntax for each cell for me to store the path on a separate worksheet and concatenate it with the cell location on the other worksheet?
For example (I'll use a simpler version of what I'm trying to do):
Book1 has a reference in A1 to Book4 as follows:
='C:\Documents and Settings\user10\Desktop\[Book4.xls]Sheet1'!A1
Book4 just contains a string "Hello world".
I'd like Book1 to link to Book4 as follows:
Book1 has the path in A2:
C:\Documents and Settings\user10\Desktop\[Book4.xls]
So, my reference in Book1!A1 would be something like this:
=A2 & Sheet1'!A1
I've tried this, example, but it's not working for some reason. Is this not doable, or is my syntax all screwy?
I've got a few Workbooks with about 600 references per WorkSheet (6 worksheets) to another Workbook.
Each cell (of the 600 references) are running an HLOOKUP to the master Workbook which is all fine and dandy until I have to create a new WorkBook for the following month.
At that point I'm running a Search/Replace for the full file path (b/c the previous month goes to an archive folder). This takes FOREVER!!!! ;)
My question is, what is the syntax for each cell for me to store the path on a separate worksheet and concatenate it with the cell location on the other worksheet?
For example (I'll use a simpler version of what I'm trying to do):
Book1 has a reference in A1 to Book4 as follows:
='C:\Documents and Settings\user10\Desktop\[Book4.xls]Sheet1'!A1
Book4 just contains a string "Hello world".
I'd like Book1 to link to Book4 as follows:
Book1 has the path in A2:
C:\Documents and Settings\user10\Desktop\[Book4.xls]
So, my reference in Book1!A1 would be something like this:
=A2 & Sheet1'!A1
I've tried this, example, but it's not working for some reason. Is this not doable, or is my syntax all screwy?