I'm working on a gantt chart spreadsheet and trying to make the impossible work (it seems so)
Basically I'm trying to achieve is that dates in U and W columns would have a dynamically set range for min or max functions based on G column value (type of row) so everything that is between two PS (Primary section) and between two S (sections) would be defined as a range.
The catch is that row type is set by user, and the number of rows can be from 1 to N-th, so the fixed MIN or MAX formula is not really an option
Getting the start of the range is easy - basic IF(G13="PS",...,...)
The difficult part, where I need help, is getting the last range row that is just before the next S or PS type.
See example ranges below.
Current achieved result: =MAX(IF(tipas="PS",INDIRECT("U" & ROW(id)),IF(tipas="S",INDIRECT("U" & ROW(id)),"")):U17)
Full cell formula:
=IFERROR(IF(AND(tipas<>"",tipas="PS"),MAX(INDIRECT("U" & ROW(id):U17),
IF(AND(tipas<>"",tipas="S"),MAX(INDIRECT("U" & ROW(id):U17),
'formulas to get the date for other types'
IF(plan.pradzia<>"",plano.pabaigos.skaic,IF(plan.pabaiga<>"",plan.pabaiga,IF(eilNr<>"",
IF(darbo.priklausomumas="PrPb",prpb.skaic,IF(darbo.priklausomumas="PbPb",pbpb.skaic,plano.pabaigos.skaic)),""))))),"")
tipas is a nemed range for G column
How could I get the U17 value dynamically based on column G value?
CodePudding user response：
Since i don't know what cells are avaiable, let's assume that the range HA:HF is avaiable. Place these formulas accordingly:
| ADDRESS | OUTCOME DESCRIPTION | FORMULA |
|---|---|---|
| HA13 | Row of any PS cell. | =IF($G13="PS",ROW($G13),"") |
| HB13 | Row of any PS or S cell. | =IF(OR($G13="PS",$G13="S"),ROW($G13),"") |
| HC13 | Address of the range for the PS section in the U column (reported on the proper PS row). | =IF($G13="PS",IF(OR(OFFSET($G13,1,0)="PS",OFFSET($G13,1,0)=""),"","U"&MAX(HA$13:HA13) 1&":U"&IF(MIN(INDIRECT(CELL("address",OFFSET(HA13,1,0))&":"&ADDRESS(ROWS(HA:HA),CELL("col",HA:HA),1,1)))-1=0,MATCH(TRUE,INDEX(INDIRECT(CELL("address",OFFSET($G13,1,0))&":"&ADDRESS(ROWS($G:$G),CELL("col",$G:$G),1,1))="",0),FALSE) ROW($G13),MIN(INDIRECT(CELL("address",OFFSET(HA13,1,0))&":"&ADDRESS(ROWS(HA:HA),CELL("col",HA:HA),1,1)))-1)),"") |
| HD13 | Address of the range for the S section in the U column (reported on the proper S row). | =IF($G13="S",IF(OR(OFFSET($G13,1,0)="PS",OFFSET($G13,1,0)="S",OFFSET($G13,1,0)=""),"","U"&MAX(HB$13:HB13) 1&":U"&IF(MIN(INDIRECT(CELL("address",OFFSET(HB13,1,0))&":"&ADDRESS(ROWS(HB:HB),CELL("col",HB:HB),1,1)))-1=0,MATCH(TRUE,INDEX(INDIRECT(CELL("address",OFFSET($G13,1,0))&":"&ADDRESS(ROWS($G:$G),CELL("col",$G:$G),1,1))="",0),FALSE) ROW($G13),MIN(INDIRECT(CELL("address",OFFSET(HB13,1,0))&":"&ADDRESS(ROWS(HB:HB),CELL("col",HB:HB),1,1)))-1)),"") |
| HE13 | Address of the range for the PS section in the W column (reported on the proper PS row). | =IF($G13="PS",IF(OR(OFFSET($G13,1,0)="PS",OFFSET($G13,1,0)=""),"","W"&MAX(HA$13:HA13) 1&":W"&IF(MIN(INDIRECT(CELL("address",OFFSET(HA13,1,0))&":"&ADDRESS(ROWS(HA:HA),CELL("col",HA:HA),1,1)))-1=0,MATCH(TRUE,INDEX(INDIRECT(CELL("address",OFFSET($G13,1,0))&":"&ADDRESS(ROWS($G:$G),CELL("col",$G:$G),1,1))="",0),FALSE) ROW($G13),MIN(INDIRECT(CELL("address",OFFSET(HA13,1,0))&":"&ADDRESS(ROWS(HA:HA),CELL("col",HA:HA),1,1)))-1)),"") |
| HF13 | Address of the range for the S section in the W column (reported on the proper S row). | =IF($G13="S",IF(OR(OFFSET($G13,1,0)="PS",OFFSET($G13,1,0)="S",OFFSET($G13,1,0)=""),"","W"&MAX(HB$13:HB13) 1&":W"&IF(MIN(INDIRECT(CELL("address",OFFSET(HB13,1,0))&":"&ADDRESS(ROWS(HB:HB),CELL("col",HB:HB),1,1)))-1=0,MATCH(TRUE,INDEX(INDIRECT(CELL("address",OFFSET($G13,1,0))&":"&ADDRESS(ROWS($G:$G),CELL("col",$G:$G),1,1))="",0),FALSE) ROW($G13),MIN(INDIRECT(CELL("address",OFFSET(HB13,1,0))&":"&ADDRESS(ROWS(HB:HB),CELL("col",HB:HB),1,1)))-1)),"") |
Then drag the cells to cover the whole list. You can use an INDIRECT function to integrate the addresses in your formulas. For example:
=MAX(INDIRECT(HC13))
or
=MAX(INDIRECT(IF($G13="PS",IF(OR(OFFSET($G13,1,0)="PS",OFFSET($G13,1,0)=""),"","U"&MAX(HA$13:HA13) 1&":U"&IF(MIN(INDIRECT(CELL("address",OFFSET(HA13,1,0))&":"&ADDRESS(ROWS(HA:HA),CELL("col",HA:HA),1,1)))-1=0,MATCH(TRUE,INDEX(INDIRECT(CELL("address",OFFSET($G13,1,0))&":"&ADDRESS(ROWS($G:$G),CELL("col",$G:$G),1,1))="",0),FALSE) ROW($G13),MIN(INDIRECT(CELL("address",OFFSET(HA13,1,0))&":"&ADDRESS(ROWS(HA:HA),CELL("col",HA:HA),1,1)))-1)),"")))
I've tried a AGGREGATE based solution, but it appeared to be slower and possibly needed more columns. This solution should be lighter, but in case of a long list it might still be too slow. In such case, a VBA solution might be better with a user defined function. Then again, the questions' tag were excel and excel-formula. I might try a user defined function if needed.