Here is my problem:
I have a cell (V4) containing the value 444. I want to use this value in the formula of another cell (M12) in the following way. I want the formula to be equivalent to =MIN(L12:L444) but instead of 444 I want to refer to cell V4 which contains the value 444. But when I type in =MIN(L12:L(V4)) it obviously doesnt work so how do I do it? Sorry if I didn't explain it very well. :S
INDIRECT will work and is closest to the solution you described, but I prefer OFFSET, which uses proper references. (For example, if you insert a column in the sheet before L, INDIRECT will break while OFFSET will just update its reference as expected.
Two ways to go with OFFSET:
1 - Start at L$1 and go down $V$4-1 rows. (This will work with $V$4 as you've defined it now.)
=MIN(L12:OFFSET(L$1,$V$4-1,0))
2 - In $V$4, provide the height of the range you want.
=MIN(OFFSET(L12,0,0,$V$4,0))
It's hard to make suggestions without more context, but I'm sure you can tweak one of these patterns to meet your needs.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments