Indirect/Offset Column Reference

Hi all,

I need to sum a column on a worksheet based on a reference returned from INDIRECT. I am having trouble with *error here*

My formula is:

=Sumif(indirect($a$4),indirect($b$4),*error here* indirect($a$4)&"!" & column())

$A$4 & $B$4 are worksheet names.

Essentially: If the worksheet name in A4=B4, then sum all values in worksheetname.column(). I want to use the column() function because I have to drag it across 100 columns. The column length is variable (1-10000).

Any ideas? The problem I run into is that column() returns a number, not a letter. I tried changing to R1C1, but I'm not familar with that technique.

Thanks-DSV

In place of :

indirect($a$4)&"!" & column())

TRY :

INDIRECT($a$4 & "!" & CHAR(64+COLUMN()) & ":" & CHAR(64+COLUMN()) ) )

PeterB--

The CHAR(+column()) trick works, but only up to 26 columns (then the CHAR set devolves into something useless). I unfortunately have a minimum of 65 columns (2.5 years worth of weeks). Other than using something ugly with "mod" and "trunc", any other thoughts?

Cheers-DSV

Hi DSV,

I know it's messy (like you said), but it will take you up to 676 (26*26) columns :

INDIRECT($A$8 & "!" & IF(64+COLUMN()<91,CHAR(64+COLUMN()),CHAR(64+(INT((COLUMN()-1)/26)))&CHAR(64+(COLUMN()-(26*(INT((COLUMN()-1)/26)))))) & ":" & IF(64+COLUMN()<91,CHAR(64+COLUMN()),CHAR(64+(INT((COLUMN()-1)/26)))&CHAR(64+(COLUMN()-(26*(INT((COLUMN()-1)/26)))))) ) )

Haven't been able to think of another, cleaner method yet.

Peter

Yep--That's messy. And computationally heavy, once you start putting it in hundreds of cells.

If you think of something else, I'd much appreciate it, otherwise, I'm going to do this with vba.

I think this is the solution I was looking for (INDIRECT(ADDRESS()):

=SUM(INDIRECT(ADDRESS(6,COLUMN(),,,$B209)&":"&(ADDRESS(200,COLUMN()))))

Where $B209 is a worksheet name.

I've hardcoded the number of rows to sum (in this example 6:200), but there are plenty of techniques to find the last row of data in a column.

Thanks for the help,
DSV