CODEWITHSUNDEEP
excelexcel-formula
Yigit Tanverdi
Yigit Tanverdi

Reputation: 161

finding first empty row to adjust the range in formal

Here is an UPDATE:

I have the formal below which i want to change range D4:D8 to D4:First empty cell in D. Basically i need to make my range dynamical and not limited to D8

 =IF(
  COUNTIF(D4:D8;"Completion delayed")>0;"Completion delayed";IF(
  COUNTIF(D4:D8;"Start delayed")>0;"Start delayed";IF(
  COUNTIF(D4:D8;"In progress")>0;"In progress";IF(
  AND(COUNTIF(D4:D8;"Not started")>0;COUNTIF(D4:D8;"Done")>0);"In progress";IF(
  COUNTIF(D4:D8;"Not started")>0;"Not started";IF(
  COUNTIF(D4:D8;"Done")>0;"Done";"N/A"
))))))

Can anyone help me with this?

Upvotes: 0

Views: 52

Answers (2)

mmehta
mmehta

Reputation: 103

=IF(
  COUNTIF(INDIRECT("D4:D" & A1);"Completion delayed")>0;"Completion delayed";IF(
  COUNTIF(INDIRECT("D4:D" & A1);"Start delayed")>0;"Start delayed";IF(
  COUNTIF(INDIRECT("D4:D" & A1);"In progress")>0;"In progress";IF(
  AND(COUNTIF(INDIRECT("D4:D" & A1);"Not started")>0;COUNTIF(INDIRECT("D4:D" & A1);"Done")>0);"In progress";IF(
  COUNTIF(INDIRECT("D4:D" & A1);"Not started")>0;"Not started";IF(
  COUNTIF(INDIRECT("D4:D" & A1);"Done")>0;"Done";"N/A"
))))))

Upvotes: 1

ingwarus
ingwarus

Reputation: 412

Get number of first empty row

=MATCH(TRUE;D4:D1048576="";0)+3

as ArrayFormula (Ctrl+Shift+Enter) in other cell (for example A1) and use it to get correct reference by INDIRECT

(...) COUNTIF(INDIRECT("D4:D" & A1);"Completion delayed")>0 (...)

Upvotes: 0

Related Questions