Report

Autopopulate using dates

Ask a question chrisbrownn28 1Posts Saturday September 30, 2017Registration date September 30, 2017 Last seen - Last answered on Oct 10, 2017 at 12:04 PM by TrowaD
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!
Helpful
+0
plus moins
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
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!