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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pat - 21 Dec 2007 15:35 GMT

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?

Signature

Pat

Reply to this Message

Pete_UK - 21 Dec 2007 15:50 GMT

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

> I have created a workbook that has several worksheets that contain formulas
> referencing the different worksheets. One worksheet contains the data used
[quoted text clipped - 15 lines]
> --
> Pat

Reply to this Message

Pat - 21 Dec 2007 16:19 GMT

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.

Signature

Pat

> 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
[quoted text clipped - 23 lines]
> > --
> > Pat

Reply to this Message