please see the attached sheet to help follow what i am saying. i need a formula that finds the word "Find Me" and performs the calculation written to the side of the word "find me", and stops with the word "finished". the difference in rows between "find me" and "finished" varies. i am at a loss on this one. hopefully someone out there is got the thinking cap on today!
Sum Rows Based On Location
-
-
-
Re: Sum Rows Based On Location
See attachment. Best I can do, cannot put the results where you want them.
-
Re: Sum Rows Based On Location
the only problem with your solution - that i should have spelled out better - is that there is data all up and down in column A. so if you were to use that formula you would have values returned all over.
-
Re: Sum Rows Based On Location
Hi z,
While a few ideas come to mind I think some clarification is needed:
Is the data layed out consistently? as in:
- Is the answer always required in Col C with "FIND ME" in Col A/same row?
- Will there always be a blank row between the last number in the column and the word "FINISHED"?
-Will the column of data be changing and need constant updates?
Cheers,
dr
-
Re: Sum Rows Based On Location
in reposonse to the previous reply, the data will always be in column "a" and it does need constant updating. the rows in the column and text will be changing all the time. yes there is always a blank under last entry before the word "finished." it is not nessecary to have the results in column "c" they can go anywhere even on another sheet, likewise with the formulas. i am at a total loss on this one - please help!
-
-
Re: Sum Rows Based On Location
Based on your example input formula in cell B7 custom format cell to [COLOR="DarkRed"]0;-0;;@[/COLOR] and copied down.
=LOOKUP(10^307,CHOOSE({1,2},0,IF(A7="",SUM(B$7+INDEX($B$6:$B6,MATCH(9.99999999E+307,B$6:B6))),A7-A6)))
Hope it helps!
-
Re: Sum Rows Based On Location
Hi zotee,
No indication that you got what you need so here's a Sheet_Change sub that (I think) will do what you want.
It does, however require the numbers to be consecutive (no blank rows in series).
What it does:-Gets the current range of numbers IF they are in a series (no blank rows)
-finds first and last row
-does arithmeticWhat it doesn't do (as written):
-doesn't regard deletion/insertion of a row as a change
-doesn't regard deletion of a number in the series (blank row) as a changeTherefore it requires the numbers to be in a series.
Note: If this is useful and blank rows needed to be dealt with it could be done.
As always, try this on a COPY of your worksheet.
[vba]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'
' Finds and recalculates CURRENT instance of "FIND ME"
'
Dim i As Long
Dim c As Range
Dim amt As Long
Dim lastrow As Long
Dim firstrow As Long
Dim firstaddress As String'handle error that escapes checks
On Error GoTo reset
'turn off screen for speed
Application.ScreenUpdating = False
'stop this sub from firing on change
Application.EnableEvents = False
'check if change in Col A
If Not Intersect(Target, Range("A:A")) Is Nothing Then
'check if change was number
If Not IsNumeric(Target) Or Target = "" Then GoTo reset
firstrow = Target.End(xlUp).Row + 2
'get last number in series
lastrow = Cells(firstrow, 1).End(xlDown).Row
'do arithmetic
For i = lastrow To firstrow Step -1
amt = amt + Cells(i, 1) - Cells(i - 1, 1)
Next i
'put result in Col C at "FIND ME"
Cells(firstrow - 2, 3) = amt
End Ifreset:
Application.ScreenUpdating = True
Application.EnableEvents = TrueEnd Sub
[/vba]
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!