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

Options

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!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/31/22
    Options

    @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?

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!