#DIV/0! and #NUM! errors are replaced with #VALUE! if I "copy and paste"

I am using excel 2010 32 bit.

I have 3 spreadsheet tabs:

1st - Staff Updates
2nd - 2017 Bogota B
3rd - 2017 Bogota Calculation

In the 1st tab, there are 3 columns with data. Two columns are date in date format (dd-mmm-yy) and the 3rd column is just a number (integer) in number format with 0 decimal places showing.

For example:

Date of Birth (column F) Date of Retirement (column G) Years of School (column H)
14-Oct-1998 1-Aug-2016 12

In the 2nd tab, I have 3 blank columns for me to "copy and paste" this data from the 1st tab. I can do so in a variety of ways: destination format, source format, etc. No matter how I copy and paste, it does so fine and all appears well, until I go to the 3rd tab.

In the 3rd tab, I have a group of cells linked to the 2nd tab. In this 3rd tab, I have for example:

=AVERAGE('2017 Bogota B'!$E$5:$E$1500) which is to calculate the average. As long as the 2nd tab cells are blank, this 3rd tab displays #DIV/0! which is fine. The problem is that after I copy and paste data from the 1st tab to the 2nd tab, this 3rd tab changes from #DIV/0! to #VALUE!

=MEDIAN('2017 Bogota B'!$E$5:$E$1500) which is to calculate the median. As long as the 2nd tab cells are blank, this 3rd tab displays #NUM! which is fine. The problem is that after I copy and paste data from the 1st tab to the 2nd tab, this 3rd tab changes from #NUM! to #VALUE!

As a summary, the #DIV/0! and #NUM! errors are replaced with #VALUE! if I "copy and paste" and nothing can get rid of the #VALUE! error.

To make this stranger, and the real reason for my confusion, is that this same spreadsheet as another set of 3 tabs with the exact same setup as I just described (i.e., 1st tab, 2nd tab, 3rd tab). They have the same formulas and relationships, etc. However, these other 3 spreadsheet tabs do not experience the #VALUE! problem.

To make it stranger still, if I manually type the data into the 2nd tab instead of pasting it, then the 3rd tab works fine. This won't work in the long run though because there are hundreds of entries.

I am not opposed to posting the spreadsheet online for review but I don't know how. I can remove private data.

I appreciate any insight.

Thank you.

Originally Posted by cat2005

I am not opposed to posting the spreadsheet online for review but I don't know how. I can remove private data.

You can edit your original post to do this. At the bottom of your post, click Edit Post.

Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window, and submit the post.

Wonderful. I will post it here.

First, start with the tab labeled "2017 Bogota Calculation".
This is the 3rd tab I mentioned in my earlier post.
Notice the top 2 grid charts (Finished School Years & Age with purple color) have the following:

a) #DIV/0!
b) #NUM!
c) #N/A

Second, go the tab labeled "Staff Update".
This is the 1st tab I mentioned in my earlier post.
Notice the multiple green columns.

Third, copy columns A through D (A5:A417, same for B,C,D).
Paste that data to the 2nd tab labeled "2017 Bogota B" in the same green column.

Fourth, look at the 3rd tab labeled "2017 Bogota Calculations" to see if anything changed.

Fifth, repeat this process starting with the 1st tab labeled "Staff Updates".
Copy only column H (H5:H417).
Paste that data to the 2nd tab labeled "2017 Bogota B" in the same green column.

Sixth,look at the 3rd tab labeled "2017 Bogota Calculations" to see if anything changed.

Seventh, repeat this process repeat this process starting with the 1st tab labeled "Staff Updates".
Copy columns F & G (F5:F417, same for G).
Paste that data to the 2nd tab labeled "2017 Bogota B" in the same green column.

Eighth, look at the 3rd tab labeled "2017 Bogota Calculations" to see if anything changed.
This is when you should see the #VALUE! error I spoke of earlier.

This is the problem.
Why am I getting the #VALUE! error?