PDA

View Full Version : [SOLVED] Please help with formula



Juriemagic
10-30-2015, 05:50 AM
hi good people,

Would someone please be so kind to modify this formula for me so that it would be dynamic?. The formula is:
=INDEX($C$5:$C$1000,MATCH(0,COUNTIF($C$5:$C$1000,"<"&$C$5:$C$1000)-SUM(COUNTIF($C$5:$C$1000,"="&AW$14:AW14)),0))

The problem is that the range is not always up to 1000. All and any help will be greatly appreciated!..thank you guys..

mancubus
10-30-2015, 06:34 AM
instead of $C$5:$C$1000

if there are no blank cells:
$C$5:OFFSET($C$1,COUNTA(C:C)-1,0)

if there are blank cells:
INDIRECT("$C$5:$C$"&SUMPRODUCT(MAX(($C:$C<>"")*(ROW(C:C)))))

Juriemagic
10-30-2015, 06:47 AM
Splendid!!!!..There are never blanks. Thank you very very much mancubus. Your time is really appreciated!..

mancubus
10-30-2015, 07:13 AM
you are welcome.
pls mark the thread as solved.

credits go to those who first built these formulas. :)

Juriemagic
11-01-2015, 10:09 PM
Absolutely..thank you..