Is there a way to have a column automatically pull data from multiple columns based on the month/yea
我有一个主单,包括数据多ple 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!
Comments
-
Genevieve P. Employee AdminHi Brad,
In order to performcalculations with datesyou need to haveDate column typeset 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, butonlyIF 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 anINDEX(MATCHformula 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. Employee Admin
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:
=条件统计([卸货日期]:[卸货日期],IFERROR(蒙特H(@cell), 0) = MONTH(TODAY()))
Cheers,
Genevieve
Help Article Resources
Categories
Check out theFormula Handbook template!
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//m.santa-greenland.com/community/discussion/61966/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":54,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
