SOLVED

Setting retrieved data

%3CLINGO-SUB%20id%3D%22lingo-sub-1451059%22%20slang%3D%22en-US%22%3ESetting%20retrieved%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451059%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20the%20generous%20help%20of%20someone%20on%20this%20forum%20(thanks%20again!)%2C%20I%20was%20able%20to%20retrieve%20some%20data%20that%20had%20been%20messed%20up%20on%20my%20Excel%20sheet.%20We%20used%20a%20VLOOKUP%20function%20to%20sync%20two%20columns%20of%20data%20that%20had%20become%20unsynced.%20The%20reference%20data%20was%20on%20a%20separate%20sheet.%20Now%20that%20my%20information%20is%20straight%20again%2C%20I%20would%20like%20to%20clean%20up%20the%20book%20and%20delete%20the%20helper%20columns%20and%20reference%20sheets%20that%20were%20used%20for%20the%20sorting.%20When%20I%20save%20the%20book%20and%20delete%20the%20unneeded%20sheet%2C%20I%20get%20a%20%23REF%20error%20for%20all%20my%20corrected%20data.%20I%20understand%20why%20this%20is%20happening%20as%20that%20original%20referenced%20data%20is%20now%20deleted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%2C%20%3CSTRONG%3E%3CEM%3Ewithout%20redoing%20the%20VLOOKUP%20function%3C%2FEM%3E%3C%2FSTRONG%3E%2C%20to%20embed%20or%20set%20the%20corrected%20data%20so%20that%20I%20can%20separate%20it%20from%20the%20function%20and%20reference%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1451059%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1451081%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20retrieved%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451081%22%20slang%3D%22en-US%22%3Ecan%20u%20pls%20share%20the%20file%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1451089%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20retrieved%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662966%22%20target%3D%22_blank%22%3E%40bhushan_z%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENope%2C%20sorry%2C%20proprietary%2Fpersonal%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1451102%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20retrieved%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451102%22%20slang%3D%22en-US%22%3Ecan%20you%20remove%20confidential%20data%20and%20share%20just%20some%20sample%3F%3CBR%20%2F%3Eit%20is%20difficult%20to%20provide%20solution%20just%20based%20on%20info%20you%20have%20provided.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1451180%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20retrieved%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451180%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662966%22%20target%3D%22_blank%22%3E%40bhushan_z%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20understand%20the%20need%20to%20visualize%20but%20I'm%20afraid%20all%20the%20information%20is%20personal%20contact%20details.%20I'm%20not%20sure%20I%20could%20anonymize%20it%20and%20still%20retain%20the%20function%20properly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20I%20don't%20think%20this%20is%20the%20kind%20of%20question%20that%20needs%20to%20see%20the%20actual%20data.%20All%20I%20need%20to%20know%20is%20if%20ANY%20kind%20of%20data%20retrieved%20through%20a%20VLOOKUP%20function%20can%20be%20set%20(I'm%20not%20sure%20of%20the%20proper%20term)%20so%20that%20it%20doesn't%20need%20to%20reference%20the%20original%20data%20any%20longer.%20Sorry%2C%20that's%20the%20clearest%20way%20I%20can%20explain%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1451196%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20retrieved%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F686696%22%20target%3D%22_blank%22%3E%40Vonni95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20the%20instructions%20were%20to%20use%20%3CSTRONG%3ECopy%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EPaste%20%3CU%3ESpecial%3C%2FU%3E%3C%2FSTRONG%3E%20....%3CSTRONG%3E%20Values%3C%2FSTRONG%3E%2C%20putting%20the%20correct%20emails%20themselves%2C%20not%20the%20VLOOKUP%20formulas%2C%20into%20the%20column%20where%20they%20belong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1451212%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20retrieved%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451212%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F686696%22%20target%3D%22_blank%22%3E%40Vonni95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20way%20I%20said%20it%20in%20the%20file%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1591715218383.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197574i7E9B530800660575%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22mathetes_0-1591715218383.png%22%20alt%3D%22mathetes_0-1591715218383.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20here's%20where%20you%20find%20it%20in%20the%20Edit%20menu%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1591715258803.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197575i922BE78B0B2E58AB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22mathetes_1-1591715258803.png%22%20alt%3D%22mathetes_1-1591715258803.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20once%20you%20select%20Paste%20Special%20you'll%20see%20this%20next%20choice%3A%20Select%20%22Values%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_2-1591715334929.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197576iB4DDE676A4D8335B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22mathetes_2-1591715334929.png%22%20alt%3D%22mathetes_2-1591715334929.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EJust%20to%20be%20completely%20clear%3A%20you%20first%20select%20the%20VLOOKUP%20cells%20and%20do%20%3CSTRONG%3EEdit...Copy%3C%2FSTRONG%3E%2C%20then%20go%20to%20the%20place%20where%20the%20actual%20values%20need%20to%20be%20pasted%20and%20select%20Paste%20Special%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimple%20Copy%20and%20Paste%20will%20produce%20exactly%20the%20problem%20you're%20experiencing.%20It's%20%3CSTRONG%3ECopy...Paste%20Special%20%3C%2FSTRONG%3Ethat%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1451232%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20retrieved%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451232%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerfect%2C%20thank%20you!%3C%2FP%3E%3CP%3EI%20think%20I%20was%20so%20thrilled%20and%20relieved%20with%20getting%20the%20info%20back%20that%20I%20completely%20overlooked%20that%20step%20in%20the%20instructions.%20LOL%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

With the generous help of someone on this forum (thanks again!), I was able to retrieve some data that had been messed up on my Excel sheet. We used a VLOOKUP function to sync two columns of data that had become unsynced. The reference data was on a separate sheet. Now that my information is straight again, I would like to clean up the book and delete the helper columns and reference sheets that were used for the sorting. When I save the book and delete the unneeded sheet, I get a #REF error for all my corrected data. I understand why this is happening as that original referenced data is now deleted.

 

Is there a way, without redoing the VLOOKUP function, to embed or set the corrected data so that I can separate it from the function and reference data?

7 Replies
Highlighted
can u pls share the file?
Highlighted

@bhushan_z 

 

Nope, sorry, proprietary/personal data.

Highlighted
can you remove confidential data and share just some sample?
it is difficult to provide solution just based on info you have provided.
Highlighted

@bhushan_z 

 

I understand the need to visualize but I'm afraid all the information is personal contact details. I'm not sure I could anonymize it and still retain the function properly.

 

Anyway, I don't think this is the kind of question that needs to see the actual data. All I need to know is if ANY kind of data retrieved through a VLOOKUP function can be set (I'm not sure of the proper term) so that it doesn't need to reference the original data any longer. Sorry, that's the clearest way I can explain it.

 

Highlighted
Best Response confirmed by Vonni95 (Occasional Contributor)
Solution

@Vonni95 

 

I think the instructions were to use Copy and Paste Special .... Values, putting the correct emails themselves, not the VLOOKUP formulas, into the column where they belong.

 

Highlighted

@Vonni95 

 

Here's the way I said it in the file

mathetes_0-1591715218383.png

And here's where you find it in the Edit menu:

mathetes_1-1591715258803.png

 

And once you select Paste Special you'll see this next choice: Select "Values"

mathetes_2-1591715334929.png

Just to be completely clear: you first select the VLOOKUP cells and do Edit...Copy, then go to the place where the actual values need to be pasted and select Paste Special, etc.

 

Simple Copy and Paste will produce exactly the problem you're experiencing. It's Copy...Paste Special that you want.

 

Highlighted

@mathetes 

 

Perfect, thank you!

I think I was so thrilled and relieved with getting the info back that I completely overlooked that step in the instructions. LOL