Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
formula in different worksheets | Excel Discussion (Misc queries) | |||
count formula between worksheets | Excel Discussion (Misc queries) | |||
I there an easy way to find out if any formula reference a cell? | New Users to Excel | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |