I am doing simple cell references from another file. I'm not using
any formulas or functions or anything; just using a simple cell
reference. However, it does not work (returns #REF) unless the file
is open. Now, I should mention that this is not an Excel file but
simply a text file with rows. The cell referencing does in fact work
if the text file is open, but not if it's closed. (as a test, I also
checked to make sure I am able to reference cells from .xls files and
that also, of course, works regardless of whether they're open or
closed).

Does anyone know if referencing from closed text files is supposed to
work or not, and why? And does anyone have any ideas for a
workaround?

Thanks much

TheErick wrote...
....
Does anyone know if referencing from closed text files is supposed to
work or not, and why? *And does anyone have any ideas for a
workaround?
....

Experimentation is your only recourse. FWIW, Excel can update
references into closed Lotus 123 .WK* files. It can't update
references into closed XML spreadsheet files or DIF files. I didn't
try other types, but I'd be surprised if it couldn't update references
into closed SYLK files.

How would excel know how to parse your file.

If you wanted the value in cell X17, how would it know if you have a fixed width
layout, comma separated, tab separated, ... file?

I would think the best workaround would be to import the data (using a macro if
it's repetitive) and then save the data in a normal excel file.

But if you were really industrious, I bet you could create your own UDF that
would open the text file, parse the data following your rules and return the
value you want.

If you have lots of these values to return, I don't think I'd want to be around
for a recalculation.

TheErick wrote:

I am doing simple cell references from another file. I'm not using
any formulas or functions or anything; just using a simple cell
reference. However, it does not work (returns #REF) unless the file
is open. Now, I should mention that this is not an Excel file but
simply a text file with rows. The cell referencing does in fact work
if the text file is open, but not if it's closed. (as a test, I also
checked to make sure I am able to reference cells from .xls files and
that also, of course, works regardless of whether they're open or
closed).

Does anyone know if referencing from closed text files is supposed to
work or not, and why? And does anyone have any ideas for a
workaround?

Thanks much

--

Dave Peterson

On Jan 21, 9:07*am, Dave Peterson wrote:
How would excel know how to parse your file.

If you wanted the value in cell X17, how would it know if you have a fixed width
layout, comma separated, tab separated, ... file?

I would think the best workaround would be to import the data (using a macro if
it's repetitive) and then save the data in a normal excel file.

But if you were really industrious, I bet you could create your own UDF that
would open the text file, parse the data following your rules and return the
value you want.

If you have lots of these values to return, I don't think I'd want to be around
for a recalculation.



TheErick wrote:

I am doing simple cell references from another file. *I'm not using
any formulas or functions or anything; just using a simple cell
reference. *However, it does not work (returns #REF) unless the file
is open. *Now, I should mention that this is not an Excel file but
simply a text file with rows. *The cell referencing does in fact work
if the text file is open, but not if it's closed. *(as a test, I also
checked to make sure I am able to reference cells from .xls files and
that also, of course, works regardless of whether they're open or
closed).

Does anyone know if referencing from closed text files is supposed to
work or not, and why? *And does anyone have any ideas for a
workaround?

Thanks much

--

Dave Peterson

It doesn't need to know what "cell" it is in (not there are are truly
cells in a text file) as the file is very simple. To envision it, it
would be the same type of thing as having a spreadsheet with data only
in A1 to A100 (nothing in any other columns). So, back to your
question...it doesn't need to know if it's comma separated or how to
pull from cell X17, for example.

Here is what I'm considering doing: I think I will create a cell
(using INDIRECT) with the name of the file in it and make it hot
linkable. Then, all the user would have to do is click on the link to
open the file and that would then allow the reference to work.

You may only have one "column" per line/row this time, but how would excel know
that that's the rule to use for everyone and for everytime?

So why not just create a normal excel file?

TheErick wrote:
<<snipped
It doesn't need to know what "cell" it is in (not there are are truly
cells in a text file) as the file is very simple. To envision it, it
would be the same type of thing as having a spreadsheet with data only
in A1 to A100 (nothing in any other columns). So, back to your
question...it doesn't need to know if it's comma separated or how to
pull from cell X17, for example.

Here is what I'm considering doing: I think I will create a cell
(using INDIRECT) with the name of the file in it and make it hot
linkable. Then, all the user would have to do is click on the link to
open the file and that would then allow the reference to work.

--

Dave Peterson

On Jan 21, 1:39*pm, Dave Peterson wrote:
You may only have one "column" per line/row this time, but how would excel know
that that's the rule to use for everyone and for everytime?

So why not just create a normal excel file?

TheErick wrote:

<<snipped

It doesn't need to know what "cell" it is in (not there are are truly
cells in a text file) as the file is very simple. *To envision it, it
would be the same type of thing as having a spreadsheet with data only
in A1 to A100 (nothing in any other columns). *So, back to your
question...it doesn't need to know if it's comma separated or how to
pull from cell X17, for example.

Here is what I'm considering doing: I think I will create a cell
(using INDIRECT) with the name of the file in it and make it hot
linkable. *Then, all the user would have to do is click on the link to
open the file and that would then allow the reference to work.

--

Dave Peterson

The file contents and the filename syntax have to be in a certain
format so that the application that they're used in can read them.
The file's content format will not change so if I can come up with a
way to make this work, it should continue to work over time.
Thanks for your input on this.

Good luck.

TheErick wrote:

<<snipped

The file contents and the filename syntax have to be in a certain
format so that the application that they're used in can read them.
The file's content format will not change so if I can come up with a
way to make this work, it should continue to work over time.
Thanks for your input on this.

--

Dave Peterson