-2

How can i select all data in between 2 rows from other columns. Please see attached photo.

Sample excel photo:

enter image description here

Note that ending row criteria is dynamic row. the number of data to be selected is not constant. I just want to select a all data from column B in contrast to the starting and ending row names (content start and end). Thank you!

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
John Michael Joshua Tanhueco
  • 1
  • 1
  • As a formula or macro? Also how do you know where you want it start or if you don’t know that can you pick an ending spot? – Daniel Butler Jul 09 '20 at 02:55
  • as a formula. The "trigger/mark" for ending and start will always be the keyword "Total Cash in/Total Cash out" out respectively. The only thing that's not constant is the number of rows between them. – John Michael Joshua Tanhueco Jul 09 '20 at 03:17
  • Like a bank rec! I used to do a lot of stuff like this in exce but migrated to python and pandas because excel formulates break all the time – Daniel Butler Jul 09 '20 at 03:35
  • What have you already tried? – markblandford Jul 09 '20 at 07:41
  • This is the closest '=INDEX(C:C,MATCH("Total Cash In",A:A,0),1):INDEX(C:C,MATCH("Total Cash Out",A:A,0),1) here is the result of it : https://ibb.co/wRwvS8T the only problem i have with it is that it shows an error for other rows, the goal is for it to be blank. – John Michael Joshua Tanhueco Jul 09 '20 at 08:44
  • You should add your comment as an Edit to your question, so others can see it. And the formula you write in your comment will not give the results you show in your screenshot, making it unclear where your error lies. – Ron Rosenfeld Jul 09 '20 at 10:23

1 Answers1

0

The formula below will extract the SUM of the range you specify. Of course, there are other things you might do with the range.

=SUM(OFFSET($B4,0,0,MATCH(FALSE,ISBLANK($A$4:$A$4000),0)-1))

Key to this function is the assumption that the "Content End" row in column A is the first non-blank cell in column A after the first cell of the range ($B$4 in the above formula). The "Content Start" cell is immaterial but it might be used for defining the first cell of the range if that is required. If the intervening rows aren't blank you can adjust the MATCH function to look for "Total Cash out" instead of False.

The range $A$4:$A$4000 is arbitrary but the first cell must be on the same row as the start cell of the range. I recommend a named range in place of the address.

Note that the MATCH formula was advertised as an array formula but it works as normal on Excel 365. If you have a problem with the formula try entering it with Ctl+Shift+Enter

Variatus
  • 14,293
  • 2
  • 14
  • 30