Indirect function

[Guest]

i have a workbook containing calendars of different persons.

each calendar has the same formatting. the sheets' names are composed of
the surname & name of the person concerned (e.g. "SmithW", "DoverG").

in each sheet, i named the following rows:
- date : PER_[+Letter] ranging from B1 to IV1
- value : VAL_[+Letter] ranging from B2 to IV2

i.e.:
- in the calendar of SmithW, the date row is named "PER_A" and the value row
"VAL_A"
- in the calendar of DoverG, the date row is named "PER_B" and the value row
"VAL_B"

i would now like to sum all the values in these different worksheets where a
date condition is met. so add all the values from SmithW and DoverG for
July-2007.

to do so, i used the following function :

=SUMIF(INDIRECT("PER_"&{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K"});N3;INDIRECT("VAL_"&{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K"}))

however, the only values that are returned are those from SmithW.

when i change the formula as follows:
=SUMIF(INDIRECT("PER_"&{"B"});N3;INDIRECT("EU_"&{"B"}))
the values of DoverG are returned correctly.

i use ";" because i use the european version of excel 2003. when i use ","
an error is returned. i also tried changing the "{}" brackets into "()", but
that didn't work either.

questions:
- how does the indirect function work?
- is it possible to add 'wildcards' to the function in order to reduce its
size. for example : =SUMIF(INDIRECT("PER_"&{*});N3;INDIRECT("EU_"&{*}))
where * is the wildcard

thanks for helping.
andy

 

[Guest]

Try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$N$4:$N$30&"'!B1:IV1"),"=" &
N3,INDIRECT("'"&$N$4:$N$30&"'!B2:IV2")))

Enter with Ctrl+Shift+Enter

N4 to N30 contain your worksheet names

HTH

 

[Peo Sjoblom]

,"="&N3,

can be replaced by

,$N3,

only when you use <>>=<= do you really need the ampersand


not that it really matters since it works


--
Regards,

Peo Sjoblom

Try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$N$4:$N$30&"'!B1:IV1"),"=" &
N3,INDIRECT("'"&$N$4:$N$30&"'!B2:IV2")))

Enter with Ctrl+Shift+Enter

N4 to N30 contain your worksheet names

HTH

i have a workbook containing calendars of different persons.

each calendar has the same formatting. the sheets' names are composed of
the surname & name of the person concerned (e.g. "SmithW", "DoverG").

in each sheet, i named the following rows:
- date : PER_[+Letter] ranging from B1 to IV1
- value : VAL_[+Letter] ranging from B2 to IV2

i.e.:
- in the calendar of SmithW, the date row is named "PER_A" and the value
row
"VAL_A"
- in the calendar of DoverG, the date row is named "PER_B" and the value
row
"VAL_B"

i would now like to sum all the values in these different worksheets
where a
date condition is met. so add all the values from SmithW and DoverG for
July-2007.

to do so, i used the following function :

=SUMIF(INDIRECT("PER_"&{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K"});N3;INDIRECT("VAL_"&{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K"}))

however, the only values that are returned are those from SmithW.

when i change the formula as follows:
=SUMIF(INDIRECT("PER_"&{"B"});N3;INDIRECT("EU_"&{"B"}))
the values of DoverG are returned correctly.

i use ";" because i use the european version of excel 2003. when i use
","
an error is returned. i also tried changing the "{}" brackets into "()",
but
that didn't work either.

questions:
- how does the indirect function work?
- is it possible to add 'wildcards' to the function in order to reduce
its
size. for example : =SUMIF(INDIRECT("PER_"&{*});N3;INDIRECT("EU_"&{*}))
where * is the wildcard

thanks for helping.
andy

 

[Guest]

Another variant: "MySheets" is a named range containing your list of sheets

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!B1:IV1"),
$N$3,INDIRECT("'"&MySheets&"'!B2:IV2")))

,"="&N3,

can be replaced by

,$N3,

only when you use <>>=<= do you really need the ampersand


not that it really matters since it works


--
Regards,

Peo Sjoblom

Try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$N$4:$N$30&"'!B1:IV1"),"=" &
N3,INDIRECT("'"&$N$4:$N$30&"'!B2:IV2")))

Enter with Ctrl+Shift+Enter

N4 to N30 contain your worksheet names

HTH

i have a workbook containing calendars of different persons.

each calendar has the same formatting. the sheets' names are composed of
the surname & name of the person concerned (e.g. "SmithW", "DoverG").

in each sheet, i named the following rows:
- date : PER_[+Letter] ranging from B1 to IV1
- value : VAL_[+Letter] ranging from B2 to IV2

i.e.:
- in the calendar of SmithW, the date row is named "PER_A" and the value
row
"VAL_A"
- in the calendar of DoverG, the date row is named "PER_B" and the value
row
"VAL_B"

i would now like to sum all the values in these different worksheets
where a
date condition is met. so add all the values from SmithW and DoverG for
July-2007.

to do so, i used the following function :

=SUMIF(INDIRECT("PER_"&{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K"});N3;INDIRECT("VAL_"&{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K"}))

however, the only values that are returned are those from SmithW.

when i change the formula as follows:
=SUMIF(INDIRECT("PER_"&{"B"});N3;INDIRECT("EU_"&{"B"}))
the values of DoverG are returned correctly.

i use ";" because i use the european version of excel 2003. when i use
","
an error is returned. i also tried changing the "{}" brackets into "()",
but
that didn't work either.

questions:
- how does the indirect function work?
- is it possible to add 'wildcards' to the function in order to reduce
its
size. for example : =SUMIF(INDIRECT("PER_"&{*});N3;INDIRECT("EU_"&{*}))
where * is the wildcard

thanks for helping.
andy