formula array in a macro

[Guest]

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

 

[Carl Hartness]

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.

 

[Guest]

Thank you Sooo much, I have not heard of the INDIRECT function before and I
had seen the '&' used in place of concatenate but hadn't realised how it can
save so much typing.

I am still tinkering with your solution to problem 2, it just won't accept
the value 'M' or 'male'. I will look at it again tomorrow night and will get
back to you.

Again THANK YOU.
Regards
Tanya

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.

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

 

[Guest]

Carl I finally managed to get back to the problem and you have again been
very helpful with problem number two.

Thank You!!

Tanya

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.

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