References to sheets from cells???

[pdvreg]

Cel A2 contains:
G:\Directoryname\workbook.xls]

Cel A10.. etc contains worksheet names
SheetA
SheetB
SheetC
....



In B10 I want to have the content of SheetA A1 cel
=ADDRESS(1;1;1;TRUE;$A$2&$A10)
gives
'G:\Directoryname\workbook.xls]SheetA'!$A$1

=INDIRECT(ADDRESS(1;1;1;TRUE;$A$2&$A10))
displays the value in that field.


*Now comes my question:*


In C10 I want to get the sum from the range $B$3:$B$303 from SheetA

Which formule delivers me that result???


With kind regards

Pvd

 

[Bob Phillips]

=SUM(INDIRECT(A2&A10&"!B3:B10")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
try
=SUM(INDIRECT("'[" & A2 & "]" & A10 & "'!B3:B303"))

Note: INDIRECT works only with open workbooks. So no need to give the
path information in cell A2 as this won't work if the file is closed.
But if the file is opened no need for a path string. So A2 contains
smething like
workbook.xls

also no need for the ADDRESS function in your example. Simply use
INDIRECT("'[" & A2 & "]" & A10 & "'!A1")

 

[pdvreg]

The

=INDIRECT("'["&$A$2&"]"&A10&"'!$A$1")

and

=SUM(INDIRECT("'[" & $A$2 & "]" & A10 & "'!B$3:B$303"))

are working very well.



Though I had to change the content of cell A2: leaving out drive an
directorypathname, using only:

workbook.xls


This is no problem, as I understood that it has to be open anyway.


Thank you both, it will save me lots of work!!!

With kind regards,

Pd