Formula to return a value based on the current month in the SAME sheet
Hi,
I'm having trouble getting this formula, I've tried a lot of options and am probably over-thinking it.
In the image above, what I want to return is the 5% value. I have a helper row at the top under "12/2024", 5% relates to a value for the month of December. There would be columns in this sheet through 12/2024 and the columns are NOT date columns and cannot be set as date columns. So, what formula would return "5%" if the current month is December?
Thanks in advance!!!
Answers
-
Where exactly are you planning to put that formula?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It would be in row 9, in a column labeled "YTD". Just a random location really.
-
Would the 5% always be in the same row across all of the individual month columns?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes, it will
-
Ok. I think I have some ideas, but can you post a screenshot of more data in the month columns so I can be sure I understand your architecture?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Top image is top half, bottom image is bottom half. The formula will go in the highlighted cell in the top image. The value I would want to pull for Jan would be in the highlighted image in the bottom half. I've tried helper rows 1-12 and Jan-Dec, helper date columns with 1-12 and Jan-Dec. I'm having no luck!
Appreciate the assistance Paul!
-
Is this going to have columns that cover multiple years, or is it just going to be 12 columns (one for each month in 2024)?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Just for 2024
-
In that case try this:
=INDEX([01/2024]27:[12/2024]27, MONTH(TODAY()))
The above will show December for now, but as soon as Jan 1 2024 hits, it will start pulling from the current month's column.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It returned an invalid value error. Keep in mind, the column types are Text/Number. Also, the value I want returned, in the example 5%, a formula determines that value.
-
Figured it out with this one and another post of yours, Paul:
What works is:
=INDEX([01/2024]27:[12/2024]27, 1, MATCH(MONTH(TODAY()), [01/2024]1:[12/2024]1, 0))
Where the range represents the portion of a row I want to pull the value from. Where the row_index is 1 since I want to reference 1 row. Where I'm matching today's month, with its representative # (December = "12") listed in row #1 of columns 01/2024 to 12/2024.
Thanks!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!