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

Brad Bonner

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!

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. 

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