Unable to convert a vlookup formula into values
I have multiple columns with column formulas built that do a lookup into another Smartsheet. Want to convert the lookup values into values in the workbook that I built the formulas in, and then delete the workbook that is currently being referenced to find lookup.
I cannot figure out how to do it.
I do not see an option to copy and paste values. In Paste Special it only offers paste format, not paste values
Does anyone know a solution?
Thanks
Best Answer
-
Rod H ✭
A Big Thank You! to both Paul and Itai! Yes you have the answer. Deeply appreciated.
Rod
Answers
-
Paul Newcome ✭✭✭✭✭✭
Are you able to provide a screenshot of the poste special menu? I have always had "Paste Values" as an option.
-
Rod H ✭
Paul Thanks for checking in, is that what you requested?
-
Paul Newcome ✭✭✭✭✭✭
That's exactly what I was asking for but not what I expected to see. Are you pasting into the same sheet that you are copying from?
-
Rod H ✭
yes
-
Paul Newcome ✭✭✭✭✭✭
Are you highlighting multiple cells before trying to paste? Are all cells that you are copying adjacent?
-
Rod H ✭
yes, copying and pasting in the same column/cell
-
Paul Newcome ✭✭✭✭✭✭
Can you provide a series of screenshots that shows the cells along with each step of what you are doing?
-
Paul Newcome ✭✭✭✭✭✭
-
Rod H ✭
I do not have any filters applied.
The first screen shot is a small overview of the sheet.
List of products (not showing) on far left and at the top is headers of categories of different attributes of products.
in the screen shot is the formula, the reference "Meat Hierarchy" is the name and range in the "other" sheet being referred to.
-
Rod H ✭
I highlighted all the data in the column and click copy
Then I click Paste Special and get the following
-
Itai ✭✭✭✭✭
你不能没有te values into a coulmn formula column. you can convert the column formula to a cell formula and then paste it as values but it will not be a column formula column anymore.
Do you have access to DataMesh? it could help and make it easier.
Itai Perez
Project Manager | Transformation Department
Gong cha
-
Paul Newcome ✭✭✭✭✭✭
You will need to covert to cell formula before pasting.
-
Rod H ✭
A Big Thank You! to both Paul and Itai! Yes you have the answer. Deeply appreciated.
Rod
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
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), \"//www.santa-greenland.com/community/discussion/107579/\") + \"% 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":47,"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":"