Use cell reference to look for worksheet name in diff workbook?

[Jill Pleau]

I would like to use a cell reference A1 = Jan-03 to look up
worksheet named "Jan-03" in another workbook. Is this possible?

TIA

Jill

P.S. Great Forum

 

[Frank Kabel]

Hi Jill

if the other workbook is open you can use the function INDIRECT
=INDIRECT("'[Other_Sheet.cls]" & A1 & "'!$A$1")

This won't work if the other workbook is closed. You can use the
following Add-In MOREFUC.XLL (see http://longre.free.fr/english/)
The function INDIRECT.EXT will overcome this Excel restriction

HTH
Frank

 

[Jill Pleau]

This formula returns a #REF!.

If I understand correctly, I have replaced A1 with the cell I wish to
match my worksheet to. (in both cases?).

I really don't understand the syntax. What it looks like to me is:

"OtherWookBook.xls & thiscell & thiscell"

Should I put an ! following the first 'thiscell' to indicate that it is
a worksheet name?

TIA

Jill

 

[Harlan Grove]

This formula returns a #REF!.

If I understand correctly, I have replaced A1 with the cell I wish to
match my worksheet to. (in both cases?).

I really don't understand the syntax. What it looks like to me is:

"OtherWookBook.xls & thiscell & thiscell"

Should I put an ! following the first 'thiscell' to indicate that it is
a worksheet name?

From this it'd seem a good bet you didn't use the *EXACT* formula Frank gave
you. Most definitely you need to use ! after the worksheet name. What you
put inside INDIRECT must look like a valid external reference, so the
workbook name must appear inside square brackets, the worksheet name must
follow immediately, an exclamation point must follow that, and the cell
address come last. Since single quotes are needed when there are spaces or a
few other characters in workbook filenames or worksheet names but are
innocuous when there aren't any such, best to add them all the time. So
start with a single quote and end with another immediately before the
exclamation point.

"'[" workbook_filename_w/o_brackets &"]" & worksheet_name & "'!" & cell_ref

 

[Jill Pleau]

It looks like you two were and are working on a similar thread as w
speak! Much of the other thread is over my head - but I do conside
myself fairly proficient in excel, so... here's what I did, I copie
Frank's formula and replaced his info with mine. example:

Frank's
=INDIRECT("'[Other_Sheet.cls]" & A1 & "'!$A$1")

Jill's
=INDIRECT("'[Download.xls]" & V4 & "'!$V$4")


The cell I am putting my formula in (V6) returns #REF!

Am I missing the boat?

Thanks,

Jil

 

[Frank Kabel]

Hi Jill
1. First question: Is the workbook 'Download.xls' open?. If not this
would explain your #REF! error. For this I suggested to use the free
add-in MOREFUNC.XLL.
So just try to open the workbook 'Download.xls' and see if the REF
error disappear.

2. The only other problem could be the entry of V4. Maybe you can post
the entry of this cell.

Besides that your formula look o.k. for me.
Frank

 

[Harlan Grove]

...
...

Jill's
=INDIRECT("'[Download.xls]" & V4 & "'!$V$4")

The cell I am putting my formula in (V6) returns #REF!

...

Is the file Download.xls open in Excel? If not, INDIRECT won't work - Frank
mentioned that. If this file is open in Excel, are you *certain* the worksheet
name in cell V4 matches an actual worksheet name in Download.xls exactly? One
thing to check would be whether the worksheet name in Download.xls contains
leading or trailing space characters. If it does, you need to include those
space characters along with the rest of the worksheet name in cell V4.

Another way to test, enter the formula

="='[Download.xls]" & V4 & "'!$V$4"

in a blank cell in the same worksheet that contains your formula above. This
would give a text string as its result. Copy it and paste-special as value in
some other blank cell. It'll still be a text string in this other cell. Now
press [F2] followed by [Enter] in this cell. Is the result #REF! ? If so, what
is the exact result from

="='[Download.xls]" & V4 & "'!$V$4"

?

 

[Jill Pleau]

My other workbook is open. But I am wondering about something else.

Cell V4 is a date 'Mar-04' (this date is actually a formula '=A4').
See below when I pasted Harlan's test text, the 'V4' was converted to
number. So it must be looking for that number as a worksheet, correct
My worksheet name is "Mar-04".

TEST TEXT:
="='[Downloaded PDR Files.xls]37681'!$V$4

When I did the test I received an error message, that my formul
contains an invalid external reference to a worksheet. etc.

Thanks,

Jil

 

[Frank Kabel]

Hi Jill

now we are getting to your problem. I assumed that the value in V4 was
text. but as you have entered a date (which is internaly represented by
a number) your problem arises. One suggestion: Change the formula in V4
to
=TEXT(A4,"MMM-YY")
to convert the date to text. This should work. If not maybe you send
your spreadsheet to me and I#ll look at it

Frank

 

[Harlan Grove]

...
...

Cell V4 is a date 'Mar-04' (this date is actually a formula '=A4').
See below when I pasted Harlan's test text, the 'V4' was converted to a
number. So it must be looking for that number as a worksheet, correct?
My worksheet name is "Mar-04".

TEST TEXT:
="='[Downloaded PDR Files.xls]37681'!$V$4

When I did the test I received an error message, that my formula
contains an invalid external reference to a worksheet. etc.

It's always in the details.

Your Mar-04 in cell V4 is a formatted date value. It's actually a number, and
it's the underlying number that appears in the formula above.

Change your V4 cell's formula to =TEXT(A4,"MMM-YY") and see if that clears
things up.