Setting retrieved data

[Vonni95]

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?

[#2]

can u pls share the file?

[#3]

@bhushan_z 

Nope, sorry, proprietary/personal data.

[#4]

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

[#5]

@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.

[#6 · Accepted 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.

[#7]

@Vonni95 

Here's the way I said it in the file

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

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

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.

[#8]

@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