Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a spreadsheet I'm working on that we use to run large corporate events. Each segment length has a length as well as corresponding start time. I want each cell in the time column to look above itself and find the previous time and then add it to that segments length.

In column A it has the start time of that segment. Then, in column B, it has the segment length. So for example, A1: start time & B1: segment length. I then want B2 or C2 or D2 (whichever is next) to go up and look for A1 and add it to B1.

The problem is that there are often blank rows in between so even though they are blank, it returns an error with the offset function statically moving 1 row at a time.

I can provide a screen shot if that helps. But, in short, I'm trying to dynamically find the cell above a given cell with content in it knowing that there could be may blank rows in between and not wanting them to calculate.

share|improve this question
1  
Hi Conway, welcome to Web Applications !! The segment length is then also time based, like A1 = 12:00:00, B1 = 00:35:00 and E1 = 02:25:00. The total time would be Z1 = 15:00:00? A screenshot often tells more, then words can describe !! –  Jacob Jan Tuinstra Oct 3 '13 at 6:31
1  
And even better than a screenshot, is a link to the spreadsheet in question (if you're able to share it). –  Vidar S. Ramdal Oct 9 '13 at 14:44

1 Answer 1

Here is one way to achieve the desired effect: get the value in the last non-empty cell in column A above the current row.

B2  =OFFSET(A1,-LEN(REGEXEXTRACT(JOIN("|",A$1:A1),"\|*$")),0)

(And propagate down the B column). This works under the assumption that none of your column A cells end with character | (if they are dates, they really shouldn't). Another character could be used instead of |.

What the command does:

  1. JOIN("|",A$1:A1) joins the content of column A, above the current row, separating the values with |.
  2. REGEXEXTRACT(...,"\|*$") extracts only the characters | at the end of the string. (This particular character needs to be escaped with a backslash in a regex.) The idea is that these characters correspond to blank cells that we must jump over.
  3. LEN takes the length of the result, i.e., the number of blank cells at the end.
  4. OFFSET jumps up by that many rows.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.