Return a value from the previous month in a reference sheet

Esther Wu

Hello,

I'm trying to bring in the average score from the previous month in a reference sheet, it's returning an error.

Here's the formula in text:

=IFERROR(INDEX(COLLECT({Archived ATL Audit Scores - Combined Average}, {Archived ATL Audit Scores - Combined Area}, Area@row, {Archived ATL Audit Scores - Combined Concourse}, Concourse@row, {Archived ATL Audit Scores - Combined Type of Issue}, [Type of Issue]@row, {Archived ATL Audit Scores - Combined Created}, MONTH(TODAY()) - 1), 1), "-")

Reference sheet - Archived ATL Audit Scores - Combined

I couldn't figure out what I'm doing wrong. Any help would be greatly appreciated!

Thank you in advance.

KPH

Hi @Esther Wu

This part concerns me for a couple of reasons:

{Archived ATL Audit Scores - Combined Created}, MONTH(TODAY()) - 1)

Possible problem 1

The month of today could be January which is month 1. Subtracting 1 from 1 is 0, not 12. So there will not be a match in January.

Possible problem 2

The column you are referencing in {Archived ATL Audit Scores - Combined Created} could be the Created column on the left of your second screenshot. If so, that column has a whole date (and time) in it.

However,

MONTH(TODAY()-1))

will be a number between 0 and 11. It will not match with a date.

My gut instinct is that it is problem 2 causing the issue you are experiencing and problem 1 is also in there and will occur later, so here is an idea for a workaround to solve both.

Suggestion to solve problem 2

Add a column to the Archived ATL Audit Scores sheet called "next month" (and cross reference it as {Archived ATL Audit Scores - Next Month}). Put this formula in it:

=MONTH(Created@row) + 1

This gives you the month from the Created date plus 1.

Then in your COLLECT you can use this new column to see if the next month column is the current month by replacing:

{Archived ATL Audit Scores - Combined Created}, MONTH(TODAY()) - 1)

with

{Archived ATL Audit Scores - Next Month}, MONTH(TODAY())

Suggestion to solve problem 1

The above will work for all months except when the current month is January as in January you need 12+1 to equal 1. So we can pop in an IF for this situation. Amend the formula in "next month" to:

=IF(MONTH(Created@row) = 12, 1, MONTH(Created@row) + 1)

This basically says that if the Month in the Created cell is December, then the next month is January. If not, add 1 to the current month date.

Disclaimers

You may have a problem with the years element. I'm not sure how you are handling that in your data but right now ANY February date will be one month before ANY March date so March 2024 will bring in Feb 2024 and Feb 2023 data if you have it.

My guess about your data could be completely wrong and it could be neither of these issues. If so, please share a screenshot of the original sheet, identifying which column is which in your cross-references. Also, let us know what error is being returned.

KPH

Wonderful, glad that helped, and you did exactly what I would have suggested for the year*!

* You did add an IF for December again though, didn't you? Because the next month after December will be YEAR(TODAY())+1

KPH

Hi @Esther Wu

This part concerns me for a couple of reasons:

{Archived ATL Audit Scores - Combined Created}, MONTH(TODAY()) - 1)

Possible problem 1

The month of today could be January which is month 1. Subtracting 1 from 1 is 0, not 12. So there will not be a match in January.

Possible problem 2

The column you are referencing in {Archived ATL Audit Scores - Combined Created} could be the Created column on the left of your second screenshot. If so, that column has a whole date (and time) in it.

However,

MONTH(TODAY()-1))

will be a number between 0 and 11. It will not match with a date.

My gut instinct is that it is problem 2 causing the issue you are experiencing and problem 1 is also in there and will occur later, so here is an idea for a workaround to solve both.

Suggestion to solve problem 2

Add a column to the Archived ATL Audit Scores sheet called "next month" (and cross reference it as {Archived ATL Audit Scores - Next Month}). Put this formula in it:

=MONTH(Created@row) + 1

This gives you the month from the Created date plus 1.

Then in your COLLECT you can use this new column to see if the next month column is the current month by replacing:

{Archived ATL Audit Scores - Combined Created}, MONTH(TODAY()) - 1)

with

{Archived ATL Audit Scores - Next Month}, MONTH(TODAY())

Suggestion to solve problem 1

The above will work for all months except when the current month is January as in January you need 12+1 to equal 1. So we can pop in an IF for this situation. Amend the formula in "next month" to:

=IF(MONTH(Created@row) = 12, 1, MONTH(Created@row) + 1)

This basically says that if the Month in the Created cell is December, then the next month is January. If not, add 1 to the current month date.

Disclaimers

You may have a problem with the years element. I'm not sure how you are handling that in your data but right now ANY February date will be one month before ANY March date so March 2024 will bring in Feb 2024 and Feb 2023 data if you have it.

My guess about your data could be completely wrong and it could be neither of these issues. If so, please share a screenshot of the original sheet, identifying which column is which in your cross-references. Also, let us know what error is being returned.

Esther Wu

@KPH To your concern with the year, I've added another column in the archive sheet that pulls out the year, and added YEAR(TODAY))) to the formula. Thank you so much for your help! It worked perfectly!!!

KPH

Wonderful, glad that helped, and you did exactly what I would have suggested for the year*!

* You did add an IF for December again though, didn't you? Because the next month after December will be YEAR(TODAY())+1

Esther Wu

@KPH Ah, thank you for the reminder! That's such huge help!