using & in part of a cell referance e.g. '[book1]sheet1'!totals&text(a1,"dddd")

Posted by zen on June 20, 2001 6:09 AM

hello all,
i'm having problem using the & thingy in a referance, the formula was ='Hours Report WE....xls'!totalsmonday which is fine, i'd like to use ='Hours Report WE....xls'!totals plus a referance to cell on the sheet the formula is on, i.e. i want to extract the day from a date =text(a1,"dddd") and use the words monday, tuesday etc as the end of the ref. how would write this???

thanks for any help

zen

Posted by cpod on June 20, 2001 6:38 AM

=INDIRECT("[book1.xls]sheet1!Totals" & TEXT(A1,"dddd"))

This only works when both workbooks are open.

Posted by zen on June 20, 2001 6:58 AM

top banana cpod, did you get my e.mail??

: hello all, : i'm having problem using the & thingy in a referance, the formula was ='Hours Report WE....xls'!totalsmonday which is fine, i'd like to use ='Hours Report WE....xls'!totals plus a referance to cell on the sheet the formula is on, i.e. i want to extract the day from a date =text(a1,"dddd") and use the words monday, tuesday etc as the end of the ref. how would write this???