Is there a way to have a column automatically pull data from multiple columns based on the month/yea

I have a master sheet that includes data for multiple projects from different months. I'm trying to generate a column that pulls the data from the current month. The intent is to create a cell that populates automatically and can be referenced on a dashboard.

I have tried to use the index/match formula with no success. I tried to use row 1 for the "searchable" content. My initial thought was that I could search for the column that includes today's month and year.

I tried combining =LEFT command with MONTH(TODAY()) to return the left 2 numbers from row1. I also figured that I need to use =RIGHT command with YEAR(TODAY()) to return the year from row1.

There might be a much simpler way to accomplish this.

Please help!

Annotation 2019-11-04 163407.jpg

Find more posts tagged with

Formulas

Project Management

Comments

Genevieve P.

Hi Brad,

In order to perform calculations with dates you need to have Date column type set up in your sheet. I would suggest switching the placement of your information to have your months all in one column, with Project 1 and Project 2 two other columns.

Once you have your dates set up in a date column type, then you can run formulas to find the Month. For what you are looking to do, to see the data for both this current month and year, you will want to set up a helper column first. This helper column will return the Month number, but only IF it the month is in this current year. Otherwise, it will return a blank cell. I have attached an example of how you may want to organize your sheet below.

You will notice that in my second example image I have used this formula:

=IF(YEAR([Month/Year]@row) = YEAR(TODAY()), MONTH([Month/Year]@row), "")

This states that if the Year in my Month/Year column is the same as Today's year, it will return the number of the Month. Otherwise it will return "", which is a blank cell.

Once you have a helper column set up, then you can use an INDEX(MATCH formula to look for the current data based off the Helper column. INDEX(MATCH formulas can only look for one criteria, which is why you would need a Helper column to first eliminate any months that are not in this current year.

Please see my final image for an example of this, below.

Example Organization.png

Example Helper Formula.png

Example Index Match.png

Ashley F

Is there any way to do this without a helper column? I have 6 date columns in our sheet where I have to find weekly and monthly averages off of. This means for each of the 6 date columns, I need a week helper and a month helper column which adds 12 columns of formulas to our sheet and since our sheet is 7000 rows long, these 12 columns are taking up 84k cells and since they are formulas, they really slow the sheet down. Can you do something like

=COUNTIF(MONTH([Unload Date]:[Unload Date], Month(Today())

Genevieve P.

Hi @Ashley F

Could you explain your scenario a little further? I'm wondering if the easiest thing to do is to create a Row Report, filter by your month or date range, then use the Grouping/Summary features to get your values.

Otherwise, yes, you can use a COUNTIF to count how many rows are within a certain month, like so:

=COUNTIF([Unload Date]:[Unload Date], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?