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
__________________________