Solved: Sum all cells using Sheet Name, Row number, & Column number

[Cosmos75]

I have two sheets;

Sheet Ref
It has several columns with data
1) Column A - Name of a worksheet in the current workbook
2) Column B - Indicates either a number or "-"
3) Column C - Indicates either a number or "-"
4) Column D - Indicates either a number or "-"
5) Column E - Indicates either a number or "-"
The numbers in columns B through E indicate the number of a column, if applicable. If not, then a "-" is shown.

Sheet Calc
1) Column A - Indicates a particular row number

What I want is to have another column in 'Sheet Calc' that will add up certain cells. The individual cells that I want to add up can be found using Indirect() & Address()
=INDIRECT(ADDRESS(A2,'Sheet Ref'!B2,4,1,'Sheet Ref'!A2))

But my sum formula ends up being very long, e.g;
=Sum(INDIRECT(ADDRESS(A2,'Sheet Ref'!B2,4,1,'Sheet Ref'!A2)),
INDIRECT(ADDRESS(A2,'Sheet Ref'!B3,4,1,'Sheet Ref'!A3)),
INDIRECT(ADDRESS(A2,'Sheet Ref'!B4,4,1,'Sheet Ref'!A4)),
INDIRECT(ADDRESS(A2,'Sheet Ref'!B5,4,1,'Sheet Ref'!A5)),
...
INDIRECT(ADDRESS(A2,'Sheet Ref'!B24,4,1,'Sheet Ref'!A24)),
INDIRECT(ADDRESS(A2,'Sheet Ref'!B25,4,1,'Sheet Ref'!A25)))

Also, I have to make sure that I am not including any rows in 'Sheet Ref' that contains "-" instead of a number.


I am trying to sum cells that are
- on different sheets (Column A on 'Sheet Ref' contains the sheet's name)
- on the same row (Column A on 'Sheet Calc' contains the relative row number under the header row)
- on the different columns (Column B through D on 'Sheet Ref' contain the columns that a particular 'field' is in on a particular sheet)

Is there a better way to do this using formulas?

Or is this one situation where I will have to write up a VBA function to handle this? I would prefer to try and avoid using VBA.

I am attaching a sample spreadsheet.

My thanks in advance for reading this!


Before I get accused of cross-posting without notifiying forum members, I started this post at MrExcel's forum. Click here to see that post..

[mark007]

Ok, have attached a solution for you.

[Cosmos75]

mark007,

Thanks for checking this thread out and for the cool solution!


I kinda guessed that an array formula would have to be used. I think that may slow the calculations down too much since I will have a lot of rows that need this formula.

I will either have to consistently use the same headers or use a custom VBA function. I wrote one but can't seem to find the file. I will post it here once I find it.

THANKS ONCE AGAIN!

[Cosmos75]

I will either have to consistently use the same headers or use a custom VBA function. I wrote one but can't seem to find the file. I will post it here once I find it.

Can't find the file , so I'll just go ahead and mark this one solved.

If I manage to find that file, I'll either post back or edit this posting.

THANKS!