Tanya,

The INDIRECT function does what you want. Put the sheet names in a
separate column, so when you copy down, the next formula picks up the
next sheet name. For example, with sheet names in column A,
C3 contains =CONCATENATE(B3," ",INDIRECT(A3&"!E5"),"
",INDIRECT(A3&"!E12")," ",INDIRECT(A3&"!E19"),".")
The cell addresses do not need the absolute form $E$5 because they are
text and won't increment with copy down.

Unless I already have a comma-separated list to put into CONCATENATE,
I prefer the concatenate operator over the function (it just seems
like less typing!):
C3 contains =B3&" "&INDIRECT(A3&"!E5")&" "&INDIRECT(A3&"!E12")&"
"&INDIRECT(A3&"!E19")&"."
If sheet names contain spaces, then the column A values must be
surrounded with apostrophes:
C3 contains =B3&" "&INDIRECT("'"&A3&"'!E5")&" "&INDIRECT("'"&A3&"'!
E12")&" "&INDIRECT("'"&A3&"'!E19")&"."

Problem 2
If column L has something that indicates male or female then
C3 formula is B3&" "&INDIRECT("'"&A3&"'!E5")&"
"&IF(L3="male","he","she")&" "&INDIRECT("'"&A3&"'!E12")&"
"&INDIRECT("'"&A3&"'!E19")&"."

Carl.

On May 11, 9:51 pm, Tanya wrote:
Hi I have two problems
Number 1 I have the following formula in my workbook [on sheet3] and I need
to copy the formula down, which is fine for the absolute address of
individual cells, however my problem is I need the formula to ref to the next
sheet when I copy it down. I wondered if there could be a simpler method by
using a formula macro.

=CONCATENATE(B3," ",'S1'!$E$5," ","He/She"," ",'S1'!$E$12," and
",'S1'!$E$19,".")

Problem number 2
I would like to concatenate he/she into the formula based on a whether a
student is male or female. Is this possible?

Kind Regards
Tanya