Copy/Paste from one workbook to another not recognized by formulas

[Pat]

I have created a workbook that has several worksheets that contain formulas
referencing the different worksheets. One worksheet contains the data used
in another worksheet. Then that worksheets data is used by a totals
worksheet. There are multiple workbooks that use the same data in the
worksheet. Rather than retype the data, I copy/pasted the data from one to
the other. However when I do the copy/paste the data is used by the one
worksheet but the total page will not recognize the data from the second
worksheet.

For example:
I have 3 tabs in a workbook. Tab1 = totals page, tab2 = results, tab3 =
data. I enter data on tab3 to be used by tab2. Tab1 then uses info from
tab2. All works if I type the data in tab3. But if I copy/paste the data
from another workbook into tab3, tab2 recognizes the data but tab1 doesn't
recognize the data from tab2.

Is this a format issue with the copy/paste of the data?

 

[Pete_UK]

Perhaps if you post some of the formulae you are using we might have a
clearer idea of what is happening. If your formulae in tab1 recognise
the formulae in tab2, then they shouldn't be affected by the data that
you paste into tab3, so perhaps if you explain more clearly what you
are getting and what you expect to get ... ?

Pete

 

[Pat]

Sure. Here is data from tab3, the Entries tab.

Back # County Exhibitor Horse
100
101 Burlington Janet Jones Bar
102 Burlington hope gill a royal legacy
103 sussex lindsey mckee until i kissed you
104 sussex kaitlyn murray boggies white diamond

Here is what tab2, Barrels Horse tab, looks like.
NUMBER OF ENTRIES 10

COUNTY BACK # EXHIBITOR HORSE POINTS
1 Gloucester 110 quinn o'leary homerun joe 10
2 Salem 109 Linda Bruce 9
3 Cumberland 108 Judy Max 8
4 Monmouth 107 allison smith trf a trace of lace 7
5 Mercer 106 katie devlin sparklin investment 6
6 Camden 105 nicole jacobsen smooth rockin 5
7 sussex 104 kaitlyn murray boggies white 4
8 sussex 103 lindsey mckee until i kissed you 3
9 Burlington 102 hope gill a royal legacy 2
10 Burlington 101 Janet Jones Bar 1

And here only back #'s are entered. Data for county, exhibitor, horse and
points are calculated or brought from tab3. Here are the formulas.
County formula is =IF(ISERROR(VLOOKUP($C6,Entries!$A$2:Entries!$D$501,2)),"
",(VLOOKUP($C6,Entries!$A$2:Entries!$D$501,2)))
Similar formulas for exhibitor and horse.
Points formula is
=IF(C3>9,10,LOOKUP(C3,{0,1,2,3,4,5,6,7,8,9},{0,1,2,3,4,5,6,7,8,9}))

Classes Barrels Horse
# entries 10
# classes county entered # entries by county

Atlantic
Bergen
Burlington 2
Camden 5
Cape May
Cumberland 8
Gloucester 10
Hunterdon
Mercer 6
Middlesex
Monmouth 7
Morris
Ocean
Salem 9
Somerset
Sussex 4
Warren

A formula is used to calculate the points for the county in the Barrels
Horse class.
Formula is =IF(ISERROR(INDEX('Barrels Horse'!$B$6:'Barrels
Horse'!$F$15,MATCH("burlington",'Barrels Horse'!$B$6:'Barrels
Horse'!$B$15,0),5))," ",(INDEX('Barrels Horse'!$B$6:'Barrels
Horse'!$F$15,MATCH("burlington",'Barrels Horse'!$B$6:'Barrels
Horse'!$B$15,0),5)))
Of course, each county has it's own formula.

The above shows the points allocated as they should be. This works as long
as the entries tab has the data typed in. But if I type the data in another
workbook and copy/paste it to the entries tab, tab1 does not pick up the
points awarded in tab2. Tab2 does recognize all the data in the entries tab.
Tab1 is having the issue.

I've since been reading some posts on this site and may have found a
solution. Using the paste icon with the number 12 only pastes the data not
the formatting. I tried this and it seems to work. I'm going to keep
testing this out to see if it really fixes my problem.

Let me know what you think.

Thanks for looking at this.