Using a variable in a formula
Posted by Bryan Vakos on November 23, 1999 1:23 PM
I'd like to use a variable in a formula. I'm doing a reference to another cell in a workbook I'd like to keep closed. The trick is the cell I need to reference will vary with the date. Is there any way to write the reference (without using a macro, that'd be too easy) so that the row number it's referencing comes from another cell.
Posted by Bryan Vakos on November 23, 1999 1:32 PM
Just to clarify what I'm talking about -
Say if I have the reference:
='C:\[EIC99.xls]Reports ''99'!$B$14
I'd like to be able to replace the 14 row reference with the value of a cell on the current workbook without having to open the source workbook or use a macro.
Posted by Marcus on November 24, 1999 2:53 AM
Use the INDIRECT function:
Put the row number in a cell, eg A1. Then
=INDIRECT("'C:\[EIC99.xls]Reports ''''99'!$B$" & A1)
should now change as you vary A1.
- Marcus
PS - you may run into problems with all the single quotes in your sheet name...
Posted by Bryan Vakos on November 24, 1999 6:30 AM
That works just fine so long as the source is open. The problem is that the source file is huge - 3-4MB huge. The idea is not to have to open it.
I can get the information several ways as long as the source is open but the only way I can get it with the source closed is through an absolute reference to one fixed cell.
The only way I can think to do it is to carry the information from the source into hidden cells in the destination sheet. That defeats the purpose though as the destination would end up bloated too.
Posted by Chris on November 24, 1999 7:55 AM
Bryan,
I think it's time to use VBA. John Walkenbach has a nice function to get a value from a closed file. Check it out here:
http://www.j-walk.com/ss/excel/tips/tip82.htm
HTH,
Chris
Posted by Bryan Vakos on November 24, 1999 8:18 AM
That's what I've started on now... was looking for the simple, elegant solution and it doesn't look like it exists. Thanks.
Posted by Bryan Vakos on December 01, 1999 6:59 AM
Re: Solution
Got this from a newsgroup:
=INDEX('D:\[x.xls]Sheet1'!$1:$65536,2,2)
The last two numbers there are the references to the cell and can be references to cells in the destination sheet.
