+ Reply to Thread
Results 1 to 11 of 11

Issue with deleted rows

  1. #1
    Registered User
    Join Date
    08-16-2019
    Location
    Roma
    MS-Off Ver
    2016
    Posts
    7

    Issue with deleted rows

    Hello guys,

    I have an issue. I have a Sheet 2 with formula references in another Sheet 1. The issue is related with deleting rows in sheet 1, because in sheet 2, in the other cells, on the right side of the formula references, there are written informations.
    Practically when I delete a row in sheet 1, It will be deleted also in sheet 2 the information but all the written informations in Sheet 2 will be moved up. It is not easy to explain, so I will upload a simplified example.

    1.png
    2.png

    So, the first picture is when nothing is deleted. The second picture instead is when the first row in sheet 1 is deleted. (Sheet 1 is simply 200, 300, 400, 500, 600, 700, starting from A2)

    As you can see in the second picture, all the informations from formulas and the written informations are no more related. Is it possible to link in someway the cells in order to not do that? Do I need a Macro or is it possible in another way to solve this issue?
    Last edited by richard123456789; 08-16-2019 at 06:09 AM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Issue with deleted rows

    Post a sample spreadsheet, remove any sensitive data, create a mockup example if necessary.
    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Issue with deleted rows

    Are you actually deleting rows (right click and delete) or just clearing the contents using the delete or backspace keys?

    While many people often refer to clearing the contents as 'deleting', the effect that this will have on most formulas is very different from acually deleting.

  4. #4
    Registered User
    Join Date
    08-16-2019
    Location
    Roma
    MS-Off Ver
    2016
    Posts
    7

    Re: Issue with deleted rows

    Yes right click and delete, there is no mistake.

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Issue with deleted rows

    We will need the file that Special-K asked for, without seeing the formulas and how they relate to the data this will be near impossible to resolve.

  6. #6
    Registered User
    Join Date
    08-16-2019
    Location
    Roma
    MS-Off Ver
    2016
    Posts
    7

    Re: Issue with deleted rows

    Quote Originally Posted by jason.b75 View Post
    We will need the file that Special-K asked for, without seeing the formulas and how they relate to the data this will be near impossible to resolve.
    THis is the file.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Issue with deleted rows

    Quote Originally Posted by richard123456789 View Post
    Is it possible to link in someway the cells in order to not do that? Do I need a Macro or is it possible in another way to solve this issue?
    I didn't really give it much thought before seeing the file, but now that I have, I don't think that it will be possible in any way, not even with a macro.

    The only way that I can see would be to remove the formulas by using copy and pastespecial > values.

    If you want to keep the formulas, then I'm afraid that I have no suggestions to offer.

  8. #8
    Registered User
    Join Date
    08-16-2019
    Location
    Roma
    MS-Off Ver
    2016
    Posts
    7

    Re: Issue with deleted rows

    Quote Originally Posted by jason.b75 View Post
    I didn't really give it much thought before seeing the file, but now that I have, I don't think that it will be possible in any way, not even with a macro.

    The only way that I can see would be to remove the formulas by using copy and pastespecial > values.

    If you want to keep the formulas, then I'm afraid that I have no suggestions to offer.
    Yes I have to keep the formulas. These are buildings, I need to write informations and remove it when they are sold. So, I need the formulas.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Issue with deleted rows

    In that case, the only other possibility is to have the descriptions in sheet 1 and use formulas in column E of sheet 2.

  10. #10
    Registered User
    Join Date
    08-16-2019
    Location
    Roma
    MS-Off Ver
    2016
    Posts
    7

    Re: Issue with deleted rows

    Quote Originally Posted by jason.b75 View Post
    In that case, the only other possibility is to have the descriptions in sheet 1 and use formulas in column E of sheet 2.
    I don't understand what will change in this way.

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Issue with deleted rows

    When you delete the rows, you will delete the descriptions as well. Everything that remains will still be lined up correctly.

+ 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. Referencing rows where rows gets added/deleted
    By Bezzie in forum Excel General
    Replies: 1
    Last Post: 11-29-2018, 02:31 AM
  2. [SOLVED] code to auto number rows - that will update when rows are added/deleted
    By dkitzerow in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-12-2015, 10:45 AM
  3. [SOLVED] Automatic Date Update on Row Change, Issue with Blank and Deleted Rows
    By nKife in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-13-2015, 10:07 PM
  4. [SOLVED] Rows need to be deleted
    By kesavanprabhu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2013, 09:37 AM
  5. Insert New Rows based on Number of Deleted Rows
    By adam2308 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-03-2012, 05:14 PM
  6. Replies: 6
    Last Post: 08-18-2012, 05:00 AM
  7. Sum values in cell from variable rows (rows are added and deleted)
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2010, 08:38 AM

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