- Joined
- Jan 30, 2008
- Messages
- 1
- Reaction score
- 0
Hello,
I have a spreadsheet with about 26 tabs in it, each one has 7 columns with data (layout identical in all tabs). The first column is a date column starting about 1993 to present day (1 days worth of data per row). So thats 26 tabs with ~ 5- 6000 rows per tab.
Now, I have created a sheet (tab 27) that has successfully been able to accept a single date from a user and LOOKUP the values for a certain column eg user enteres 12/02/2008 and all the values for the relevant column of data are updated in the summary tab.
Now, this data is numeric and it would be tremendous if I could supply a date range inthis summary tab and get Excel to SUM all the values from one date to the other eg you enter 01/02/2005 and 01/02/2006 and excel sums all the values in one column and returns that value to the summary sheet.
I havent succeeded in this. The annoying thing is I have used the following formula to give me the cell references I require for start and finish but getting those into a formula is proving a bind.
the formula I used to return the range of values is as follows for the initial part (start date entered into C1)
ADDRESS(ROW('A01'!$A:$A)+MATCH($C$1,'A01'!$A:$A,0)-1,COLUMN('A01'!$B:$B))
and this is what I used for the second part of the range in a different cell (C2)
ADDRESS(ROW('A01'!$A:$A)+MATCH($C$2,'A01'!$A:$A,0)-1,COLUMN('A01'!$B:$B))
So I now have 2 cells in excel showing the cell references I need to sum between. Is there any way to force these into a formula such that I can get excel to automatically sum the values for a specified column on each tab between (including) the cell references the formulas above have given?
eg SUM('A01'! cell refernce 1:cell reference 2)
Our IT dept tried and failed. If its VBA I need then anyone got a script that may help?
Best wishes
Colin
I have a spreadsheet with about 26 tabs in it, each one has 7 columns with data (layout identical in all tabs). The first column is a date column starting about 1993 to present day (1 days worth of data per row). So thats 26 tabs with ~ 5- 6000 rows per tab.
Now, I have created a sheet (tab 27) that has successfully been able to accept a single date from a user and LOOKUP the values for a certain column eg user enteres 12/02/2008 and all the values for the relevant column of data are updated in the summary tab.
Now, this data is numeric and it would be tremendous if I could supply a date range inthis summary tab and get Excel to SUM all the values from one date to the other eg you enter 01/02/2005 and 01/02/2006 and excel sums all the values in one column and returns that value to the summary sheet.
I havent succeeded in this. The annoying thing is I have used the following formula to give me the cell references I require for start and finish but getting those into a formula is proving a bind.
the formula I used to return the range of values is as follows for the initial part (start date entered into C1)
ADDRESS(ROW('A01'!$A:$A)+MATCH($C$1,'A01'!$A:$A,0)-1,COLUMN('A01'!$B:$B))
and this is what I used for the second part of the range in a different cell (C2)
ADDRESS(ROW('A01'!$A:$A)+MATCH($C$2,'A01'!$A:$A,0)-1,COLUMN('A01'!$B:$B))
So I now have 2 cells in excel showing the cell references I need to sum between. Is there any way to force these into a formula such that I can get excel to automatically sum the values for a specified column on each tab between (including) the cell references the formulas above have given?
eg SUM('A01'! cell refernce 1:cell reference 2)
Our IT dept tried and failed. If its VBA I need then anyone got a script that may help?
Best wishes
Colin