How to use cell input as column name in formula?

The idea I referenced earlier should work for you. Create a column called blank (this column should always be immediately after all the contact for MM_DD columns).

Then you create a column called current month (or whatever you would like) and the formula would be similar to the below (put pne of the previous months column names in the bolded info below).

=IFERROR(INDEX([contact for MM_DD]@row:BLANK@row, 1, COUNTIF([contact for MM_DD]@row:BLANK@row, OR(@cell = "", @cell <> "")) - 1), "")

This should always pull the last month column info into this column and you can always reference your count formulas to this column.

You can change the formula slightly for previous weeks by changing the bolded number below from -1.

-2 should get you the previous week

-3 should get you 2 weeks prior, etc.

=IFERROR(INDEX([contact for MM_DD]@row:BLANK@row, 1, COUNTIF([contact for MM_DD]@row:BLANK@row, OR(@cell = "", @cell <> "")) - 1), "")

All comments

https://community.smartsheet.com/discussion/comment/275029#Comment_275029

I don't know of a way to dynamically reference a range in Smartsheet.

not sure if this is the correct option for you, but a solution I gave for someone else may apply here as well:

Thank you for your suggestion! Unfortunately, we can't put the column at the end of the sheet. Due to some other requirements, it gets added in the middle. I couldn't figure out a way to do it in Excel either, but perhaps it's possible.

It does not need to be added at the end of the sheet... If you send a snapshot and/or add more info of your setup I can try and delve deeper.

I would also point out that it might be better if you were tracking this on a second sheet

I really appreciate your assistance!

We manage a roster of volunteers who are needed once a week. Each week, we create a new checkbox column titled "Contact for MM_DD", and update the month/day, then manually check certain people to contact. We clear the data from a column called "Can you volunteer this week?" and they are sent an automated Smartsheet form where they say yes or no.

The sheet will have their name, email, phone, then a column for each week as mentioned above, then the "can you volunteer" column, and then multiple more columns with various personal information.

So I'd like to create a new sheet that provides a count of how many volunteers have said "Yes" so we can see if our needs are met for the week. Worst case, I could update the formula each week to count the newly created weekly column, but it would be nice to simply input a date into a cell so we can do similar things like multiple different COUNTIFS, if that makes sense.

A similar additional aspect is I have created a report that brings in just the name, phone number, "contact for MM_DD", and "can you volunteer", with a filter to show only blanks, so we know who to call if they have not answered yet. Each week I can manually remove the weekly column, add the new one, and reset the filter for the new column. However, we'd like to pass the work on to another charitable organization without them needing even these simple skills. I don't expect to find a way to have the report automatically replace the weekly column and filter, but it would help immensely to find a similar way to approach it, since we would make many other reports that similarly use the weekly column added each week.

I'm very sorry for the length of this!

The idea I referenced earlier should work for you. Create a column called blank (this column should always be immediately after all the contact for MM_DD columns).

Then you create a column called current month (or whatever you would like) and the formula would be similar to the below (put pne of the previous months column names in the bolded info below).

=IFERROR(INDEX([contact for MM_DD]@row:BLANK@row, 1, COUNTIF([contact for MM_DD]@row:BLANK@row, OR(@cell = "", @cell <> "")) - 1), "")

This should always pull the last month column info into this column and you can always reference your count formulas to this column.

WOW! Thank you SO much!! Being able to always reference the "current" column will allow for so many useful reports that continually update themselves with the current week's information.

I must admit, I do not yet understand how the formula works.

Is it possible to modify it to also create a "previous week" column? I would order it like "contact for MM_DD", then multiple other columns for subsequent weeks, then the blank column, then the "previous week" column, then the "current week" column.

This is less crucial, but would enable us to see who is newly scheduled for this week, and was not for the previous week.

I appreciate what you've already helped with so much, and it is more than enough already.

You can change the formula slightly for previous weeks by changing the bolded number below from -1.

-2 should get you the previous week

-3 should get you 2 weeks prior, etc.

=IFERROR(INDEX([contact for MM_DD]@row:BLANK@row, 1, COUNTIF([contact for MM_DD]@row:BLANK@row, OR(@cell = "", @cell <> "")) - 1), "")

Awesome! You're the best. I figured that was the case, but it wasn't working at first. Then I realized I had the Blank column named Blank - do not touch. I included square brackets and it now works. I will likely lock and hide these columns.

This will significantly increase our efficiency and I can't thank you enough!

I hate to ask you for extra assistance, but would you mind explaining the formula's logic?