Dynamic Link to Cell in another worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AMDRIT - 24 Aug 2007 16:59 GMT

I was hoping that I could reference a cell in another worksheet in another
workbook using indirect and address in my formula and I appeat to be missing
something or I am doing it wrong.

Target workbook = "c:\myworkbook.xls"
Target sheet = "mydata"
Target Range = "$a$1"

my formula = indirect(address("'[c:\myworkbook.xls]mydata'!$A$1"))

any suggestions on what I may try?

TIA

Reply to this Message

Dave Peterson - 24 Aug 2007 17:18 GMT

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

> I was hoping that I could reference a cell in another worksheet in another
> workbook using indirect and address in my formula and I appeat to be missing
[quoted text clipped - 9 lines]
>
> TIA

Signature

Dave Peterson

Reply to this Message

Ron Rosenfeld - 24 Aug 2007 17:21 GMT

>I was hoping that I could reference a cell in another worksheet in another
>workbook using indirect and address in my formula and I appeat to be missing
[quoted text clipped - 9 lines]
>
>TIA

It would be helpful if you would share what happens when you tried that
formula. My mind-reading skills are not very good.

If you received a #REF! error value, then Excel HELP may be of use to you:

If ref_text refers to another workbook (an external reference), the other
workbook must be open. If the source workbook is not open, INDIRECT returns the
#REF! error value.

--ron

Reply to this Message

AMDRIT - 24 Aug 2007 20:31 GMT

Thanks Ron and Dave.

I was able to determine my issue, it was a typo.

my formula = indirect(address("'[c:\myworkbook.xls]mydata'!$A$1"))

should have been

my formula = indirect(address("'c:\[myworkbook.xls]mydata'!$A$1"))

I appreciate the QUICK response.

Ron,

You were correct, telling you that my issue was a #Ref error would have been
more descriptive. Unfortunately, since I knew I had the other workbook
open, I already ruled out that as the issue. I was late for a meeting when
I made the post (which is an excellent time to submit a post when you really
want a quick response.) and didn't re-read what I had typed.

Truth be known, debugging formulas can be a bit challenging, take this
resulting formula as an example:

=IF(ISERROR(INDIRECT(ADDRESS(ROW(27:27)-DATA_OFFSET,COLUMN(B:B),,TRUE,DATA_BOOK
& VLOOKUP(SELECTED_COMPANY,COMPANY_ABREVIATIONS,2,FALSE) & TEXT(B$7," mmm
yy")),TRUE)),0,IF((INDIRECT(ADDRESS(2,COLUMN(B:B),,TRUE,DATA_BOOK &
VLOOKUP(SELECTED_COMPANY,COMPANY_ABREVIATIONS,2,FALSE) & TEXT(B$7," mmm
yy")),TRUE))
=B$8,INDIRECT(ADDRESS(ROW(27:27)-DATA_OFFSET,COLUMN(B:B),,TRUE,DATA_BOOK &
VLOOKUP(SELECTED_COMPANY,COMPANY_ABREVIATIONS,2,FALSE) & TEXT(B$7," mmm
yy")),TRUE),0))

IsError checks to see if there was a #Ref error getting to the target data
If there was an error set the cell's value to 0
If there wasn't an error check to see if the source date is the same as
the target date
If same date set the cell's value to the value of the source cell
If not the same date set the cell's value to 0

Noticing that the [] were in the wrong place is so insignificant in the
grander scheme of things, it simply couldn't have been the issue, and yet,
it was. '+uncpath+[+filename+]+sheetname!+'!+range (the link dave provided
clued me in on my problem.)

The only way to break this up more is to use more cells that would mean
nothing to the intended user should he/she see them. As it is now, the user
has to either allow macros so that I can dynamically open the source
workbook and update on paramater change, or they have to open the source
workbook(s) so that the data is automagically refreshed. Which brings me
back to the fact that I initially wanted to use querytables rather than
point to a silly ol' workbook.

Anyway, stepping off my soapbox and closing my diatribe.

Thanks for the feedback.

>>I was hoping that I could reference a cell in another worksheet in another
>>workbook using indirect and address in my formula and I appeat to be
[quoted text clipped - 22 lines]
>
> --ron

Reply to this Message

Ron Rosenfeld - 24 Aug 2007 21:22 GMT

>Thanks Ron and Dave.
>
[quoted text clipped - 13 lines]
>more descriptive. Unfortunately, since I knew I had the other workbook
>open, I already ruled out that as the issue.

Well, had you posted both that you had a #REF error and also that the target
workbook was open, that would have narrowed down the problem considerably and
I, too, would have looked elsewhere.

--ron

Reply to this Message