#VALUE! When I paste values

Question

So I have a pretty complicated formula that took me quite some time to figure out. Everything is finally working until I reached the final bit where I copy and paste values over to the actual sheet. Often times the formula doesn't have a number to do its equation on, which is fine and to be expected. But for some reason when I copy and paste values anything that had a formula but no numbers causes a #VALUE! error which in turns breaks the whole formula. Whats weird is when I click into the cell and click away without making any changes the problem corrects itself. Simplified Example:

My formula starts with a percentage (A1), on B1 I put in a percentage I think will work, C1 takes from B1 and divides with D1( a set number depending on the size of what we're working on.) so C1:C10 all have this equation. Now I copy and paste values from C1:C10 on top of A1:A10 but if A5 doesn't have data it comes up with the #VALUE! error.

I hope this was easy to follow. 

I built my formula as a cheat sheet to make things easier on my coworkers I'd rather not have to explain to them to just delete whats not actually there. I'm hoping there is a way around this error that I can't find on my own. I've tried searching this problem but it just comes up with pretty basic copy and pasting tricks. Any help?

Answer 1

Sorry for the confusion I worked it out myself. When the initial box is blank it comes up with the error so I added a simple =IF(A1="","0",equation) so it see's a 0 instead of nothing. 

Sorry the post was hard to follow it was confusing me a bit just writing it.

Answer 2

Hello,

that link is OneDrive For Business and will open the file in Excel Online in the browser in read only mode without the option to download it. I cannot recreate the problem if I cannot edit the file in Excel for desktop.

Please use your personal OneDrive at https://onedrive.live.com/ instead and allow the file to be downloaded. Also, please explain which cells you copy and where you paste them to.

Answer 3

Please put sample file on OneDrive not SharePoint

best wishes

Answer 4

Here is the sample. https://spatzlabs-my.sharepoint.com/personal/jsantos\_spatzlabs\_com/\_layouts/15/guestaccess.aspx?docid=11fe11a1d3f1b4017aa6d8560fc0861b0&authkey=AbhBB02gM4dLr2cuFT3Y7Cc

Answer 5

Hello,

I'm afraid that was not easy to follow. Can you share a sample file? You can upload a sample file to the Public folder of your OneDrive, share it and post a link here. You can access your OneDrive with the same account as this forum.