Column() Function Leading To #VALUE! Error

3LeggedDog

Board Regular
Joined
Feb 11, 2006
Messages
149
Unfortunately, I'm at my work computer at the moment, and am not able to copy the sheet over, but I will try to describe the issue here.

At work, I use Excel 2003. I have a formula that essentially mimics the 2007 SumIfs function, while calling a range from a different sheet. In order to make the sheet easily scaleable, I tried to use the column() function, which I've used many times without incident. When I just put in the specific column that I want, the formula works perfectly, but when I replace the column number with the column() function, I get a #VALUE! error.

I ran the Formula Evaluator to determine where the issue was coming up - for some reason, the column() call is evaluating to a {3} (or 4, 5, 6, etc depending on the column), rather than a regular 3. I've never seen a formula evaluate out this way, with braces, and I'm not sure what could be causing that. Here's the entire formula:

=SUMPRODUCT(INDIRECT(CONCATENATE("Sheet1!",ADDRESS(5,MATCH(INDIRECT(ADDRESS(1,COLUMN())),Sheet1!$A$4:$CC$4,0)),":",ADDRESS(5000,MATCH(INDIRECT(ADDRESS(1,COLUMN())),Sheet1!$A$4:$CC$4,0)))),INDIRECT(CONCATENATE("Sheet1!",ADDRESS(5,MATCH($A3,Sheet1!$A$4:$CC$4,0)),":",ADDRESS(5000,MATCH($A3,Sheet1!$A$4:$CC$4,0)))),INDIRECT(CONCATENATE("Sheet1!",ADDRESS(5,MATCH(INDIRECT(ADDRESS(2,COLUMN())),Sheet1!$A$4:$CC$4,0)),":",ADDRESS(5000,MATCH(INDIRECT(ADDRESS(2,COLUMN())),Sheet1!$A$4:$CC$4,0)))))

The relevant part, though, is here:
=SUMPRODUCT(INDIRECT(CONCATENATE("Sheet1!",ADDRESS(5,MATCH(INDIRECT(ADDRESS(1,COLUMN())),Sheet1!$A$4:$CC$4,0)),":",ADDRESS(5000,MATCH(INDIRECT(ADDRESS(1,COLUMN())),Sheet1!$A$4:$CC$4,0))))

Again, I wish that I could post this using Colo's HTML maker, but I don't have it here at work, and adding things onto the computers at work is probably frowned upon.

Any help would be appreciated - I'm just really puzzled by why the column() call would evaluate in a way that I've never seen before.

Thanks,
3LD
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

3LeggedDog

Board Regular
Joined
Feb 11, 2006
Messages
149
Aladin,
I misspoke (mistyped) earlier when I implied that this particular formula simulates SumIfs - the columns that I'm taking the dot product of with SumProduct have a binary value in each cell. Essentially I'm trying to get the dotproduct of three columns, and two of those columns are just binary.

I will try to post some informative stuff from the sheet itself tomorrow when I'm back at work.

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,099
Messages
5,570,210
Members
412,309
Latest member
Benky
Top