Fórmulas Google Sheets — tb4 auto-populate (sem LET)

Fórmula 1 — fk_id_ev (coluna B de tb4)

Colar em tb4!B2, arrastar para baixo:

=IFERROR(IF(ROW()-1<=COUNTIF(tb3_evidence_raw!$H:$H;1);INDEX(tb3_evidence_raw!$B:$B;SMALL(IF(tb3_evidence_raw!$H$2:$H=1;ROW(tb3_evidence_raw!$H$2:$H));ROW()-1));IF(ROW()-1<=COUNTIF(tb3_evidence_raw!$H:$H;1)+COUNTIF(tb3_evidence_raw!$I:$I;1);INDEX(tb3_evidence_raw!$B:$B;SMALL(IF(tb3_evidence_raw!$I$2:$I=1;ROW(tb3_evidence_raw!$I$2:$I));ROW()-1-COUNTIF(tb3_evidence_raw!$H:$H;1)));INDEX(tb3_evidence_raw!$B:$B;SMALL(IF(tb3_evidence_raw!$J$2:$J=1;ROW(tb3_evidence_raw!$J$2:$J));ROW()-1-COUNTIF(tb3_evidence_raw!$H:$H;1)-COUNTIF(tb3_evidence_raw!$I:$I;1)))));"")

Mesma fórmula com quebras de linha para leitura (colar a versão acima):

=IFERROR(
  IF(ROW()-1 <= COUNTIF(tb3_evidence_raw!$H:$H; 1);
    INDEX(tb3_evidence_raw!$B:$B;
      SMALL(IF(tb3_evidence_raw!$H$2:$H=1; ROW(tb3_evidence_raw!$H$2:$H)); ROW()-1));
  IF(ROW()-1 <= COUNTIF(tb3_evidence_raw!$H:$H; 1) + COUNTIF(tb3_evidence_raw!$I:$I; 1);
    INDEX(tb3_evidence_raw!$B:$B;
      SMALL(IF(tb3_evidence_raw!$I$2:$I=1; ROW(tb3_evidence_raw!$I$2:$I));
        ROW()-1 - COUNTIF(tb3_evidence_raw!$H:$H; 1)));
  INDEX(tb3_evidence_raw!$B:$B;
    SMALL(IF(tb3_evidence_raw!$J$2:$J=1; ROW(tb3_evidence_raw!$J$2:$J));
      ROW()-1 - COUNTIF(tb3_evidence_raw!$H:$H; 1) - COUNTIF(tb3_evidence_raw!$I:$I; 1)))
  ));
"")

Quando ROW()-1 excede o total de evidências classificadas, SMALL dá erro → IFERROR retorna "".

Fórmula 2 — fk_hypothesis_group (coluna C de tb4)

Colar em tb4!C2, arrastar para baixo:

=IF(B2="";"";IF(ROW()-1<=COUNTIF(tb3_evidence_raw!$H:$H;1);"H1";IF(ROW()-1<=COUNTIF(tb3_evidence_raw!$H:$H;1)+COUNTIF(tb3_evidence_raw!$I:$I;1);"H2";"H3")))

Fórmula 3 — pk_id_ev_an (coluna A de tb4)

Colar em tb4!A2, arrastar para baixo:

=IF(B2="";"";B2&"_"&C2)

Fórmula 4 — ik_ lookups (colunas D, E, F, G de tb4)

tb4!D2 — ik_date_evidence:

=IF($B2="";"";INDEX(tb3_evidence_raw!$C:$C;MATCH($B2;tb3_evidence_raw!$B:$B;0)))

tb4!E2 — ik_evidence_name:

=IF($B2="";"";INDEX(tb3_evidence_raw!$D:$D;MATCH($B2;tb3_evidence_raw!$B:$B;0)))

tb4!F2 — ik_complete_evidence_content:

=IF($B2="";"";INDEX(tb3_evidence_raw!$E:$E;MATCH($B2;tb3_evidence_raw!$B:$B;0)))

tb4!G2 — ik_evidence_summary:

=IF($B2="";"";INDEX(tb3_evidence_raw!$G:$G;MATCH($B2;tb3_evidence_raw!$B:$B;0)))

Ordem

  1. B2 (fk_id_ev) → arrastar para baixo
  2. C2 (fk_hypothesis_group) → arrastar para baixo
  3. A2 (pk_id_ev_an) → arrastar para baixo
  4. D2:G2 (lookups) → arrastar para baixo
Back to top