copy and paste special and delimiters

[moneypennie21]

HI, I have a bit of a weird one! I have a spreadsheet with a lot of formulas in which is constantly updated based on the data saved in other spreadsheets. each week I have to save a static version of this report to create a snapshot view of that week/Month/YTD moment in time. I do this by copying the cells I want and then copy paste special (values and number formats) into a template which I then save and name for the relevant week. I then use the snapshot views to create a monthly report at the end of each month by creating a new spreadsheet that has sumproduct formulas in it which point to the relevant week's snapshot report. (sounds odd but it does make sense)!

My problem is this. in the monthly report that I create I have been getting VALUE errors in all of the cells that have the sumproduct formulas in them unless I go back into the snapshot report and change the delimiters on the cells containing the data to 'space'. I have no idea why this might be. can anyone shed any light on this and how to fix it otherwise I have to manually go into the snapshot report and do the delimiter thing with every column I am referencing in the monthly report.

[Larbec]

Can you upload a copy of the spreadsheet? Sounds to me like you may be a space off on your formulas and have a REF# error in a cell or 2. An easy way to find them is go to your left top corner of your spreadsheet and click there. That will high light the entire sheet. Then change the color of the sheet to all black background. Then change your font to white or I use like green lol. The REF# error will stick out like a sore thumb

Either fix the error now or write down where it is and just hit the undo arrow back to your original sheet. If you have a lot of rows go and high light all your columns . This time run your mouse across the top of all your columns and high light them ...... now , click on your data tab and then your filter tab. That should put little arrows in every column On each column click on an arrow. This will tell you everything that is in each row in order (all your 1s , 2s, words together ) again you will see the word REF# which is an reference error

Good luck

[moneypennie21]

Hi I have checked all of the reports and I cannot see any issues with the formulas no #REF anywhere. The monthly report is pointing to a static snapshot which does not contain formulas only numbers which have been copy paste special from the original report.There is no #REF on the snapshot (numbers report) The formula I am using to look up in the monthly report is:

=SUMPRODUCT(--($C2='S:\Marketing\2015\BRANCH DEVELOPMENT\Reports 2015\DLS Branch Referral (LG)\DLS Referral Report By Week\[DLS Branch Referral Report 2015 Week 37.xlsx]Branch'!$N$5:$N$5987)*('S:\Marketing\2015\BRANCH DEVELOPMENT\Reports 2015\DLS Branch Referral (LG)\DLS Referral Report By Week\[DLS Branch Referral Report 2015 Week 37.xlsx]Branch'!$S$5:$S$5987))

This is currently resulting in #VALUE error unless I go to the 'DLS Branch Referral Report 2015 Week 37' report and change the delimiters on column S to 'space'. The 'DLS Branch Referral Report 2015 Week 37' report does not have formulas in it only the data.

sorry if this is confusing but I just don't understand why this is happening or how to fix it!

[Larbec]

Not at all confusing just need to find (-:


It generally means you've tried to carry out a mathematical operation on text. For example

A1= a text value

the formula =A1*1 returns a value error.

It isn't always as obvious as that because sometimes cell contents that look like numbers can actually be text and give this error.

[moneypennie21]

I have tried copy and paste the columns as format 'general' to remove any text as this did occur to me as well but it still does not stop the #VALUE error. Is it something to do with the way I am copy paste special the data as 'values and number formats only' from the original spreadsheet or something to do with the original spreadsheet containing the formulas?

[Larbec]

I have tried copy and paste the columns as format 'general' to remove any text as this did occur to me as well but it still does not stop the #VALUE error. Is it something to do with the way I am copy paste special the data as 'values and number formats only' from the original spreadsheet or something to do with the original spreadsheet containing the formulas?

I am not sure, save a copy and then you can try to copy and paste it differently and see what happens. I've not given up just think to think a bit and perhaps someone else with more expertise than I can chime in. I know these things can be aggregating

Do you do any color formatting of any conditional formatting with this? The reason I ask is because I wrote a little Macro thast "fixes" any and all numbers BUT it will take away any CF you may have. I need to redo the Macro so it does not do this. Save a copy and you can try to run it

Please Login or Register  to view this content.

[moneypennie21]

Hi I am a bit rubbish with Macros and when I ran this one o my data it changed all the percentages I had in one column to either 100% or 0%. Not sure if I did something wrong!

[Larbec]

Hi I am a bit rubbish with Macros and when I ran this one o my data it changed all the percentages I had in one column to either 100% or 0%. Not sure if I did something wrong!

I'm not ran with % in cells before. You would high light the area(s) you want ran and then run the Macro. Hopefully you saved a back up copy. Thus may not do the trick

Can you upload a sample? Perhaps others can chime in to help with more experience than I.

Try this in an unused cell
=ISNUMBER(Xxxx). Xxxx is any of your column and row numbers like A:1
That formula will return FALSE if you have text in the cell.


If you only have a numeric value it will return TRUE