I have a series of formulae across several columns like this

in say T10
=SUMIF(mqs_col,$N14,'Main Working Paper'!Z$15:Z$2033)

where the last reference (in this case Z15:Z2033) varies in each
column. The reference doesn't change consistently, i.e the formula in
the next column U10 is not to AA15:AA2033, but something else.

In T1 I have a formulae which evaluates to a cell reference, say
M$15

I need to substitute the reference to Col M found in T1, in place of
the reference to Z so that I end up with

=SUMIF(mqs_col,$N14,'Main Working Paper'!M$15:M$2033)

so that I can then copy this across many more columns, U,V W, X
etc...

It's not practical to use Find and Replace since the column references
are different and I have about 200 columns to change.

I'm sure an Indirect construction is required, something like

=INDIRECT("SUMIF(mqs_col,$N14,Main Working
Paper!"&left(T1,1)&"15:"&left(T1,1)&"2033")

but can't seem to get it quite right and get #REF!

Can anyone help please?

Usual TIA


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi!

Try this:

Will T1 always be a reference to row 15 ?

=SUMIF(mqs_col,N14,INDIRECT("'main working paper'!"&T1&":"&LEFT(T1)&"2033"))

Biff

"Richard Buttrey" wrote in
message ...
I have a series of formulae across several columns like this

in say T10
=SUMIF(mqs_col,$N14,'Main Working Paper'!Z$15:Z$2033)

where the last reference (in this case Z15:Z2033) varies in each
column. The reference doesn't change consistently, i.e the formula in
the next column U10 is not to AA15:AA2033, but something else.

In T1 I have a formulae which evaluates to a cell reference, say
M$15

I need to substitute the reference to Col M found in T1, in place of
the reference to Z so that I end up with

=SUMIF(mqs_col,$N14,'Main Working Paper'!M$15:M$2033)

so that I can then copy this across many more columns, U,V W, X
etc...

It's not practical to use Find and Replace since the column references
are different and I have about 200 columns to change.

I'm sure an Indirect construction is required, something like

=INDIRECT("SUMIF(mqs_col,$N14,Main Working
Paper!"&left(T1,1)&"15:"&left(T1,1)&"2033")

but can't seem to get it quite right and get #REF!

Can anyone help please?

Usual TIA


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Richard,

It would be better to put the whole range (M15:M2033) in T1 and use

=SUMIF(mqs_col,$N14,INDIRECT("'Main Working Paper'!"&T1))

or if you want a variable last row somehow, then at least the letter (M15:M)
and use

=SUMIF(mqs_col,$N14,INDIRECT("'Main Working Paper'!"&T1&"2033"))

just makes it easier to read and maintain.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Richard Buttrey" wrote in
message ...
I have a series of formulae across several columns like this

in say T10
=SUMIF(mqs_col,$N14,'Main Working Paper'!Z$15:Z$2033)

where the last reference (in this case Z15:Z2033) varies in each
column. The reference doesn't change consistently, i.e the formula in
the next column U10 is not to AA15:AA2033, but something else.

In T1 I have a formulae which evaluates to a cell reference, say
M$15

I need to substitute the reference to Col M found in T1, in place of
the reference to Z so that I end up with

=SUMIF(mqs_col,$N14,'Main Working Paper'!M$15:M$2033)

so that I can then copy this across many more columns, U,V W, X
etc...

It's not practical to use Find and Replace since the column references
are different and I have about 200 columns to change.

I'm sure an Indirect construction is required, something like

=INDIRECT("SUMIF(mqs_col,$N14,Main Working
Paper!"&left(T1,1)&"15:"&left(T1,1)&"2033")

but can't seem to get it quite right and get #REF!

Can anyone help please?

Usual TIA


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Wed, 24 May 2006 22:56:41 +0100, "Bob Phillips"
wrote:

Richard,

It would be better to put the whole range (M15:M2033) in T1 and use

=SUMIF(mqs_col,$N14,INDIRECT("'Main Working Paper'!"&T1))

or if you want a variable last row somehow, then at least the letter (M15:M)
and use

=SUMIF(mqs_col,$N14,INDIRECT("'Main Working Paper'!"&T1&"2033"))

just makes it easier to read and maintain.

Thanks Bob - (and Biff)

I see now my main mistake was to have the Indirect before the SumIf...
doh!

Good idea though about having the whole reference in T1.

Incidentally I hope to be going to the Excel conference in London in
July so I'll make a point of coming to say Hello.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Please do, it will be good to meet you.

Regards

Bob

"Richard Buttrey" wrote in
message ...
On Wed, 24 May 2006 22:56:41 +0100, "Bob Phillips"
wrote:

Richard,

It would be better to put the whole range (M15:M2033) in T1 and use

=SUMIF(mqs_col,$N14,INDIRECT("'Main Working Paper'!"&T1))

or if you want a variable last row somehow, then at least the letter
(M15:M)
and use

=SUMIF(mqs_col,$N14,INDIRECT("'Main Working Paper'!"&T1&"2033"))

just makes it easier to read and maintain.

Thanks Bob - (and Biff)

I see now my main mistake was to have the Indirect before the SumIf...
doh!

Good idea though about having the whole reference in T1.

Incidentally I hope to be going to the Excel conference in London in
July so I'll make a point of coming to say Hello.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________