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 |