Current Month Data Pull

https://community.smartsheet.com/discussion/comment/407982#Comment_407982

Are you able to provide some screenshots for context?

Kostya V

Hi Paul,

Sure thing, but I also believe I figure out a simpler formula which works now, but I'm hesitant to say for sure if it will work for next month.

Here's the new Formula on a new sheet where I'm consolidating specific data:

Here's where the raw data drops in via a form that our PMs fill out:

For reference, I am only using the Entry Date data set, not job start or job end dates.

https://community.smartsheet.com/discussion/comment/407994#Comment_407994

That should work assuming you will not have an entry date in the next month.

Kostya V

I ran a test, and put a test line for next month, formula did not capture it, most likely because it's pulling only the current month data, which makes me believe the formula is working correctly. So I would not know if it will work next month until January 1st comes.

Now i'm working to resolve two issues:

Anything that has no data or is at "0" should return N/A. I believe I can fix that fairly easy.
My other issue now is that for some reason under the "training" column, it's not giving me the right info, and I assume it's because there is "N/A". I'm now trying to figure out how to average only the numbers points and exclude the N/A. (this can be seen in screenshots provided above).

Hi!

Similarly, I am trying to capture data in a page summary field using an IF format. My sheet has two columns: Month, formatted as date, and Investment Forecast. I want the summary field to look at the Month column and determine if the date in that month column is the current month, to bring over the Investment Forecast $ amount into the summary field.

Using the AI feature to write a prompt, I received this formula. However, it comes back as 'unparsable' every time.

=IF(MONTH([Month]@row) = MONTH(TODAY()), [DMS Forecast]@row, "")

I tried to use another method. First, made a summary field that calculated the current month's associated number using the 'Month' function" =MONTH(TODAY())

Then set up a summary field with an IF statement: =IF((MONTH(Month1)) = [Today's Month]#, [DMS Forecast]1) - this worked but when I tried to make it an IFs statement and build in the logic for each row, I received another error.

Hi @Lauren Stedman

=IF(MONTH(Month@row) = MONTH(TODAY()), [DMS Forecast]@row, "")

Should work! As long as your column names are Month and DMS Forecast

If it still gives you unparseable, can you show a screen capture of your sheet with the column names showing? But block out sensitive data.

Thanks!

Genevieve

Hi @Genevieve P.

Thanks for the quicky reply. I didn't articulate my issue well. I can get this to work for one IF statement but not for an IFs statement:

I need the summary field to look at all the months and if it's the current month, bring me the DMS forecast associated with that row. When I make it an IFs statement, it fails.

@Lauren Stedman The screenshot helps with the context. Try this:

=INDEX([DMS Forecast]:[DMS Forecast], MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), Month:Month, 0))

Hi @Lauren Stedman

Smartsheet does not have an IFS function with an S - you'll want to use what's called a "nested IF" statement instead, where you list multiple possibilities as individual IF statements in the order you want it to check.

For example:

=IF(MONTH(Month1) = MONTH(TODAY()), [DMS Forecast]1, IF(MONTH(Month2) = MONTH(TODAY()), [DMS Forecast]2, IF(MONTH(Month3) = MONTH(TODAY()), [DMS Forecast]3.... and so on.

However based on how your sheet is set up, I'd actually recommend using an INDEX function instead. You can index the DMS forecast column and return a value from that column based on the row with the current month.

Try:

=INDEX(COLLECT([DMS Forecast]:[DMS Forecast], Month:Month, MONTH(@cell) = MONTH(TODAY())), 1)

If you get an error, this may be due to blank cells in your Month column or cells with text. Try wrapping the MONTH(@cell) portion in an IFERROR:

=INDEX(COLLECT([DMS Forecast]:[DMS Forecast], Month:Month, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())), 1)

See: Lookup one cell using multiple criteria

Cheers,

Genevieve

https://community.smartsheet.com/discussion/comment/422218#Comment_422218

Haha or Paul's suggestion! 🙂

@Genevieve P. There's that COLLECT function again. 🤣

Thanks @Genevieve P. & @Paul Newcome

The Index with no collect reads as #NO MATCH

=INDEX([DMS Forecast]:[DMS Forecast], MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), Month:Month, 0))

The Index WITH Collect returns #Incorrect Argument Set, even with the error wrapper

=INDEX(COLLECT([DMS Forecast]:[DMS Forecast], Month:Month, MONTH(@cell) = MONTH(TODAY()), 1)

=INDEX(COLLECT([DMS Forecast]:[DMS Forecast], Month:Month, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), 1)

However, the stacked IFs seem to do the trick so I'll use that for my immediate need and keep working on the others for a cleaner formula. Thank you!!

Hey @Lauren Stedman

The cell that has today's month (February) listed has the 26th of February, versus the 1st of February. If you change the date in the cell it should find a match 🙂

Just a side note - there's no March row, either, is that intentional?

For the INDEX(COLLECT, my apologies! There's a missing closing parentheses. If you prefer that method, try it again with 3 ))) before the 1

=INDEX(COLLECT([DMS Forecast]:[DMS Forecast], Month:Month, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())), 1)

) one for TODAY

) one for MONTH

) final one for COLLECT