Solved

Formatting between Numbers and Excel

Posted on 2011-03-07
8
203 Views
Last Modified: 2012-05-11
I am working with a client on a solution for integrating an iPad into his daily business life.

He is a builder so needs to be mobile, and the size of the iPad is perfect. He works on an excel spreadsheet with a list of orders and would need to modify this spreadsheet on the run. I have been able to string together DropBox, WebDav, DropDav and Numbers into a solution to get the file from his PC to the iPad and back to the PC, but am facing what looks to be a formatting issue between Numbers and Excel.

So once he gets back into the office he logs onto his PC and needs to copy/paste data from Spreadsheet into a new spreadsheet. For some reason when he uses the Paste function the result is a red outlined cell with white text and a light grey shade (see attached).

If I Paste Values I get the intended results (also in screenshot), but I am not using any non-default formatting in the original spreadsheet?
ScreenShot
0
Comment
Question by:Flipp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 250 total points
ID: 35066680
Dear Filipp,

You could try to ask your client to use paste special, and choose values, that way the formatting of the sheet will not be altered..


Kind regards


Eric
0
 
LVL 6

Author Comment

by:Flipp
ID: 35066754
Well, I already found that this could be a workaround, but I would prefer to look deeper to find out what any possible conflicts could be.
Surely this is something that others have tried to do.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35066834
Hi Flipp,

It is a weird formatting/conversion issue going from Excel to Numbers for iPad and back. I don't know if there is a solution other than to do paste special and values or use the format painter to fix it post-paste.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 250 total points
ID: 35066839
Hello,

if you don't want to trouble your client with learning how to use Paste Special - Values, you could have a Worksheet Change macro that removes any fill, font color and border formatting from the pasted cells.

Your client would have to have macros enabled to do this.

This is the code for Excel 2007 and later. It goes into the Sheet module (right-click the sheet tab, select View Code and paste the code into the code window)

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Target.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Target
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
End Sub

Open in new window


If you need the code for Excel 2003 or earlier, please pipe up.

cheers, teylyn
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35066897
Oooo, that's certainly better than Format Painter.  Nice job teylyn.
0
 
LVL 50
ID: 35066902
Here's the same macro code for Excel 2003 and earlier

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    .Font.ColorIndex = 0
    .Interior.ColorIndex = xlNone
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub

Open in new window


Any cell that is edited or pasted will be formatted with no background, automatic font color and no borders.

cheers, teylyn
0
 
LVL 6

Author Comment

by:Flipp
ID: 35066914
Does anyone have a copy of Numbers on Mac that they can test? I would like to know if this is isolated to Numbers App on iPad or Numbers in general.
0
 
LVL 6

Author Closing Comment

by:Flipp
ID: 35229462
Thank you all - did not see any update to my final comment so closing question.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question