+ Reply to Thread
Results 1 to 12 of 12

Paste formula: Excel 2013

  1. #1
    Registered User
    Join Date
    02-07-2014
    Location
    Johannesburg
    MS-Off Ver
    2013
    Posts
    9

    Smile Paste formula: Excel 2013

    Hi I will try and be brief.
    I have complex spreadsheets with some complex formulas.
    In Excel 2013 recently installed, Windows 8, copying a formula to another set of cells, a fairly normal thing in Excel, wow, it looks like its done but it hasn't. It will copy the formula ONE CELL AT A TIME, however.
    Is this:
    1. A setting in Excel?
    2. A faulty installation problem?
    3. Something I don't know about in Excel 2013?

    Opening the same file in Excel 2007 on a different machine and copy and paste behave predictably.

    This is a disaster as one cannot assume cells have pasted and one has to check each cell.

    This naturally makes my Excel 2013 unusable.

    It seems to behave normally with cells without formulas.

    My Excel 2013 is fully updated as far as I know.

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Paste formula: Excel 2013

    Hello jlb333333,

    Welcome to the Forum.

    I do not have Excel 2013, but have you checked the enable Copy and Paste, and Drag and Drop, in your new Excel 2013 settings?

    Just guessing here.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Registered User
    Join Date
    02-07-2014
    Location
    Johannesburg
    MS-Off Ver
    2013
    Posts
    9

    Re: Paste formula: Excel 2013

    Hi. Thanks Winon.
    I can't find those specific settings to check.
    I've done some more checks.
    The problem seems at this stage to be limited to this kind of formula: =IF($KK3="";"";OFFSET(INDIRECT("[Filename.xls]Sheetname!"&$JF3);$JC3+$KK3+7;-4)). With Error checking on, Excel warns "Inconsistent calculated column formula". However the formula is(hopefully) correct. Have used it before. The formula results look/are correct. It just looks like Excel is trying to prevent an error. As I have stated above, Excel is happy to paste one cell at a time, but not more than one.

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Paste formula: Excel 2013

    Hello jlb333333,

    I think your "problem" may relate to Filename.xls. An Excel Workbook with a .xls extension goes only as far as Column IV, yet you are talking about Ranges far beyond that,i.e. =IF($KK3="";"";OFFSET(INDIRECT("[Filename.xls]Sheetname!"&$JF3);$JC3+$KK3+7;-4))

    Might it be that you are working in Compatibility mode with an .xls file?

    If so, save it in any later Excel versions, like xlsx,xlsm or xlsb, and then try again.

    Good luck!

  5. #5
    Registered User
    Join Date
    02-07-2014
    Location
    Johannesburg
    MS-Off Ver
    2013
    Posts
    9

    Re: Paste formula: Excel 2013

    Hi Winon.
    Thank you for your reply.
    I thought I commented on your reply yesterday but my comment is not visible.
    The formula is contained in an xlsm file and all is good as far as I know.
    I have done some more tests.
    I have activated the Excel clipboard pane in Excel 2007 and 2013. When copying this formula the Excel clipboard says in both cases "(preview not available)"
    With other cells with formulas details show up in the excel clipboard in both versions.
    I have opened an older file with the same formulas in and this file copies and pastes the formulas correctly.(although still"(preview not available)")
    It would seem to me the file itself is affected.
    Is there a way of doing a check on this or a good(not expensive) Excel file repairer?
    Many thanks for those who have read my questions.

  6. #6
    Registered User
    Join Date
    02-07-2014
    Location
    Johannesburg
    MS-Off Ver
    2013
    Posts
    9

    Smile Re: Paste formula: Excel 2013

    Hi all.
    One last thing for thought.
    If I edit a formula in a column and press enter, I get a small autocorrect options notification next to the cell. If I press this, I get an option "Replace all the cells in this column with this formula"If I select this Excel does just that, perfectly. The whole column fixed.
    Why is Excel happy to do it but it won't let me decide?

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Paste formula: Excel 2013

    Hi jlb333333,

    My Excel 2007 does not accept the formula you have provided, quoted below.

    =IF($KK3="";"";OFFSET(INDIRECT("[Filename.xls]Sheetname!"&$JF3);$JC3+$KK3+7;-4))
    And yet you say:
    With Error checking on, Excel warns "Inconsistent calculated column formula". However the formula is(hopefully) correct. Have used it before. The formula results look/are correct.
    I must admit that I do not get the gist of the ; in that formula nor how it is supposed to Offset what.

    I think Lotus used the ; whereas Excel uses ,.

    It would really be interesting to see a working sample with that formula deployed!
    Maybe you should try and redo that formula.

    Regards

  8. #8
    Registered User
    Join Date
    02-07-2014
    Location
    Johannesburg
    MS-Off Ver
    2013
    Posts
    9

    Re: Paste formula: Excel 2013

    Thanks Winon.
    I didn't spot the ;
    However this was a Windows setting from Region settings, Control Panel. Windows had set the "List separator" to ; I have now changed it to ,
    I had some considerable trouble with these settings previously by the way and found that the settings for South Africa did not suit me at all and I have changed them bit by bit.
    I will carry on checking but at this stage the situation remains the same.
    Any thoughts on my previous post?(The autocorrect story)

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Paste formula: Excel 2013

    Thank you for the feedback jlb333333,

    Any thoughts on my previous post?(The autocorrect story)
    To be honest, no. What I would suggest is that you copy the problem formula somewhere else as text, allow the System to do its Autocorrect, and then compare the differences. Please note that at times it does not really change anything, so don't be surprised if there is no diffirence between the two.

    Hopefuuly this could clarify what issues the System encounter with your formulae.
    Good luck!

  10. #10
    Registered User
    Join Date
    02-07-2014
    Location
    Johannesburg
    MS-Off Ver
    2013
    Posts
    9

    Re: Paste formula: Excel 2013

    Thanks again Winon.
    A good idea.
    I allowed autocorrect to "fix" my formula.
    It changed it to the formula before the last edit, which was only changing the +7 close to the end of the formula to read +3. In other words changing the +7 back to +3. This was merely an edit to read the correct column in OFFSET.
    After changing the +7 to a +3, the autocorrect notice disappears.??? However, Excel will still not copy the "corrected" cell.


    Other possible questions:
    The file is corrupted.(Is there an easy way of checking)
    Is there a difference between the capabilities of Excel in "Microsoft Office Professional Plus 2007" and the Excel in the "Office Home and Student 2013" Version?
    My Office 2013 installation is shaky and needs reinstallation.(Nightmare)
    My Windows 8.1's operation is shaky for some reason.
    I must give this problem a break. My brain is too tired of it at the moment.

  11. #11
    Registered User
    Join Date
    02-07-2014
    Location
    Johannesburg
    MS-Off Ver
    2013
    Posts
    9

    Re: Paste formula: Excel 2013

    Hi everyone.

    Good news.(for me especially):)

    I have fixed the problem described and discussed above, as usual for me, quite by accident.
    I was continuing to work on the same file and added some new columns at the end.
    I designed formulas and tried to copy and paste. Nope. Sometimes it would just paste a blank cell instead of the formula. Mostly it refused to do anything.
    I then got an idea.
    I highlighted the whole table and converted it back to a range.(I learned how to do that from an internet search, I did not know how)
    Guess what, I can copy and paste formulas everywhere I want.
    Also the funny warnings have disappeared.
    This is also true when I make the range a table again.
    It is quite a big spreadsheet, 396 columns by 896 rows with lots of formulas.
    I can only think that as it was set up in a previous version of Excel, it got itself in a fix with table rules.

    The fix was: convert the table to a range, and convert back to a table.

    :)
    Last edited by jlb333333; 03-30-2014 at 02:36 PM.

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Paste formula: Excel 2013

    Hi jlb333333,

    Thank you for the feedback.

    Good news! Glad you got it sorted out. I would never have imagined it to be a Table that could have been messing with us.

    Regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel 2013 x64 hangs during large paste
    By sportsguy in forum Excel General
    Replies: 1
    Last Post: 01-31-2014, 04:05 AM
  2. Simple formula to display a date (10/19/2013) as text (October 2013)
    By benyben123 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2013, 03:47 AM
  3. Excel 2013 formula not responding
    By samson jerry in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-05-2013, 06:36 PM
  4. Excel 2013: Paste Data into Visible cells only
    By nikams_85 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-15-2013, 03:26 AM
  5. Link Excel 2013 to Word 2013
    By drosen99 in forum Word Formatting & General
    Replies: 20
    Last Post: 02-26-2013, 08:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1