Add Forecast/Budget values in a cell based on a date in another sheet

Julie Nelson

I need to populate a value for Accounting Accounts in a Month/Year column in a "FORECAST SHEET" based on two criteria: 1. Forecast Date in a Report ("DATE REPORT"), and 2. by Account. Below is a snipit of the Forecast Sheet. My dilemma is that the column headers are the Forecast Date (day isn't important), but the rows need to list all Accounts (Production Expenses-Contract Labor, in the example below) and roll up into a Monthly TOTAL. Since I need the $ Amount to rollup, the column headers can't be a "Date" type (so Row 1 can't be a date to compare against the Forecast Date from DATE REPORT). This forecast/budget is for 72 months and many factors go into the DATE REPORT (different equipment types, etc.)

In the example below, if the Date in DATE REPORT is 2/25/22 (remember, day isn't important, just month/year), I want $195 to populate in the Contract Labour column for 01/01/22 and 02/01/22, but 03/01/22 would be zero.

I really appreciate any ideas for this - I have been working on it for months! Thanks so much!

Jeff Reisman

@Julie Nelson

You're going to have to add some clarity to your ask on this one. First you say:

I need to populate a value for Accounting Accounts in a Month/Year column in a "FORECAST SHEET" based on two criteria: 1. Forecast Date in a Report ("DATE REPORT"), and 2. by Account. 

Then you say:

In the example below, if the Date in DATE REPORT is 2/25/22 (remember, day isn't important, just month/year), I want $195 to populate in the Contract Labour column for 01/01/22 and 02/01/22, but 03/01/22 would be zero.

So are you trying to fill in a column called "Month/Year" on the FORECAST SHEET, or fill in a $ value in a sheet called DATE REPORT? Where is the "Contract Labour" column?

And what is the common value between the two sheets?