|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 29th, 2010, 01:51 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
count blank cells to next value
Hello,
I am using Excel 2007. Column A records individual sales. Column C records the date that bulk payments are made to suppliers. When a date is entered into Column C, I would like Column B to sum the value in Column A up to the previous bulk payment. For example, when the date is entered into C7, B7 should sum A7:A2. A B C 1 14.40 14.40 15/03/2010 2 9.00 3 9.00 4 9.00 5 9.00 6 9.00 7 9.00 54.00 25/03/2010 Any assistance would be appreciated. 
|
|
#2
March 29th, 2010, 02:30 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
count blank cells to next value
On B2 try:
=if(ISNUMBER(C2),sum(A$1:A2)-sum(B$1:B1),"") and copy it down. Best of luck! -- Thanx & Best Regards, Faraz! "Malika" wrote: Hello, I am using Excel 2007. Column A records individual sales. Column C records the date that bulk payments are made to suppliers. When a date is entered into Column C, I would like Column B to sum the value in Column A up to the previous bulk payment. For example, when the date is entered into C7, B7 should sum A7:A2. A B C 1 14.40 14.40 15/03/2010 2 9.00 3 9.00 4 9.00 5 9.00 6 9.00 7 9.00 54.00 25/03/2010 Any assistance would be appreciated.
|
|
#3
March 29th, 2010, 02:32 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
count blank cells to next value
Try this ARRAY formula
=IF(C1="","",SUM(A1:INDEX(A:A,MAX(1,MIN(IF($C$1:$C 1="",ROW($B$1:$B1))))))) -- HTH Bob "Malika" wrote in message ... Hello, I am using Excel 2007. Column A records individual sales. Column C records the date that bulk payments are made to suppliers. When a date is entered into Column C, I would like Column B to sum the value in Column A up to the previous bulk payment. For example, when the date is entered into C7, B7 should sum A7:A2. A B C 1 14.40 14.40 15/03/2010 2 9.00 3 9.00 4 9.00 5 9.00 6 9.00 7 9.00 54.00 25/03/2010 Any assistance would be appreciated.
|
|
#5
March 29th, 2010, 03:32 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
count blank cells to next value
Hello Faraz,
Many, many thanks for sharing your knowledge. Your suggestion worked perfectly. Much appreciated. Malika "Faraz Ahmed Qureshi" wrote: On B2 try: =if(ISNUMBER(C2),sum(A$1:A2)-sum(B$1:B1),"") and copy it down. Best of luck! -- Thanx & Best Regards, Faraz! "Malika" wrote: Hello, I am using Excel 2007. Column A records individual sales. Column C records the date that bulk payments are made to suppliers. When a date is entered into Column C, I would like Column B to sum the value in Column A up to the previous bulk payment. For example, when the date is entered into C7, B7 should sum A7:A2. A B C 1 14.40 14.40 15/03/2010 2 9.00 3 9.00 4 9.00 5 9.00 6 9.00 7 9.00 54.00 25/03/2010 Any assistance would be appreciated.
|
|
#6
March 29th, 2010, 04:15 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
count blank cells to next value
Hello Bob,
Thank-you very much for your suggestion. Although I already have a solution (provided by Faraz), I was keen to try your suggestion as well. What I found though was that the formula totals all the values in Column A (i.e. if I enter a date in C7, B7 totals A1:A7, if there is a date in C7 and I enter a date in C8, B8 totals A1:A8 instead of just A8.) While it is not the solution for the problem I posted, it is a solution I was looking for in another workbook. So thank-you very much. Malika "Bob Phillips" wrote: Try this ARRAY formula =IF(C1="","",SUM(A1:INDEX(A:A,MAX(1,MIN(IF($C$1:$C 1="",ROW($B$1:$B1))))))) -- HTH Bob "Malika" wrote in message ... Hello, I am using Excel 2007. Column A records individual sales. Column C records the date that bulk payments are made to suppliers. When a date is entered into Column C, I would like Column B to sum the value in Column A up to the previous bulk payment. For example, when the date is entered into C7, B7 should sum A7:A2. A B C 1 14.40 14.40 15/03/2010 2 9.00 3 9.00 4 9.00 5 9.00 6 9.00 7 9.00 54.00 25/03/2010 Any assistance would be appreciated. .
|
|
#7
March 29th, 2010, 04:28 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
count blank cells to next value
Hello Don,
Many, many thanks for your advice. The code works beautifully on my current worksheet. However, if I could ask for a bit more help please. I tried to adapt it to another worksheet in which the data was in columns H, I and J, by changing Target.Column 10. But it didn't work correctly. I obviously need to do more but am not sure what. I would appreciate your further advice. Kind regards Malika "Don Guillett" wrote: Right click sheet tabview codeinsert this. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column 3 Or Target.Count 1 Then Exit Sub If Target.Row = 1 Then Target.Offset(, -1) = Cells(1, 1) Else fc = Target.End(xlUp).Row + 1 Target.Offset(, -1) = _ Application.Sum(Range(Cells(fc, 1), Cells(Target.Row, 1))) End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Faraz Ahmed Qureshi" wrote in message ... On B2 try: =if(ISNUMBER(C2),sum(A$1:A2)-sum(B$1:B1),"") and copy it down. Best of luck! -- Thanx & Best Regards, Faraz! "Malika" wrote: Hello, I am using Excel 2007. Column A records individual sales. Column C records the date that bulk payments are made to suppliers. When a date is entered into Column C, I would like Column B to sum the value in Column A up to the previous bulk payment. For example, when the date is entered into C7, B7 should sum A7:A2. A B C 1 14.40 14.40 15/03/2010 2 9.00 3 9.00 4 9.00 5 9.00 6 9.00 7 9.00 54.00 25/03/2010 Any assistance would be appreciated. .
|
|
#8
March 29th, 2010, 04:36 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
count blank cells to next value
Need to change the constants from ,1 to target.column-2. Look in help for
CELLS Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column 3 Or Target.Count 1 Then Exit Sub col1=target.column-2 If Target.Row = 1 Then Target.Offset(, -1) = Cells(col1, 1) Else fc = Target.End(xlUp).Row + 1 Target.Offset(, -1) = _ Application.Sum(Range(Cells(fc, col1), Cells(Target.Row, col1))) End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Malika" wrote in message ... Hello Don, Many, many thanks for your advice. The code works beautifully on my current worksheet. However, if I could ask for a bit more help please. I tried to adapt it to another worksheet in which the data was in columns H, I and J, by changing Target.Column 10. But it didn't work correctly. I obviously need to do more but am not sure what. I would appreciate your further advice. Kind regards Malika "Don Guillett" wrote: Right click sheet tabview codeinsert this. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column 3 Or Target.Count 1 Then Exit Sub If Target.Row = 1 Then Target.Offset(, -1) = Cells(1, 1) Else fc = Target.End(xlUp).Row + 1 Target.Offset(, -1) = _ Application.Sum(Range(Cells(fc, 1), Cells(Target.Row, 1))) End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Faraz Ahmed Qureshi" wrote in message ... On B2 try: =if(ISNUMBER(C2),sum(A$1:A2)-sum(B$1:B1),"") and copy it down. Best of luck! -- Thanx & Best Regards, Faraz! "Malika" wrote: Hello, I am using Excel 2007. Column A records individual sales. Column C records the date that bulk payments are made to suppliers. When a date is entered into Column C, I would like Column B to sum the value in Column A up to the previous bulk payment. For example, when the date is entered into C7, B7 should sum A7:A2. A B C 1 14.40 14.40 15/03/2010 2 9.00 3 9.00 4 9.00 5 9.00 6 9.00 7 9.00 54.00 25/03/2010 Any assistance would be appreciated. .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
