Autopopulate using dates [Closed]

Report
Posts
1
Registration date
Saturday September 30, 2017
Status
Member
Last seen
September 30, 2017
-
Posts
2720
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 19, 2021
-
Kind of a complicated one. I'll be appreciative/extremely impressed if someone can help me out.

I'm budgeting and trying to autopopulate cells using a date range as a reference point. For example, in the photo, I want to fill row 7 with a certain $ amount ONLY if it's in a certain date range. For row 7, I wouldn't want to fill the $ amount in D7, but I would want it filled in E7 because that's when the due date falls (via B7).



Any help would be appreciated!

1 reply

Posts
2720
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 19, 2021
459
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!