How to use cell input as column name in formula?

5684532

Hello,

Is it possible to use a cell value to fill in the column name within a formula?

We add a new column each week. I would like to do a simple COUNT function, for example. Instead of updating the formula each week, I'd like to update the value in a cell that the formula will reference.

So instead of COUNT([ColumnName]:[ColumnName]), I'd like to create a new column called ColumnNEWNAME, then have the COUNT formula reference another cell where I would input ColumnNEWNAME, and it would use this in the formula to count the newly created column with the new name.

Does that make sense and is it possible? Thank you.

Leibel S

@SD313

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.

Leibel S

@SD313

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), "")

Leibel S

@SD313

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:

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

5684532

@Leibel S

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.

Leibel S

@SD313

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

5684532

@Leibel S

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!

Leibel S

@SD313

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.

5684532

@Leibel S

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.

Leibel S

@SD313

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), "")

5684532

@Leibel S

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!

5684532

@Leibel S

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

5684532

@Leibel S

Re: my last comment, here's where I'm at with it, but could you let me know if anything's wrong and help me understand it in plain English?

IFERROR returns INDEX, otherwise returns blank.

INDEX returns value outlined below, minus 1 (for current week), or 2 (for previous week)

Ranging from first week to blank column (to capture newly added columns)

Within the single row of the range (row 1) 

At the column position determined by COUNTIF

Ranging the same as mentioned above

Whether it is blank or not.