Autopopulate using dates
Closed
chrisbrownn28
Posts
1
Registration date
Saturday September 30, 2017
Status
Member
Last seen
September 30, 2017
-
Updated on Oct 9, 2017 at 05:59 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 10, 2017 at 12:04 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 10, 2017 at 12:04 PM
Related:
- Autopopulate using dates
- Gamescom 2023 dates - Guide
- Defcon 2023 dates - Guide
- Nintendo direct 2023 dates - Guide
- Steam sale dates - Guide
- How would you change these dates to the dd/mm/yyyy format? - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Oct 10, 2017 at 12:04 PM
Oct 10, 2017 at 12:04 PM
Hi Chris,
Consider this workaround:
- Change column B to actual dates.
- Then reserve an extra column to determine which date column to use (in this example I used column I).
Cell I7: =MAX(($D$1:$H$1<=B7)*$D$1:$H$1)
This is an array formula and needs to be confirmed with Ctrl+Shift+Enter. Drag formula down.
- In cell D7: =IF($I7=D$1,Your amount,"")
Drag formula to the right and then down.
Your amount could be (for example) a cell reference or a sum function.
Consider hiding or, changing text color to white, on the extra formula column.
Best regards,
Trowa
Consider this workaround:
- Change column B to actual dates.
- Then reserve an extra column to determine which date column to use (in this example I used column I).
Cell I7: =MAX(($D$1:$H$1<=B7)*$D$1:$H$1)
This is an array formula and needs to be confirmed with Ctrl+Shift+Enter. Drag formula down.
- In cell D7: =IF($I7=D$1,Your amount,"")
Drag formula to the right and then down.
Your amount could be (for example) a cell reference or a sum function.
Consider hiding or, changing text color to white, on the extra formula column.
Best regards,
Trowa
