+ Reply to Thread
Results 1 to 2 of 2

Capture a value in a changing field

  1. #1
    Registered User
    Join Date
    03-22-2018
    Location
    Hong Kong
    MS-Off Ver
    2016
    Posts
    1

    Red face Capture a value in a changing field

    Hello,

    I am running a projects tracker in my company and wish to capture the KPI values at the end of each week.

    How can I do this so that my captured data does not update as the tracker progresses the following week/s?

    My thought was that I wanted to have a cell which when marked 'Yes' (for example) the KPIs were captured at that time and they then dont change.

    The following week I would do the same again on the next row down.

    All help is appreciated

    Stuart

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Capture a value in a changing field

    Welcome to the forum.
    VBA is almost certainly the best way of doing this, but that's not my area of expertise and there is a (slightly complicated) way of doing it with formulae.

    First, you need to enable iterative calculations, as this method uses circular references to work. Click the File menu, choose Options and select Formulas. At the top-right you'll see a tick-box labelled Enable iterative calculation. Select it. You can leave the figures below for Max Iterations and Max Change as the defaults 100 and 0.001. Click OK.
    (I'm using 2010, but I believe 2016 is the same or almost the same.)

    Now, to the right of the column you have your KPI in, enter two new columns. Label one as KPI Copy and the other as Lock.

    For the sake of illustration, I'm going to assume that your KPI column is column D, so the 'copy' column is E and the 'lock' column is F. I'm also going to assume your data starts in row 2. Obviously, you should change the D/E/F / 2 references in the formulae below to match where your data is.

    In E2, enter this and drag down as far as you need:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    You can now hide column E (or make it width 1), as you don't need to see it anymore.

    You now need to surround your KPI formula in D2 in an IF formula, like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag that down as far as you need.

    Now if you enter 'Yes' in F2, D2 will be locked to the value of E2 which is a copy of D2. This has the effect of locking the cell.

    Attached is a file showing this working, using a RandBetween formula to simulate your KPI formula. If you press F9 on my sample file, you'll see that all the numbers change except the ones where the Lock column says 'Yes'.

    I hope that makes sense and does what you need.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

+ 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. decimal field of a number is changing when I strip off the integer field.
    By whburling in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-08-2017, 12:33 PM
  2. Changing the label of a Sum field in Values Field (DataField)
    By bezwlosy in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-18-2014, 06:57 AM
  3. Replies: 1
    Last Post: 08-13-2014, 09:38 AM
  4. capture value of a changing cell into data table
    By gkleckler in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-23-2013, 08:46 PM
  5. Changing a numerical field to a date field
    By MrSales in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 12-29-2006, 02:33 PM
  6. [SOLVED] Changing a text field to a date field
    By juliet in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 02-21-2006, 05:55 PM
  7. Replies: 1
    Last Post: 10-24-2005, 06:05 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