+ Reply to Thread
Results 1 to 10 of 10

How can I keep a formula persistent down a column?

  1. #1
    Registered User
    Join Date
    10-15-2011
    Location
    South
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    8

    How can I keep a formula persistent down a column?

    I have a column (G) that has a basic formula in it. My users seem to want delete rows and data across the board, in actuality they need to in some form. This page changes and entire rows need to be cleared out and replaced often.

    The problem is of course this kills the formula.

    How can I make that column always contain the simple formula?

    Hope that all makes sense, if not I can explain better or upload the file somewhere.

    Thanks,
    Gilligan

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How can I keep a formula persistent down a column?

    You can try Indirect function.
    Like suppose you have a formula in a cell
    Please Login or Register  to view this content.
    and if you insert a row above row 7, the above formula will become
    Please Login or Register  to view this content.
    But if you use Indirect Function like this.....
    Please Login or Register  to view this content.
    This formula will always refer to the cell G7.
    Google "Indirect Function in Exce" to read more about it.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    10-15-2011
    Location
    South
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    8

    Re: How can I keep a formula persistent down a column?

    Maybe I'm not following but I think I just failed to explain myself well.

    I have a formula INSIDE the G column, it does some math on a cell that they populate (in the F column) against a static cell ($J$1), then displays the results in G... H is used for "notes".

    So when they are done with this particular row/job they then need to clear the row out so they can put in a new job.

    Basically it's a work board that tracks how many days left on certain projects and color codes the rows accordingly. Pretty simple.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How can I keep a formula persistent down a column?

    Hi,

    Methinks it's time to upload the workbook and explain what actions cause the formula to be 'killed' as you put it. Also please explain what you mean by killed.

    It's almost impossible to answer your query unless we can see the problem in context. On the face of it if the formulae only refer to other cells on the same row that is being deleted there should not be a problem (provided of course J1 is not deleted or any cells that are precedent to J1)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    10-15-2011
    Location
    South
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    8

    Re: How can I keep a formula persistent down a column?

    Understood and agreed.

    Names were changed to protect the innocent.

    You will see that Row 7 needs to have the information removed because the job is completed. If it isn't clear still, then please let me know and I will do my best to clear up any confusion.

    Oh, and the macro doesn't need to be enabled to work, it is just one that updates the time stamp in J1 every 10 minutes.
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How can I keep a formula persistent down a column?

    Hi

    ...but I still don't understand what you mean by 'the formula' being 'killed'. Please clarify. When I select row 7 and delete the whole row everything seems normal as far as I understand your workbook.

    Incidentally I couldn't check whether a macro might be having an effect because the VBE is locked. When you upload a workbook that contains macros it's important that you don't prevent us getting 'round the back door'.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: How can I keep a formula persistent down a column?

    Generally speaking, you shouldn't delete a formula in a column. That just makes the whole thing inconsistent. It would be better to have a separate column that you use to indicate that a row has been "completed". You could then filter on that flag and, possibly, cut and paste the completed rows to another (audit) sheet.

    You seem to be making some classic "design mistakes", for example, blank rows, numeric fields with non numeric data ... C12: 56? means you can't add that cell into a total quantity (if you wanted/needed to). Deleting rows, cells, formulae, etc., means you have no audit trail ... once it's done, it's deleted and forgotten, no history, no analysis of past trends.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: How can I keep a formula persistent down a column?

    it's important that you don't prevent us getting 'round the back door'
    I tend not to activate code initially, and I'd never run code that I couldn't see.

    If I were that way inclined, and interested, I'd probably use a crowbar to break in But better if you unlock it for us


    Regards, TMS

  9. #9
    Registered User
    Join Date
    10-15-2011
    Location
    South
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    8

    Re: How can I keep a formula persistent down a column?

    Sorry, wasn't meant to be locked in any way... I think it's because it's "shared"... Try just taking that off if you want... But simple copied and pasted code for that updated date stamp.

    I'm sorry I didn't mean to be vague/cryptic... Just didn't articulate myself well.

    The way that this is being used is somewhat out of my control... My employees are using this and kind of defining how it will work... This is why there are some of the "design" flaws/faux pas. They were using a white board (physical) and had different sections for heat press and embroidery. I tossed this together as a "will this work/what else does it need" and they basically ran with it and put it in production.

    I agree I wouldn't mind a block that says "done" and if it equaled yes then hide it. But I would want it to hide automatically. Also, this isn't all that important as this is just to track in progress job statuses. We use quick books for all the other auditing purposes.

    Right now the way it is working is that when they are done they just highlight the cells and hit delete (killing it, in my terms... Sorry).

    When they do this it of course deletes the formula... Can't protect the cell because then they can't delete as one whole row.

    As you see they like to spread things out visually and that complicates things a bit too.

    When I return to work I'll make sure that everything is unlocked and reupload.

  10. #10
    Registered User
    Join Date
    10-15-2011
    Location
    South
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    8

    Re: How can I keep a formula persistent down a column?

    Ok, here is the "unshared" version, hopefully that will work.

    Also the K column needs to be preserved, if someone inserted or deleted a row or something crazy on rows 2-7 it would ruin K as well. Obviously this isn't major important since it's just a color chart/reference and they will likely not need it, but it's something I'm interested in if not too hard to preserve.

    *edit* guess I need to add the attachment!
    Attached Files Attached Files

+ 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. Formula not being persistent when copied
    By ks100 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-29-2013, 03:58 PM
  2. Making vlookup persistent
    By 91TwighlightGT in forum Excel General
    Replies: 15
    Last Post: 11-07-2011, 04:54 PM
  3. Persistent Formula / Graph
    By haysmj in forum Excel General
    Replies: 6
    Last Post: 11-18-2008, 10:38 AM
  4. How to Delete a persistent Toolbar?
    By alainr in forum Excel General
    Replies: 6
    Last Post: 10-06-2005, 12:05 PM
  5. [SOLVED] Persistent Data in a Combo Box
    By The Hawk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-30-2005, 06:06 AM

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