IF and INDEX functions
Hello! I want to call a month cell that's connected to the year we're on (2023) and the past year (2022) but automatically as the years go by. Example, in 2023 I want to know the number that's in the cell that corresponds to that row (january, february...) and also that same information but from the year before (2022). For this, I have created another sheet that brings data from the one in the image below. I think I'll need to have a IF function to add a =YEAR(TODAY()) and a INDEX to pick up the cell, but I'm not sure.
Answers
-
You would need a helper column on the sheet in the screenshot that contains just the year.
Then you would use a formula similar to...
=INDEX({All month columns}, MATCH(YEAR(TODAY()), {Year Helper Column}, 0), MONTH(TODAY()))
-
Thanks @Paul Newcome !!
For the year before I used =INDEX({All month columns}, MATCH(YEAR(TODAY(-365)), {Year Helper Column}, 0), MONTH(TODAY())). It works, but is there maybe a easier or simpler way to make that reference?
-
There is a slim window where that could be incorrect every 4 years (leap years). Instead of pulling the year from 365 days in the past, it would be slightly more accurate to subtract 1 from the current year.
YEAR(TODAY()) - 1
-
@Paul Newcome Thank you!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!