Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy excell file to a CSV file and than to Notepad | Excel Discussion (Misc queries) | |||
how do i open a file in excel that is encrypted or read only | Excel Discussion (Misc queries) | |||
How do I save a read only file or folder? | Excel Discussion (Misc queries) | |||
Unable to read file | Setting up and Configuration of Excel | |||
Network access to file - read only property | Excel Discussion (Misc queries) |