Hi
I've looked around the forums and tried a few things but still unable to get the solution i'm looking for and hope you can help.
I'm currently using this formula on a summary page of a workbook.....
=SUMIF(INDIRECT("'"&$D$61&"'!$A:$A"),$C9,(INDIRECT("'"&$D$61&"'!$V:$V")))
- Summary sheet - the formula is in this sheet.
- D61 contains text (Jan Data) which refers to another sheet within the workbook.
- A:A is the range in Jan Data and contains the word i'm looking for (Apples) as well as pears, oranges etc.
- C9 is on the summary page and contains the text to match (Apples)
- V:V is the range in Jan Data containing the numbers (summed) that I need returning
So far so good. The formula looks at Jan Data, picks out the rows with Apples, sums those rows and returns the correct data. The idea of using indirect is that I can change D61 every month to read Feb Data, Mar Data......and not change anything else.
However, I need to change the range every month as the columns change (which i'd like to do automatically.. the whole point of the workbook). I've set up lookup tables in the Summary sheet so that I know which column the relevant data is in but I can't find a way of feeding this into the formula so it picks it up automatically. Ideally i'd put a cell reference (A1) (with the range in, A:A) which I then refer to in place of !$A:$A". Obviously this doesn't work as it looks at A1 in Jan Data.
Hope I didn't go on too long. Any ideas gratefully received.
Liam
I've looked around the forums and tried a few things but still unable to get the solution i'm looking for and hope you can help.
I'm currently using this formula on a summary page of a workbook.....
=SUMIF(INDIRECT("'"&$D$61&"'!$A:$A"),$C9,(INDIRECT("'"&$D$61&"'!$V:$V")))
- Summary sheet - the formula is in this sheet.
- D61 contains text (Jan Data) which refers to another sheet within the workbook.
- A:A is the range in Jan Data and contains the word i'm looking for (Apples) as well as pears, oranges etc.
- C9 is on the summary page and contains the text to match (Apples)
- V:V is the range in Jan Data containing the numbers (summed) that I need returning
So far so good. The formula looks at Jan Data, picks out the rows with Apples, sums those rows and returns the correct data. The idea of using indirect is that I can change D61 every month to read Feb Data, Mar Data......and not change anything else.
However, I need to change the range every month as the columns change (which i'd like to do automatically.. the whole point of the workbook). I've set up lookup tables in the Summary sheet so that I know which column the relevant data is in but I can't find a way of feeding this into the formula so it picks it up automatically. Ideally i'd put a cell reference (A1) (with the range in, A:A) which I then refer to in place of !$A:$A". Obviously this doesn't work as it looks at A1 in Jan Data.
Hope I didn't go on too long. Any ideas gratefully received.
Liam
