Selecting range from bottom visual cell containing a value in a column upwards

19jack91

New Member
Joined
Aug 22, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi all, I'm hoping someone is able to advise if there is a formaula able to select a range of cells starting from the very bottom cell in a column, which contains a value, and is visible within a filtered column, up 25 cells. This is so that a formula I have only looks in that range, which is from the bottom cell to the 25th cell above in a single row. To give a bit more information, I have an existing fomula that inputs a range of cells as one of its parameters. However, I'm looking for that range input to be be from the very last value in a column to (and including) 25 cells up towards the top. Is this possible? Thanks in advance.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,026
Office Version
  1. 2019
Platform
  1. Windows
Welcome to Mr Excel :)

Do all 25 cells have to be visible, or just the bottom one?

What you ask should be possible but it will need some (possibly quite complex) volatile formulas, with a large amount of data this could equate to slow calculation times.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,026
Office Version
  1. 2019
Platform
  1. Windows
How you go about this would also depend on what you want to do with that range once it has been identified, a simple SUM or COUNT would not be too difficult once the range is identified, anything beyond that could be close to impossible.
 

19jack91

New Member
Joined
Aug 22, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Thank you so much for your quick reply! So I have a spreadsheet where I filter a couple of columns, then look at another column (BQ) to work with the values that are subsequently presented there. My original intention was to find a formula that calculated the number of visible rows in this BQ coulmn that had a value greater than 0.1. I ended up finding a solution which led to the creation of this somewhat complex formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(BQ3:BQ50000,ROW(BQ3:BQ50000)-MIN(ROW(BQ3:BQ50000)),,1)),--(BQ3:BQ50000>0.1)).
I have to admit I don't fully understand all of its variables, but it did the job. The amount of rows changes depending on what is filtered, and can appear in any row number between this quite large range of BQ3:BQ50000. Within this range is where I'm trying to have only the last 25 visible cells of the BQ column selected, but the row locations of the cells can be in literally any of the rows in the BQ3:BQ50000 range. And I'm only looking to get the last 25 cells that are visible, so not including any filtered out rows between each of these cells. I hope that makes sense, and thanks again for your help.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,026
Office Version
  1. 2019
Platform
  1. Windows
I ended up finding a solution which led to the creation of this somewhat complex formula:
If you think that was complex you might want to close your eyes :eek:

This is still counting the number of rows in BQ with a value >0.1, but is limited to the last 25 visible rows. I've assumed that there will always be at least 25 visible rows, if there are less then the formula will result in a #NUM! error. This can be fixed, but the method of doing so would depend on whether the count should be done on the available rows or void.

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET($BQ$3,ROW(INDEX($BQ:$BQ,AGGREGATE(14,6,ROW($BQ$3:$BQ$50000)/SUBTOTAL(3,OFFSET($BQ$3,ROW($BQ$3:$BQ$50000)-1,)),25)):INDEX($BQ:$BQ,AGGREGATE(14,6,ROW($BQ$3:$BQ$50000)/SUBTOTAL(3,OFFSET($BQ$3,ROW($BQ$3:$BQ$50000)-1,)),1)))-1,,1,1))>0.1))
 

19jack91

New Member
Joined
Aug 22, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Wow, that's absolutely incredible! It worked perfectly :love: Thank you so much, I would never have been able to figure that all out!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,421
Messages
5,547,823
Members
410,813
Latest member
Vhinzvirgo
Top