Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Excel

  1. #1
    New Lounger
    Join Date
    Sep 2012
    Posts
    13
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel

    I use Excell 2010 on Win 7 PC. When I am in a worksheet and add a row in the middle the prior formulas above and below disappear and I have to reformat the formula for each added cell. Anyone know how to default an added row to the adjoining cells.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I think the only way is to do this with named formulas. If you provide details on your setup I can walk you through creating the appropriate named formulas

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2012
    Posts
    13
    Thanks
    0
    Thanked 1 Time in 1 Post
    Steve, thanks for the offer to assist. I think the problem is rather basic but since I obviously don't know how to solve it, I'll attempt to simply-fie it!
    In column B on my spreadsheet I have named "Date" I only want the month and day shown, when I originally created the sheets I formatted the entire column to show month/day only.
    Works OK till I add a row in the middle of the worksheet, then when I type in the month/day instead of 9-28 it comes out as 28-Sept. It's not a major problem to reformat the single cell, it's just an irritant after I present it in the cell the way I want it. Hope this gives you and idea of my question. It also happens in other columns on the same spread sheet, ie: I have another column "E" that is formatted in it's entirety for "number" and it changes to $xx.xx
    Thanks again for your offer to assist, I hope this simple explanation allows you to understand my need?
    Capn-Ken

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't see how the issue is a "formula issue". The formula issue I presumed was that you had a formula in eg B4 that refers to B3 (the cell above) and when you insert a row, it no longer refers to the cell above, but 2 cells above...

    The problem you are discussing seems to be a formatting issue. i can not replicate it in XL2010, nor do I recall it ever occuring in XL97 through XL2002. If you have a range formatted and insert a new row within the range, the new cells should take on the formatting of the cell above. Which version of XL are you using?

    Steve

  5. #5
    New Lounger
    Join Date
    Sep 2012
    Posts
    13
    Thanks
    0
    Thanked 1 Time in 1 Post
    Excel 2010

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    As I said I can not replicate the problem. When I insert rows within a formatted range, the new row takes on the formatting of the existing range...

    Could you describe exactly what you do to create the "problem", perhaps I do not understand the exact situation....

    Steve

  7. #7
    New Lounger
    Join Date
    Sep 2012
    Posts
    13
    Thanks
    0
    Thanked 1 Time in 1 Post
    Steve, I don't create the problem, it just happens like this. My columns are full B thru E with data & formulas, in column B I want the date to show month/day! If i insert a row to add another item and enter the date in B it returns the answer 28-Sept instead of 10-28 which is what I input. Now remember the column was formatted in the beginning to reflect 10-28, it only happens when I insert a row in the middle of the spreadsheet. My spreadsheet is a simple budget showing date (deposit D+ or debit D-) name of payee or payer the amount paid or in the next column the amount deposited with the final column being the balance. If you can't replicate the problem then I won't take up any more of your time as it is not that big a deal. I can just continue to reformat that cell or maybe I'll just change the entire column to reflect 28-Sept, same-o, same-o?
    Thanks for trying to help,
    Capn-Ken

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is what I tried to do to replicate the problem in XL2010
    I formatted column D to be "m-d".
    I entered dates in several rows of column D (all were formatted as set)
    I inserted rows within the data range, and they all were formatted as expected
    I went outside the range of rows in col D and entered a date: formatted as expected
    I inserted a row near the bottom of the worksheet and entered a date: formatted as expected.

    I can not replicate your symptoms, nor would I expect excel to act the way you indicate. Is it only this workbook or all workbooks? Does the workbook do that on a different computer?

    Steve

  9. #9
    New Lounger
    Join Date
    Sep 2012
    Posts
    13
    Thanks
    0
    Thanked 1 Time in 1 Post
    Yes to your question "does the workbook do that on a different computer" I purchased both computers at the same time! Now I just went to the laptop and inserted a row in the middle of a worksheet and typed in the date as I prefer it, it came out exactly as you noted above. I should have explained that my worksheet is built upward not downward. If there are several rows open, left to enter data later and I enter a date above the data shown but below other entries above, it shows 29-Sept, I have attached ( I hope) an example of a portion of an excel sheet reflecting what happens in the date column. Hope this clears it up, if not I'll live with it.
    Capn-Ken

    2013-09-29_1126.png

  10. #10
    5 Star Lounger
    Join Date
    Jul 2012
    Posts
    967
    Thanks
    662
    Thanked 58 Times in 57 Posts
    I don't use Excel 2010 but it sounds to me like the formatting you did should hold on any inserted rows.

    What has come to my mind is; you have 2 computers; the one you are working (desktop?) on has Excel 2010
    --- Does this mean the issue happens on the desktop? but not on the laptop?
    --- Do you have Excel 2010 on the laptop computer?
    --- Do you use that same file on both computers?

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I still can not replicate the problem on either of my laptops. Are you sure you have the entire column formatted and not just the data range that you have entries in? If the colum is pre-formatted the formatting should not be changed.

    Could you attach a sample workbook that demonstrates the problem?

    Steve

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    capn-ken,

    Highlighting the entire column Column B (or whatever column contains the mal-formatted dates) and then formatting to Date: 3/24 or M-01 should solve your problem.

    If the issue still persists, the following macro will insert a new row at the selected row then shift the rows down. But instead of using the default CopyOrigin:=xlFormatFromLeftOrAbove, it will use CopyOrigin:=xlFormatFromRightOrBelow. The inserted row will retain the formatting of the row below it.

    Code:
    Sub InsertRow()
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    End Sub

    HTH,
    Maud

  13. #13
    New Lounger
    Join Date
    Sep 2012
    Posts
    13
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks to all that responded in attempt to satisfy a not so big problem.. I am closing out the thread as I now believe the problem is created because I am adding a new row to the top of my data and it has not been formatted as all rows below it were.
    Thanks again for your help......
    Capn-Ken

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    But if the entire column is formatted, then it does not matter if it is before a data entry area. You indicated: "I formatted the entire column to show month/day only".

    I believe the only way with a "normal XL2010" to get what you describe is if the entire column was not formatted, only a particular range. And then you entered new data in a range that had "general formatting" (the default when no explicit formatting is applied) so had not been formatted to what you desired, but allowed Excel to choose the format instead.

    Steve

  15. #15
    New Lounger
    Join Date
    Sep 2012
    Posts
    13
    Thanks
    0
    Thanked 1 Time in 1 Post
    You could be right, I would normally format an entire column but could have inadvertantly picked a range below the DATE cell. I'll work on that.
    Capn-Ken

Page 1 of 2 12 LastLast

Posting Permissions

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