View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Andy Pope Andy Pope is offline
external usenet poster
 
Posts: 2,489
Default Indirect Addressing in VBA

Hi,

INDIRECT references will only work if the referenced workbook is open.
Otherwise it will return #REF.

Is Trades Sheet 7-2-07.xls open?

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"C Brandt" wrote in message
...
Hi Guys:

Indirect addressing looks to be the soultion to another problem I'm
having,
but for the life of me, I cannot seem to get it working, which of course
means I don't understand it. Documentation seems very weak in this area.

I would like to do a vlookup into another worksheet, but the name of the
worksheet varies from day to day. Furthermore, since I do not want this
link
to be active in the final product, I create the formula using a button
driven macro that ends it's function by copy, then paste-value of all the
formulas.

To simplify this discussion I would like to replace the VLOOKUP with a
simple Equal. If I were to hard code it, this is what the formula would
look
like:
='[Trades Sheet 7-2-07.xls]Buy'!$C$363
This is what I put in there to replace the formula
Cell I4 = Trades Sheet 7-2-07.xls
Cell I5 = Buy'!$C$363
And the formula that will be pasted down the row is:
Cell I8 = =INDIRECT("'["&I4&"]"&I5)
This results in a #REF!.

Any clues?

Craig