1

This can be easy for many but i do not find any solution for this.

I have a following formula which finds the minimum of a range.

MIN(INDIRECT("F12:F"&A2))

However, when i insert a row before f12, the formula does not adjust it to:

MIN(INDIRECT("F13:F"&A2))

Can anyone help me so formula adjusts itself automatically to F13?

Soru Soravic
  • 97
  • 1
  • 9
  • Is there a reason you need to use `INDIRECT`? – ashleedawg Apr 04 '18 at 12:33
  • yes i have to use it, because in cell A2 i have a value, this value is a number which i combine with my F. If a2 has value 20 then my range will be F12:F20 so thatswhy i used indirect – Soru Soravic Apr 04 '18 at 12:34
  • Why? What's in `A2`? – ashleedawg Apr 04 '18 at 12:34
  • edited my answer – Soru Soravic Apr 04 '18 at 12:35
  • Why don't you just have another cell that's hidden somewhere with `=ROW(F12)` then reference that `=MIN(INDIRECT("F"&[Cell]&":F"&A2))` that cell will auto update the row when things are inserted... If the second row number is, let's say, the last cell with data in, there might be better ways to go about this than using indirect though. – Glitch_Doctor Apr 04 '18 at 12:38

2 Answers2

2

If you want the formula to return the minimum value of cells between F12 (which will change when you insert rows) and a cell address that is stored in A2 (which will not change when you insert rows) then you could use:

=MIN(INDIRECT(ADDRESS(ROW(F13),COLUMN(F13))&":F"&A2))

Suggested Reading:

  • Overview of Absolute versus Relative cell references. (See "crash course" at the bottom of this answer.)
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • 1
    I know you already have an answer, but going back to what ashleedawg originally asked you - the reason for `INDIRECT`. Is the value in `A2` the result of a formula? Is `A2` meant to contain the last row number of cells that aren't empty? I'm asking because there might be a better way to reference the range (INDEX & MATCH for example). – Darren Bartrup-Cook Apr 04 '18 at 13:08
  • @SoruSoravic Listen to DarrenBartrup-Cook - there *is* a better way. This one's bound to cause you problems, I foresee it! – ashleedawg Apr 04 '18 at 13:23
  • A1 contains the first empty row number. For example if first empty row after D4 is D9. Then A1 reads 9. Similar for A2 also, if first empty row after D10 is D19 then A2 should give the value 19 (remember first empty row after D4 was A1) – Soru Soravic Apr 04 '18 at 14:19
1

Here a solution without Indirect since it is volatile. 

=MIN(F12:INDEX(F:F,A2))
MGP
  • 2,480
  • 1
  • 18
  • 31