Sheet Summary - same sheet VLOOKUP with TODAY()
Hi,
My Column Headers are:
Date, Total Opened Cases, Total Closed Cases, Total Backlog.
I am attempting, and failing, to use the following variations of a VLOOKUP formula. Can someone help??
=VLOOKUP(TODAY(), {Date}:{Total Backlog}, 4, false) = #UNPARSEABLE
=VLOOKUP(TODAY(), {Date:Total Backlog}, 4, false) = #INVALID REF
=VLOOKUP(TODAY(), Date:Total Backlog, 4, false) = #UNPARSEABLE
Best Answer
-
Nick Korna ✭✭✭✭✭✭
Then you don't need to use the cross sheet reference (in the { } brackets), you would just select the columns. If they are sequential (like in the example) then your formula would be:
=VLOOKUP(TODAY(), Date:[Total Backlog], 4, false)
If they are more spaced out then this would need altering, but from your example I am guessing they are grouped together.
Answers
-
Nick Korna ✭✭✭✭✭✭
The middle one looks to be correct formula wise, but have you set the reference correctly when putting the formula in? If you're trying to create the cross sheet reference without doing this it won't work.
If you're not sure how, this should show you:
Click on this when entering the formula. On the screen which opens, find your sheet to reference in the list on the left (you can search by its name to find it) and highlight the columns required and rename the Sheet reference name (as it will default to the sheet name plus range and a number):
Hit the OK button in the bottom right of this screen and finish the VLOOKUP and everything should work:
Hope this helps, but if it's something else then please let us know so we can try and get it fixed.
-
Thank you for your answer@Nick Korna.
However, the 'Reference Another Sheet' will not allow me to select the sheet I am currently in. It only allows me to select an different sheet?
-
Nick Korna ✭✭✭✭✭✭
Then you don't need to use the cross sheet reference (in the { } brackets), you would just select the columns. If they are sequential (like in the example) then your formula would be:
=VLOOKUP(TODAY(), Date:[Total Backlog], 4, false)
If they are more spaced out then this would need altering, but from your example I am guessing they are grouped together.
-
Thanks this worked. But I don't understand why. Why doesn't Date have [] but Total Backlog does have them?
-
Nick Korna ✭✭✭✭✭✭
The square brackets are for if your column header has a space in. So "Date" wouldn't need them, but "Total Backlog" does. If "Date" was "Start Date" or "End Date", then this would also need the [ ] around it for the reference.
-
Thanks much appreciated.️
-
Nick Korna ✭✭✭✭✭✭
No problem, happy to have helped out!
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/107222/\")<\/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":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":44,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+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-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
