View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default Indirect Addressing in VBA

Just to add to Earl's suggestion...

You may want to specify the drive/folder that contains that workbook, too.



Earl Kiosterud wrote:

Craig,

The target workbook, Trades Sheet 7-2-07.xls, must be open. Indirect doesn't work with
closed files. If you're doing this via a macro, then make a link to the data instead using
INDIRECT. This should work with the target workbook closed.

='[Trades Sheet 7-2-07.xls]Buy'!$C$363

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
"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



--

Dave Peterson