If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Indirect Addressing in VBA
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 pastevalue 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 7207.xls]Buy'!$C$363 This is what I put in there to replace the formula Cell I4 = Trades Sheet 7207.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 
Ads 
#2




Indirect Addressing in VBA
Hi,
INDIRECT references will only work if the referenced workbook is open. Otherwise it will return #REF. Is Trades Sheet 7207.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 pastevalue 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 7207.xls]Buy'!$C$363 > This is what I put in there to replace the formula > Cell I4 = Trades Sheet 7207.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 > > 
#3




Indirect Addressing in VBA
Craig,
The target workbook, Trades Sheet 7207.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 7207.xls]Buy'!$C$363  Earl Kiosterud www.smokeylake.com Note: Topposting has been the norm here. Some folks prefer bottomposting. But if you bottompost to a reply that's already topposted, 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 pastevalue 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 7207.xls]Buy'!$C$363 > This is what I put in there to replace the formula > Cell I4 = Trades Sheet 7207.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 > > 
#4




Indirect Addressing in VBA
Andy:
Thanks for the response. You were right. I didn't have the target sheet open. Now I have to figure out why it is accessing the wrong cell. Is there a simple VBA command that will ensure that the target file is open? Regards, Craig "Andy Pope" > wrote in message ... > Hi, > > INDIRECT references will only work if the referenced workbook is open. > Otherwise it will return #REF. > > Is Trades Sheet 7207.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 pastevalue 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 7207.xls]Buy'!$C$363 > > This is what I put in there to replace the formula > > Cell I4 = Trades Sheet 7207.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 > > > > > 
#5




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 7207.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 7207.xls]Buy'!$C$363 > >  > Earl Kiosterud > www.smokeylake.com > > Note: Topposting has been the norm here. > Some folks prefer bottomposting. > But if you bottompost to a reply that's > already topposted, 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 pastevalue 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 7207.xls]Buy'!$C$363 > > This is what I put in there to replace the formula > > Cell I4 = Trades Sheet 7207.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 
#6




Indirect Addressing in VBA
Hi,
Not sure it simple. But I guess 1 approach would be to add code to the workbook Open event to check and then open the workbook if required. Private Sub Workbook_Open() Dim wbkRef As Workbook On Error Resume Next Set wbkRef = Workbooks("Trades Sheet 7207.xls") If wbkRef Is Nothing Then Set wbkRef = Workbooks.Open("C:\Trades Sheet 7207.xls") If wbkRef Is Nothing Then MsgBox "Unable to open referenced workbook", vbExclamation End If End If If StrComp(wbkRef.FullName, _ "C:\Trades Sheet 7207.xls", vbTextCompare) <> 0 Then MsgBox "File with same name but diffrent location" & _ " is already open", vbExclamation Else ' All is well MsgBox "OK" End If End Sub Cheers Andy C Brandt wrote: > Andy: > > Thanks for the response. > You were right. I didn't have the target sheet open. Now I have to figure > out why it is accessing the wrong cell. > > Is there a simple VBA command that will ensure that the target file is > open? > > Regards, > > Craig > > > "Andy Pope" > wrote in message > ... > >>Hi, >> >>INDIRECT references will only work if the referenced workbook is open. >>Otherwise it will return #REF. >> >>Is Trades Sheet 7207.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 pastevalue 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 7207.xls]Buy'!$C$363 >>>This is what I put in there to replace the formula >>>Cell I4 = Trades Sheet 7207.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 >>> >>> >> > > 
#7




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 7207.xls]Buy'!$A$13:$BV$363,70,0)" Manually copy this formula down to around cell I500 Copy I8:I500 Pastevalue 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 7207.xls) in a cell location in the running spreadsheet and via a button executed macro, create that formula, then copypaste 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 7207.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 7207.xls]Buy'!$C$363 > >  > Earl Kiosterud > www.smokeylake.com > > Note: Topposting has been the norm here. > Some folks prefer bottomposting. > But if you bottompost to a reply that's > already topposted, 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 pastevalue 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 7207.xls]Buy'!$C$363 > > This is what I put in there to replace the formula > > Cell I4 = Trades Sheet 7207.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 > > > > > > 
#8




Indirect Addressing in VBA
You know that the formula's syntax has to be something like:
=Vlookup($H8,'[Trades Sheet 7207.xls]Buy'!$A$13:$BV$363,70,0) or when that file is closed: =VLOOKUP(H8,'C:\My Documents\excel\[[Trades Sheet 7207.xls]Buy'!$A:$CQ,70,0) So you could get the filenameapplication.getopenfilename may work nicely if you allow the users to put the file whereever they want. Or you could pick it up from a cellbut be aware that you'll have to do some validation for this to work rightmake sure the user gives a correct location and filename. Here's hoping that the worksheets keep the name Buy(!). Option Explicit Sub testme() Dim WkbkName As String Dim FolderName As String Dim myStr As String Dim myRng As Range Dim myCell As Range With ActiveWorkbook.Worksheets("sheet1") Set myCell = .Range("a1") WkbkName = myCell.Value FolderName = "C:\My Documents\excel\" myStr = "'" & FolderName & "[" & WkbkName & "]buy'!$a$13:$bv$363" Set myRng = .Range("I8:I" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With myRng .Formula = "=vlookup($h8," & myStr & ",70,0)" .Value = .Value End With End Sub I filled column I from row 8 to the last row that was used in column A. C Brandt wrote: > > 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 7207.xls]Buy'!$A$13:$BV$363,70,0)" > Manually copy this formula down to around cell I500 > Copy I8:I500 > Pastevalue 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 > 7207.xls) in a cell location in the running spreadsheet and via a button > executed macro, create that formula, then copypaste 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 7207.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 7207.xls]Buy'!$C$363 > > > >  > > Earl Kiosterud > > www.smokeylake.com > > > > Note: Topposting has been the norm here. > > Some folks prefer bottomposting. > > But if you bottompost to a reply that's > > already topposted, 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 pastevalue 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 7207.xls]Buy'!$C$363 > > > This is what I put in there to replace the formula > > > Cell I4 = Trades Sheet 7207.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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Cell Addressing  John Calder  New Users to Excel  3  March 9th 07 01:32 PM 
Cell addressing  Mike  Excel Worksheet Functions  0  November 30th 06 03:51 PM 
Indirect Addressing  Kanga 85  Excel Discussion (Misc queries)  3  December 5th 05 05:03 AM 
Sorksheet addressing (I think) in '97  Adam Kroger  Excel Discussion (Misc queries)  0  November 27th 05 11:29 PM 
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions  Mike Barlow  Excel Worksheet Functions  6  July 6th 05 03:04 AM 