I've seen no activity here for some time so maybe you've lost interest. If
not, it's possible to use a form of the INDEX function which returns a
specified column of a range. If the range to be summed is a contiguous range
of cells in the column, then simply apply the SUM function to the returned
column.
For example, assume your data is in Sheet 2, cells A1:L20, with the month
headers in A1:L1. As the previous responder suggested, it's a little neater
if you define a name "months" which in this case would refer to
=Sheet2!$A$1:$L$1
On Sheet 1, assume your column headers are also in row 1, and that in some
cell in column A you want a formula to sum the values in rows 10 through 20
of the appropriate column in sheet 2 (with header matching the header in
Sheet 1 cell A1). Use:
=SUM(INDEX(Sheet2!$A$10:$L$20,,MATCH(A$1,months,0) ))
If the cells to be summed do not lie in a contiguous range, then you can
resort to a SUMPRODUCT formula. For example, if you want to sum the values
in rows 10, 12, 15, 17, and 20 of the appropriate column, you could use
=SUMPRODUCT(INDEX(Sheet2!$A$10:$L$20,,MATCH(A$1,mo nths,0))*(ROW($10:$20)={10,12,15,17,20}))
"Aussie CPA" wrote:
Hi,
I'm hoping someone could please help me.
I have a spreadsheet setup which has on one sheet columns of data that
represent data for a particular month. The column heading is setup to advance
forward a month depending on the point in time during the year ( eg in August
2005 the first column heading is set at Sep-05. In Sep-05 the first column
heading will become Oct-05 and so on).
I want to populate each of the columns with data that looks at another
worksheet with information arranged in columns defined by a particular month.
The month headings in this sheet are fixed and do not change.
I can't simply put a sum formula in the first sheet though as the applicable
column the formula looks at will change depending on the month. (ie. The
applicable sum formula may be in column B one month but need to be in column
A the following month).
I realise I can simply move the formulas one column to the left each change
in month however I was hoping to have the sheet function automatically.
I have a hunch an array formula may help but I am not that skilled in them.
Hopefully my question makes sense. Can someone help please?
Thanks,
Adam Wood
|