Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with dynamic sum formula
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 |
#2
|
|||
|
|||
On Thu, 29 Sep 2005 16:38:01 -0700, "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 You can put a formula on sheet 1, and don't need to use array formulae. There are no doubt other solutions, but try this one. Assuming your information is in cells C1:E10 on sheet2, and that row 1 contains the month name headings, give the range C1:E10 the name "data", and C1:E1 the name "months" . On sheet1 Assuming the column heading is in A6 and that A6 is in the same format as the month headings on sheet2, enter the following in A7 =INDEX(data,ROW()-5,MATCH(A$6,months)) Then copy this down column A to A15. You'll have to change the "-5" bit to match your layout. This is the difference in this example, between the "6" of A6, and the row "1" of the range C1:E1 "months" range on sheet2 HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
|
|||
|
|||
Richard,
Thanks for your response. I have tried your suggestion however the problem I have is that I need to sum several rows in the "data" range once the column is selected. I can't seem to get the INDEX to do this? Appreciate the assistance. Adam Wood "Richard Buttrey" wrote: On Thu, 29 Sep 2005 16:38:01 -0700, "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 You can put a formula on sheet 1, and don't need to use array formulae. There are no doubt other solutions, but try this one. Assuming your information is in cells C1:E10 on sheet2, and that row 1 contains the month name headings, give the range C1:E10 the name "data", and C1:E1 the name "months" . On sheet1 Assuming the column heading is in A6 and that A6 is in the same format as the month headings on sheet2, enter the following in A7 =INDEX(data,ROW()-5,MATCH(A$6,months)) Then copy this down column A to A15. You'll have to change the "-5" bit to match your layout. This is the difference in this example, between the "6" of A6, and the row "1" of the range C1:E1 "months" range on sheet2 HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
|
|||
|
|||
Adam,
OK, what are the rules for identifying which rows to sum? If you'd like to offer a small example I'll have another look. Rgds On Thu, 29 Sep 2005 18:51:02 -0700, "Aussie CPA" wrote: Richard, Thanks for your response. I have tried your suggestion however the problem I have is that I need to sum several rows in the "data" range once the column is selected. I can't seem to get the INDEX to do this? Appreciate the assistance. Adam Wood "Richard Buttrey" wrote: On Thu, 29 Sep 2005 16:38:01 -0700, "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 You can put a formula on sheet 1, and don't need to use array formulae. There are no doubt other solutions, but try this one. Assuming your information is in cells C1:E10 on sheet2, and that row 1 contains the month name headings, give the range C1:E10 the name "data", and C1:E1 the name "months" . On sheet1 Assuming the column heading is in A6 and that A6 is in the same format as the month headings on sheet2, enter the following in A7 =INDEX(data,ROW()-5,MATCH(A$6,months)) Then copy this down column A to A15. You'll have to change the "-5" bit to match your layout. This is the difference in this example, between the "6" of A6, and the row "1" of the range C1:E1 "months" range on sheet2 HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
Setting dynamic range in a formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Named SUM Formula with relative refernce(s) | Excel Worksheet Functions | |||
Indirect and dynamic ranges | Excel Worksheet Functions |