How do I read a file name into =[BookA.xls]Sheet1!$a$1?
I want to link Excel spreadsheets by formula, rather than by manual
manipulation. I want to put data from a series of standard-format weekly spreadsheets into 1 summary spreadsheet. Manually the process is: 1. Open Spreadsheet A with a lot of data and calculations in it in columns 2. Open Spreadsheet B into which I want to put selected data from A 3. From a cell in B, enter = and then switch to A, click on the top cell from which I want data and press Enter 4. The formula in the cell then reads in the format =[BookA.xls]Sheet1!$A$1 5. Modify that formula by removing the $ signs i.e. convert $A$1 to A1 6. Manually copy that formula down the column This achieves the objective but is labour and skill intensive. What I would like to do is to be able to add the names of the weekly spreadsheets to the top of a column (e.g. BookA, BookB and so on) and for the formulae in the column to read that name into the cell formulae. I have tried to do this but just get error messages. Is there a way to do what I want to do or must it be done manually every time? |
How do I read a file name into =[BookA.xls]Sheet1!$a$1?
One way is to use INDIRECT*
... "=[BookA.xls]Sheet1!$A$1" With B1 across containing: BookA, BookB, ... Put in B2: =INDIRECT("["&B$1&".xls]Sheet1!A"&ROW(A1)) Copy B2 across & fill down *The source books: BookA, BookB, ... need to be open simultaneously -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Facilitator" wrote in message ... I want to link Excel spreadsheets by formula, rather than by manual manipulation. I want to put data from a series of standard-format weekly spreadsheets into 1 summary spreadsheet. Manually the process is: 1. Open Spreadsheet A with a lot of data and calculations in it in columns 2. Open Spreadsheet B into which I want to put selected data from A 3. From a cell in B, enter "=" and then switch to A, click on the top cell from which I want data and press Enter 4. The formula in the cell then reads in the format "=[BookA.xls]Sheet1!$A$1" 5. Modify that formula by removing the $ signs i.e. convert $A$1 to A1 6. Manually copy that formula down the column This achieves the objective but is labour and skill intensive. What I would like to do is to be able to add the names of the weekly spreadsheets to the top of a column (e.g. BookA, BookB and so on) and for the formulae in the column to read that name into the cell formulae. I have tried to do this but just get error messages. Is there a way to do what I want to do or must it be done manually every time? |
All times are GMT +1. The time now is 11:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com