I have a cell range that I wish to copy over to another excel file. The forumla in the cell range is as follows: =LEFT(C3,1). This is the same in all cells that I am copying.

I used paste special (values) to paste it into my new excel document and I have further cells that reference this data elsewhere in the new file. The problem is that not all of my original cell range is populated (it will be shortly but not yet). Because of this, the new excel file is not seeing the cells that weren't populated as blank (because it still has the formula above in ready for when the data is ready). Therefore, my other references are getting messed up because it's not seeing these cells as blank (it looks as if there is a single space in the cell).

If I clear contents in each cell, my other references will work. This is quite time consuming however. Is there a way that I can select the whole cell range from my initial file and when I paste it, get it to ignore any cells that aren't populated yet?

I hope you get that as it's sounding really complicated explaining it although it is rather simple.