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

C

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
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.
:cry:

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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Does this work?...untested


=SUM(INDIRECT(ADDRESS(A2,'Sheet Ref'!B2,4,1,'Sheet Ref'!A2)):INDIRECT(ADDRESS(A25,'Sheet Ref'!B25,4,1,'Sheet Ref'!A25)))
 
Upvote 0
NBVC, Thank you for your reply. However, that did not work. Perhaps it'll help if I post some sample data.

SHEET CALC
SumSheetsRowColumn.xls
ABCD
1RowItem 1Item 2Item 3
21191611
329134
43121612
549914
651635
7620420
8722158
9811910
10923714
111015918
12SUM(INDIRECT(ADDRESS()),
Sheet Calc


SHEET REF
SumSheetsRowColumn.xls
ABCD
1Sheet NameItem 1Item 2Item 3
2Sheet1123
3Sheet2312
4Sheet31-2
Sheet Ref


Sheet1
SumSheetsRowColumn.xls
ABCD
1Item 1Item 2Item 3
2865
3090
4395
5223
6520
7826
81061
9346
10879
11798
Sheet1


EDIT: Sheet2 & Sheet 3 got cut off... so I removed it
 
Upvote 0
Seems a little confusing...what are the extra arguments in the Address functions for in this case?

I tried a quick sample and got a result for this, that makes sense....does it work for your sample?

=SUM(INDIRECT(ADDRESS(A2,'Sheet Ref'!B2)):INDIRECT(ADDRESS(A25,'Sheet Ref'!B25)))
 
Upvote 0
Seems a little confusing...what are the extra arguments in the Address functions for in this case?
Click to expand...
You're right, I can do with less of the arguments.

Here's the HELP file explanation on the other arguments for Address()
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
Row_num is the row number to use in the cell reference.
Column_num is the column number to use in the cell reference.
Abs_num specifies the type of reference to return.
A1 is a logical value that specifies the A1 or R1C1 reference style. If a1 is TRUE or omitted, ADDRESS returns an A1-style reference; if FALSE, ADDRESS returns an R1C1-style reference.
Sheet_text is text specifying the name of the worksheet to be used as the external reference. If sheet_text is omitted, no sheet name
is used.

I can take out Abs_num and A1, but I need to keep Sheet_text as 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)

Here's 'Sheet Calc', but this time with a manual addition example. Hopefully that will make it clear as to what I am attempting to accomplish.
SumSheetsRowColumn.xls
ABCDEFG
1RowItem 1Item 2Item 3Item 1Item 2Item 3
21191611191611
3291349134
43121612121612
5499149914
6516351635
762042020420
872215822158
981191011910
1092371423714
11101591815918
12SUM(INDIRECT(ADDRESS()),Manual
Sheet Calc
 
Upvote 0
Check your PM, please...I am having trouble understanding your request.
 
Upvote 0
I hope this resolves the problem:
SumSheetsRowColumn.xls
ABCDE
1RowItem 1Item 2Item 3
21191611
329134
43121612
549914
651635
7620420
8722158
9811910
10923714
111015918
12SUM(INDIRECT(ADDRESS()),
13
Sheet Calc


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.
 
Upvote 0
NBVC,

I just replied to your last email. Thought I should add what I wrote here as well in case it helps others.

You method is really close but still not exactly what I need. I need to use the column number rather than the text value in the first row, i.e. the column header. That because they may not all share the same column header.

Trying to tweak it but I am not 100% sure I understand how it works (hence why it took so long for my reply to you). I will work in it some more.

Thanks!
:biggrin:

EDIT: Also posted this @ VBAExpress.com. Click here to see that thread
 
Upvote 0
Hi Cosmos75,

It's going to be a liittle difficult, and probably confusing, to do what you are asking. I tried something else. Take a look at the attached. I used a formula in the headers of Sheet1 to Sheet3 to extract the proper Item # from the Sheet Ref tab. This way you can keep the formula I supplied in my last post.

What do you think?

Here is you 'Sheet Ref!' Sheet:
SumSheetsRowColumn(ver2).xls
ABCD
1Sheet NameItem 1Item 2Item 3
2Sheet1123
3Sheet2312
4Sheet31-2
Sheet Ref


Here is an example of one of your sheets..."Sheet2!"
SumSheetsRowColumn(ver2).xls
ABCD
1Item 2Item 3Item 1
21057
3404
4752
57105
6124
7266
8944
9547
100410
11076
Sheet2


Formula in A1: =INDEX('Sheet Ref'!$B$1:$D$1,MATCH(COLUMN(),INDEX('Sheet Ref'!$B$2:$D$4,MATCH(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255),tabnames,0),0),0)) copied over to column C and the same is done for each sheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Sum referenced cells in Sheet range using SUM(INDIRECT()) #REF! ERROR
2
Replies
12
Views
294
Fluff
Fluff
K
  • Solved
Dynamic Sum Range Addresses...(?)
Replies
2
Views
69
Kenny_D
K
S
  • Question
Adding same cell across dynamic sheet range
Replies
3
Views
344
arthurbr
arthurbr
N
  • Solved
Sum a range of cells defined by a string
Replies
4
Views
235
Nerdio
N
A
  • Question
Indirect Function with a separate Workbook
Replies
6
Views
994
AntMac
A

Forum statistics

Threads
1,215,738
Messages
6,126,579
Members
449,319
Latest member
iaincmac

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top