Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 20th 05, 12:47 PM
Matt D Francis
 
Posts: n/a
Default 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   Report Post  
Old May 20th 05, 01:29 PM
Bob Phillips
 
Posts: n/a
Default

=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   Report Post  
Old May 20th 05, 03:01 PM
Matt D Francis
 
Posts: n/a
Default

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   Report Post  
Old May 20th 05, 03:33 PM
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
Old May 20th 05, 04:13 PM
Matt D Francis
 
Posts: n/a
Default

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   Report Post  
Old May 20th 05, 04:32 PM
Bob Phillips
 
Posts: n/a
Default

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














Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF & VLOOKUP FORMULA taxmom Excel Worksheet Functions 3 March 2nd 05 03:35 PM
formula in different worksheets Monty Excel Discussion (Misc queries) 4 December 30th 04 08:46 PM
count formula between worksheets Seti Excel Discussion (Misc queries) 3 December 9th 04 09:25 PM
I there an easy way to find out if any formula reference a cell? Marc New Users to Excel 1 December 6th 04 10:41 PM
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? 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.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017