View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.misc
C Brandt C Brandt is offline
external usenet poster
 
Posts: 92
Default Indirect Addressing in VBA

Thanks All:

Maybe I am attacking this all wrong.

Key features of the task a
1) I want to generate a way to grab the data from another spread
sheet(target file) on demand only
2) I do not want active links, since that target file changes
3) Everyone that will be using this feature does not have the same directory
structure
4) I can require everyone to keep the target file in the save directory as
the running spreadsheet

The following is the way I currently do it.
I input the following formula in cell I8 of the running spreadsheet
"=Vlookup($H8,'[Trades Sheet 7-2-07.xls]Buy'!$A$13:$BV$363,70,0)"
Manually copy this formula down to around cell I500
Copy I8:I500
Paste-value in cells I8:I500
Save and distribute the file.

When I am done, no links exist, and all would be great, except the peope
that use the running spreadsheet make minor changes to the sheet to suite
their own needs and would like to do the update on their own.

Is there a simple way of inputting the name of the Target File (Trade Sheet
7-2-07.xls) in a cell location in the running spreadsheet and via a button
executed macro, create that formula, then copy-paste it to eliminate the
link?

I thought Indirect was the best solution, but I'm not so sure now.

Craig



"Earl Kiosterud" wrote in message
...
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