offset with reference to unopen spreadsheet

[pkhoo]

hi all,

need your help here... i have an offset function with reference to an unopened spreadsheet. It comes back with #value! msg. If the spreadsheet is open, then the formula works. both spreadsheet are on the same directory. I am running excel 2000 sp3. APPRECIATE ANY HELP YOU CAN GIVE.
THANK YOU.

regards

 

[SkipVought]

Hi,
[sub]
Patient: "Dr., my head hurts when I hit it with a hammer."

Doctor: "Stop hitting your head with a hammer! That will be $75.00!"
[/sub]
How 'bout opening the other workbook?

You could do that in the Wrokbook_Open Event. Macro record opening the other workbook, give the macro a meaningful name, and call it from

Private Sub Workbook_Open()

End Sub

Post back if you need help.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[pkhoo]

thanks for the joke, need a laugh here....

well, i kinda like to send the spreadsheet to someone else without sending the supporting spreadsheets.... is this a problem with offset only since a simple link doesn't have the same problem.

thank you
peter

 

[SkipVought]

Please post the formula.

I find that OFFFSET sometimes does not work well in a sheet formula but does work in a Named Range via Insert/Name/Define.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[pkhoo]

hi skip,

here's the formula
=SUM(OFFSET('[Global Qr View 2003 actuals.xls]260000 North America'!$D54,0,1,1,Info!$E$27))

thanks for looking into it.

peter

 

[SkipVought]

The problem with the OFFSET function, is that it returns a range, NOT JUST A SINGLE VALUE. It myst be dynamically available.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[pkhoo]

ok,
just have to value range it if i need to forward it. thank you