| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 8754 | 2001-04-16 07:27:00 | Excel97 formula | Guest (0) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 11379 | 2001-04-16 07:27:00 | I've been trying to find the right syntax for using text in a cell as part of the formula for obtaining data from a different workbook. For instance, if I want the current cell to display the contents of cell F45 of sheet ABCDE in the workbook MarchData.xls, the formula is ='[MarchData.xls]ABCDE'!F45. However, what I need to do is have a formula which reads ABCDE from a cell on the current worksheet, but every variation of syntax I've tried throws up an error. Is there a way of doing it? Many thanks. PS. Can you recommend a REALLY good book on Excel which deals with this type of problem? |
Guest (0) | ||
| 11380 | 2001-04-16 10:56:00 | If ='[MarchData.xls]ABCDE'!F45 returns the data you want, what is the problem? as I don't quite follow where you error is occuring. If you wanted to use this formula in any cell, either change F45 to $F$45 and copy to any other cell, or convert the formula to text by adding ',' or ^ to the beginning eg '='[MarchData.xls]ABCDE'!F45 then copy to any other cell and then edit out the ' to restore the original formula. If this is not what you mean, repost with more explanation. |
Guest (0) | ||
| 11381 | 2001-04-16 21:53:00 | Hi Russel, Thanks for your response, but that's not the problem. The workbook from which I'm retireving the data[MarchData.xls] has some 50 worksheets. On the parent workbook (the one in which I'm using the formula) different rows of calculations refer to different sheets in MarchData, but each row is identified with a cell containing the target sheet name, eg. ABCDE, as text. What I'm trying to find is a formula to access the correct MarchData worksheet by using a cell reference, that is, to take ABCDE from the cell and use it in the formula syntax. For instance, one of the many forms I've tried is ='[MarchData.xls]{K40}'!F45, where K40 is the cell containing ABCDE. In other words I need a generic formula which can be copied to each row of the parent workbook. There must surely be a way of doing it, it should not be necessary to modify the formula manually for each row of calculations. Perhaps this would operate more easily with a good relational database, but I'm certain Excel has this capability - it's just that the more involved operations are not well documented. |
Guest (0) | ||
| 1 | |||||