Remember Me?

#1
May 20th 05, 12:47 PM
 Matt D Francis Posts: n/a
getting a formula to find named worksheets in another?

Hi,

I have a worksheet which needs to does a calcualtion on data in another
workbook containing several worksheets.

Workbook 1 has something like

Col A Col B
Apr-05 =formula
May-05 =formula

Workbook 2 has several worksheets called
Apr-5
May-5

So I need my formulas in Workbook 1 to find the appropriate worksheet in
Workbook 2 (identified in the adjacent) before doing their calcualation.

It looks like I need to use maybe the INDIRECT function, or named ranges but
I can't quite get it...

Any suggestions?

Cheers,

Matt

#2
May 20th 05, 01:29 PM
 Bob Phillips Posts: n/a

=INDIRECT("[myBook.xls]"&A1&"!A1")

but the other workbook must be open.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Matt D Francis" wrote in message
...
Hi,

I have a worksheet which needs to does a calcualtion on data in another
workbook containing several worksheets.

Workbook 1 has something like

Col A Col B
Apr-05 =formula
May-05 =formula

Workbook 2 has several worksheets called
Apr-5
May-5

So I need my formulas in Workbook 1 to find the appropriate worksheet in
Workbook 2 (identified in the adjacent) before doing their calcualation.

It looks like I need to use maybe the INDIRECT function, or named ranges

but
I can't quite get it...

Any suggestions?

Cheers,

Matt

#3
May 20th 05, 03:01 PM
 Matt D Francis Posts: n/a

Hi,

Thanks for the reply but I can't get this to work. I replaced mybook.xls
with the appropriate file name, but I get #REF???

"Bob Phillips" wrote:

=INDIRECT("[myBook.xls]"&A1&"!A1")

but the other workbook must be open.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Matt D Francis" wrote in message
...
Hi,

I have a worksheet which needs to does a calcualtion on data in another
workbook containing several worksheets.

Workbook 1 has something like

Col A Col B
Apr-05 =formula
May-05 =formula

Workbook 2 has several worksheets called
Apr-5
May-5

So I need my formulas in Workbook 1 to find the appropriate worksheet in
Workbook 2 (identified in the adjacent) before doing their calcualation.

It looks like I need to use maybe the INDIRECT function, or named ranges

but
I can't quite get it...

Any suggestions?

Cheers,

Matt

#4
May 20th 05, 03:33 PM
 Bob Phillips Posts: n/a

Matt,

IT works fine for me, just tested it again.

You other workbook is open isn't it?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Matt D Francis" wrote in message
...
Hi,

Thanks for the reply but I can't get this to work. I replaced mybook.xls
with the appropriate file name, but I get #REF???

"Bob Phillips" wrote:

=INDIRECT("[myBook.xls]"&A1&"!A1")

but the other workbook must be open.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Matt D Francis" wrote in

message
...
Hi,

I have a worksheet which needs to does a calcualtion on data in

another
workbook containing several worksheets.

Workbook 1 has something like

Col A Col B
Apr-05 =formula
May-05 =formula

Workbook 2 has several worksheets called
Apr-5
May-5

So I need my formulas in Workbook 1 to find the appropriate worksheet

in
Workbook 2 (identified in the adjacent) before doing their

calcualation.

It looks like I need to use maybe the INDIRECT function, or named

ranges
but
I can't quite get it...

Any suggestions?

Cheers,

Matt

#5
May 20th 05, 04:13 PM
 Matt D Francis Posts: n/a

Yes, I figured the problem, needed single quotes round the workbook/worksheet
for it to work on mine.

Thanks a lot though - still got it sorted!

Matt

"Bob Phillips" wrote:

Matt,

IT works fine for me, just tested it again.

You other workbook is open isn't it?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Matt D Francis" wrote in message
...
Hi,

Thanks for the reply but I can't get this to work. I replaced mybook.xls
with the appropriate file name, but I get #REF???

"Bob Phillips" wrote:

=INDIRECT("[myBook.xls]"&A1&"!A1")

but the other workbook must be open.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Matt D Francis" wrote in

message
...
Hi,

I have a worksheet which needs to does a calcualtion on data in

another
workbook containing several worksheets.

Workbook 1 has something like

Col A Col B
Apr-05 =formula
May-05 =formula

Workbook 2 has several worksheets called
Apr-5
May-5

So I need my formulas in Workbook 1 to find the appropriate worksheet

in
Workbook 2 (identified in the adjacent) before doing their

calcualation.

It looks like I need to use maybe the INDIRECT function, or named

ranges
but
I can't quite get it...

Any suggestions?

Cheers,

Matt

#6
May 20th 05, 04:32 PM
 Bob Phillips Posts: n/a

Sorry, I always forget that as I don't tend to embed spaces in the names.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Matt D Francis" wrote in message
...
Yes, I figured the problem, needed single quotes round the

workbook/worksheet
for it to work on mine.

Thanks a lot though - still got it sorted!

Matt

"Bob Phillips" wrote:

Matt,

IT works fine for me, just tested it again.

You other workbook is open isn't it?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Matt D Francis" wrote in

message
...
Hi,

Thanks for the reply but I can't get this to work. I replaced

mybook.xls
with the appropriate file name, but I get #REF???

"Bob Phillips" wrote:

=INDIRECT("[myBook.xls]"&A1&"!A1")

but the other workbook must be open.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Matt D Francis" wrote in

message
...
Hi,

I have a worksheet which needs to does a calcualtion on data in

another
workbook containing several worksheets.

Workbook 1 has something like

Col A Col B
Apr-05 =formula
May-05 =formula

Workbook 2 has several worksheets called
Apr-5
May-5

So I need my formulas in Workbook 1 to find the appropriate

worksheet
in
Workbook 2 (identified in the adjacent) before doing their

calcualation.

It looks like I need to use maybe the INDIRECT function, or named

ranges
but
I can't quite get it...

Any suggestions?

Cheers,

Matt

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post taxmom Excel Worksheet Functions 3 March 2nd 05 03:35 PM Monty Excel Discussion (Misc queries) 4 December 30th 04 08:46 PM Seti Excel Discussion (Misc queries) 3 December 9th 04 09:25 PM Marc New Users to Excel 1 December 6th 04 10:41 PM Arvi Laanemets Excel Discussion (Misc queries) 0 December 2nd 04 11:29 AM

All times are GMT +1. The time now is 11:56 AM.

The comments are property of their posters.