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?
-
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?
-
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?
-
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)?
-
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.
-
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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!