|
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 |
=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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 10:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com