Re: INDIRECT HELP!

From: Mark Graesser (anonymous_at_discussions.microsoft.com)
Date: 04/01/04 

Date: Thu, 1 Apr 2004 09:36:07 -0800

Hi Kenny,
This is a little cumbersome but it might be useful if you have a very large list.

1) Enter the following in a cell: INDIRECT("[DATA.XLS]"&$A$1&"!O15

2) Drag down, this should increment the way you want.

3) In another cell enter: =A3&""")"
    Change A3 to correct reference. This step adds the ") onto the end of the formula.

4) Copy and Paste special Values

5) Use Replace to change INDIRECT to =INDIRECT.

Good Luck,
Mark Graesser
mark_graesser@yahoo.com
Boston MA
     
     ----- KENNY wrote: -----
     
     Success! One last part: any suggestions on how I can drag
     the formula to change the cell reference? As I understand
     it, with this formula I would have to manually change each
     cell, for example,
     
>=INDIRECT("[DATA.XLS]"&$A$1&"!O15")
>=INDIRECT("[DATA.XLS]"&$A$1&"!O16")
>=INDIRECT("[DATA.XLS]"&$A$1&"!O17")
     
     
     
>-----Original Message-----
>=INDIRECT("[DATA.XLS]"&A1&"!O15")
>>where A1 contains the *** name.
>>--
>>Vasant
>>>>"KENNY" <anonymous@discussions.microsoft.com> wrote in
     message
>news:125db01c41801$c6d94c20$a001280a@phx.gbl...
>> I have a workbook that makes an external reference
>> (DATA.xls) to different worksheets. I would like to be
>> able to have the work*** referenced change based on
     the
>> contents of a single sell (*** 1, 2, 3, etc.).
>>>> Any suggestions?
>>>> TIA!
>>>>>>>> '[DATA.XLS]Sheet1'!O15
>>>.
>