Formula to return a value based on the current month in the SAME sheet

Options
✭✭

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?

Tags:

• ✭✭✭✭✭✭
Options

Where exactly are you planning to put that formula?

• ✭✭
Options

It would be in row 9, in a column labeled "YTD". Just a random location really.

• ✭✭✭✭✭✭
Options

Would the 5% always be in the same row across all of the individual month columns?

• ✭✭
Options

Yes, it will

• ✭✭✭✭✭✭
Options

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?

• ✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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)?

• ✭✭
Options

Just for 2024

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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.

• ✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!