Cell Referencing to Another Spreadsheet

[elephant97]

I'm having a little trouble doing some referencing to another spreadsheet. For simplicity, file "A.xls" has formulas in it that refer to spreadsheet "B.xls". However, the fields in A won't populate until I open up spreadsheet B. I've tried to write a macro to conquer this but can't. Help!! Thanks!

 

[gene_sc]

Hi
I believe that both workbooks have to be open for references to other workbook to work. ( Could be wrong as, I am by no means an expert) I am doing a similar reference between two workbooks, so what I did is save both files in Xlstart folder so they both open with excel

regards
gene

 

[elephant97]

I'm almost positive I have seen this done before, it's like an automatic update function call. Please help!

 

[elephant97]

I'm almost positive I have seen this done before, it's like an automatic update function call. Please help!

 

[24ctexcel]

Same here.....

Sorry to bring this one up again - having exactly the same problem. Anyone got any fresh ideas?

 

[gaynard_nelson]

Re: Same here.....

Sorry to bring this one up again - having exactly the same problem. Anyone got any fresh ideas?

I link formulas in one spreadsheet to another all the time with no problems. I use the point mode to build the formulas with both spreadsheets open. From then on the spreadsheet referred to in the formulas does not have to be opened to be updated.

Can you show us some of the formulas you wrote to access the other spreadsheet. That may be the problem. Are both spreadsheets on automatic recalc? You have a problem that should be solvable.

 

[24ctexcel]

great thanks...

I will be back in touch again on Monday when I get back into work, as I don't have a copy of the .xls locally. Nothing too complex in the formulas I don't think..... just some VLOOKUPs. Really hope we can get this sorted as its been occupying most of my waking life for the last two days!

 

[SidBord]

I have a love/hate relationship with links. For some applications I have been forced to use a little known old function named Excel4Macro. If you have multiple references to the same workbook, you might want to create a variable to contaiin the path as I have done (below). Also it is helpful to define a name for each cell that you want to access.
Suppose you want to transfer the value of cell "B1" in workbook "B" to cell "A1" in workbook "A". First, define a name for cell "B1", like maybe "Poop". The in VBA you can write the following statements"
Dim Path as String
Path = "'C:\ExcelDocs\[WorkbookB.xls]SheetName'!"
Range("A1") = ExecuteExcel4Macro(Path & "Poop")
This will always fetch a value from a closed workbook. If you have trouble, look up Excel4Macro in the online Help.

 

[just_jon]

Take a look under Tools | Options | calculation tab, and see if Update remote references is selected. Might also want Save external link values selected.

The above is assuming your formulas do not include the INDIRECT function.

 

[24ctexcel]

OK - I think we have hit on the problem, the indirect function is being used.

There is a preference to use INDIRECT as opposed to VLOOKUP as there are over 80 reference sheets being accessed by 60 output sheets. As we see it at the moment, VLOOKUP would be possible but would be manual intensive.

So, is there a workaround with the Indirect function?

Example:

=INDIRECT(ADDRESS(9,COLUMN(G1),1,1,"R:\Research\Zhenqi\ForecastModel\WorkPopulation\[FINAL BUSDEM-outputs.xls]"&$A$1))

Other actions taken,:

Update remote references is selected - Yes... no fix
Save external link values selected - Yes... no fix
VBS Script - ran it, didn't work for me.

Thanks for the pearls of wisdom so far,

Richard