Hello Everyone,
Is it possible to return the data within the 'U:AC' range without needed to place individual formulas in column 'U' with a manually entered range (AG:DY)?
Thanks
Is it possible to return the data within the 'U:AC' range without needed to place individual formulas in column 'U' with a manually entered range (AG:DY)?
Thanks
| Business_Model_Highland_Trails_Master_March.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | |||
| 5 | 0 | 1 | ||||||||||||||
| 6 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 11/30/20 | 12/31/20 | |||||
| 7 | $0 | $0 | $7,300,000 | $0 | $21,582,175 | $12,899,302 | $7,871,173 | $2,360,206 | $0 | $0 | $0 | |||||
| 8 | 2020 | 2020 | ||||||||||||||
| 9 | $0 | $0 | $0 | $0 | $1,335,825 | $0 | $0 | $0 | $0 | 0 | 0 | |||||
| 10 | $0 | $0 | $0 | $0 | $1,943,667 | $555,333 | $0 | $0 | $0 | 0 | 0 | |||||
| 11 | $0 | $0 | $0 | $0 | $6,206,483 | $0 | $0 | $0 | $0 | 0 | 0 | |||||
| 12 | $0 | $0 | $0 | $0 | $75,000 | $150,000 | $0 | $0 | $0 | 0 | 0 | |||||
| 13 | $0 | $0 | $0 | $0 | $73,311 | $146,623 | $0 | $0 | $0 | 0 | 0 | |||||
D_Cash_Flow_Table_New | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| U6:AC6 | U6 | =TRANSPOSE(UNIQUE(Setup!$C$87#)) |
| U7:AC7 | U7 | =SUMIFS(AG7#,AG8#,U6#) |
| AG5:DY5 | AG5 | =SEQUENCE(1,sModelTerm+1,0,1) |
| AG6:DY6 | AG6 | =DATE(YEAR(sStartDate),MONTH(sStartDate)+AG5#,0) |
| AG7:DY7 | AG7 | =BYCOL(AG9#,LAMBDA(_col,SUM(_col))) |
| AG8:DY8 | AG8 | =YEAR(AG6#) |
| AG9:DY197 | AG9 | =IFERROR((O9#="S-Curve")*GK9#+((O9#<>"S-Curve")*($AG$5#>=L9#)*($AG$5#<=M9#))*(P9#/N9#),0) |
| U9:AC13 | U9 | =SUMIFS(AG9:DY9,$AG$8#,$U$6#) |
| Dynamic array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| sModelTerm | =Setup!$F$10 | AG5 |
| sStartDate | =Setup!$F$8 | AG6 |
