TheMacroNoob
Board Regular
- Joined
- Aug 5, 2022
- Messages
- 52
- Office Version
- 365
- Platform
- Windows
Hello Excel Experts,
I have a dynamic spill array that pumps out values matching various criteria.
In my example below, my spilled array returns four values (IDs), and in the next 4 columns I grab additional information with XLOOKUPS based on those values.
Is there any way to sort my spill array by the proceeds changes column? As it stands, that is a circular reference and SORTBY doesn't work by referencing those cells, so I'm not sure how to achieve the desired result.
I have a dynamic spill array that pumps out values matching various criteria.
In my example below, my spilled array returns four values (IDs), and in the next 4 columns I grab additional information with XLOOKUPS based on those values.
Is there any way to sort my spill array by the proceeds changes column? As it stands, that is a circular reference and SORTBY doesn't work by referencing those cells, so I'm not sure how to achieve the desired result.
Sale Forecast Comparison.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
20 | ID | Property Name | Change | Status | Proceeds Changes | |||
21 | 12/29/2020 | 522 | Downtowner | Moved to 2021 from 2020 | PA Signed | (2,465,580) | ||
22 | 503 | Reserve at Fox River | Moved to 2021 from 2020 | PA Signed | (13,437,174) | |||
23 | 521 | Sahuarita Mission | Moved to 2021 from 2020 | PA Signed | (1,892,910) | |||
24 | 648 | Sycamore Rdge | Property Added to 2020 | Resy Completed | (821,904) | |||
25 | ||||||||
Forecast Timelines Comparison |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B21:B24 | B21 | =FILTER(IFS(Year_Changes[Changes D1-D2]="Property Added to 2020",Year_Changes[PID],Year_Changes[Changes D1-D2]="Moved to 2021 from 2020",Year_Changes[PID],TRUE,""),IFS(Year_Changes[Changes D1-D2]="Property Added to 2020",Year_Changes[PID],Year_Changes[Changes D1-D2]="Moved to 2021 from 2020",Year_Changes[PID],TRUE,"")<>"","") |
C21:C25 | C21 | =XLOOKUP(B21,Data[PID],Data[Property],"") |
D21:D25 | D21 | =XLOOKUP(B21,Year_Changes[PID],Year_Changes[Changes D1-D2],"") |
E21:E24 | E21 | =XLOOKUP(B21,Status[PID],Status[D2]) |
F21:F25 | F21 | =IFNA(IFS(D21="Moved to 2021 from 2020",-XLOOKUP(B21,Data[PID],Data[P1]),D21="Property Added to 2020",XLOOKUP(B21,Data[PID],Data[P2])),"") |
Dynamic array formulas. |