Cosmos75
06-07-2006, 05:52 PM
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:
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. (http://www.mrexcel.com/board2/viewtopic.php?p=1035580).
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:
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. (http://www.mrexcel.com/board2/viewtopic.php?p=1035580).