Sum all cells where Sheet Name, Row, & Column are given

[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.


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.

My thanks in advance for reading this!


EDIT: Correctted the example long formula! MY APOLOGIES FOR THE CONFUSION!!!

Also posted this @ VBAExpress.com. Click here to see that thread

 

[NBVC]

I think you have to set "Always allow HTML" in your user profile.

 

[Cosmos75]

I think you have to set "Always allow HTML" in your user profile.

That fixed it! Thanks!

I took a look at your latest version sent via email and I think I understand what you did.

Instead of trying to change the summing formula to look at the column numbers in 'Sheet Ref', you fixed the Columns to instead look at 'Sheet Ref' and select the appropriate headers. The problem I have with doing that is that the sheets and respective headers are created first, and then 'Sheet Ref' is created based on how those sheets are created. All this being done manually. There is some consistency accross certain data sheets, but it is the number of data sheets for each type that can vary quite a bit.

The other thing is that the range of columns on the data sheets is going to be significantly quite large. Item1 on Sheet1 may be in column 4, whereas on Sheet2 it may be in column 90. Another way to look at it is that Sheet1 may have 5 items, Sheet2 may have 20 items.

I guess I could spend time writing a custom VBA function to do it how I want it done but it isn't worth the hassle and then I'd be the only one in my department who understood it!

So, there is a way only to use the information on 'Sheet Ref', i.e. Sheet Name/Row/Column, I think I will have to use your formula and instead ensure that the headers are always used consistently when the data sheets are constructed.

Guess I am just asking for too much!

 

[NBVC]

I think consistency is the best thing. Excel loves consistency.

Everybody wins....easier to understand, easier to follow, easier to build formulas.

By the way, you can enforce the use of the proper column titles, by way of Data Validation (ie. making a drop down list selection) or with Controls or VBA....

...where there is a will there is a way

 

[Cosmos75]

NBVC,

I hope you don't mind my asking another question.

I defined the 3 sheetnames as a Named Range, "tabnames"

Formula in B2: =SUMPRODUCT(SUMIF(INDIRECT("'"&tabnames&"'!A1:C1"),B$1,INDIRECT("'"&tabnames&"'!A"&ROW()&":C"&ROW()))) copied across and down.

Why does it have to be a named range? I removed the named range and used the following
=SUMPRODUCT(SUMIF(INDIRECT("'"&'Sheet Ref'!A2:A4&"'!A1:C1"),H$1,INDIRECT("'"&'Sheet Ref'!A2:A4&"'!A"&ROW()&":C"&ROW())))

This returns 0.

Adding back the named range and changing back the formula results in the correct value. There must something special about using a named range, but what is it?

 

[NBVC]

Oh but it doesn't have to be a named range (just makes it easier to deal with and reference), it worked for me (on the sheet we have been working on together):

=SUMPRODUCT(SUMIF(INDIRECT("'"&'Sheet Ref'!$A$2:$A$4&"'!A1:C1"),B$1,INDIRECT("'"&'Sheet Ref'!$A$2:$A$4&"'!A"&ROW()&":C"&ROW())))

You had H$1 as your "criteria" argument in the SUMIF()... where are your column titles?

Also the Sheet Ref range must be absoluted with the $ signs in order for the range values to be consistent.

 

[mark007]

I've posted a solution here:

http://www.vbaexpress.com/forum/showthread.php?p=66553#post66553

The formula being:

=SUM(IF('Sheet Ref'!$B$2:$B$4="-",0,N(INDIRECT("'"&'Sheet Ref'!$A$2:$A$4&"'!R"&ROW()&"C"&'Sheet Ref'!$B$2:$B$4,FALSE))))