+ Reply to Thread
Results 1 to 5 of 5

Conditionally set a Cell value

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Conditionally set a Cell value

    I have a spreadsheet which calculates a value in a cell. There is a date cell as well. What I want to be able to do is save the value in the 'Value Cell' to a table with a date (month) column. But, only update the row in the table if the date (month) is current.

    Broadly speaking it looks like that shown below (and attached).

    Date 18-Oct-21 Oct-21


    Value 100 Saved Values
    Oct-21 100
    Nov-21
    Dec-21
    Jan-22
    Feb-22
    Mar-22
    Apr-22
    May-22

    As long as the date is in October 21, then the Value column is stored in the Saved Values table next to Oct-21.
    If on 31st October 21 the value is 105, then the table would show 105 against Oct-21. If on 1st November 21 the value increases to 106, the Oct-21 would still show 105 but now Nov-21 would have 106 against it.

    For the Saved Value cell I have written this;

    =LET(
    CurrentValue, $F5,
    IF(E5=$F$1,$C$4,CurrentValue))

    But this leads, understandably, to a circular reference. I could disable circular reference checking for the whole worksheet, but this feels wrong. Can anybody suggest another way? VBA?
    Attached Files Attached Files
    Last edited by Nerdio; 10-18-2021 at 08:55 AM.

  2. #2
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: Conditionally set a Cell value

    I cannot attach my spreadsheet example. Clicking on the paper click just gives a thin white rectangle. Sorry.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Conditionally set a Cell value

    The paper clip icon has not worked for many years.

    Follow the guidelines in the yellow banner at the top of the screen.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: Conditionally set a Cell value

    Thank you - uploaded now :-)

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Conditionally set a Cell value

    I have a spreadsheet which calculates a value in a cell.
    I feel as if the way the "Value Cell", C4, is calculated may determine the way to accomplish what you want.
    The easiest would be if the "Value Cell" was calculating a value based on values in a source table that had corresponding dates. In that case you could just base the values in column F to the values in the source table.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 3
    Last Post: 03-17-2021, 08:19 AM
  2. Replies: 15
    Last Post: 07-31-2020, 10:10 AM
  3. conditionally format cell B colour based on what number is entered into cell A
    By bell1986uk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2018, 07:22 PM
  4. Replies: 1
    Last Post: 01-04-2017, 06:00 PM
  5. Replies: 1
    Last Post: 01-04-2017, 05:59 PM
  6. Replies: 7
    Last Post: 11-25-2013, 02:19 AM
  7. Replies: 4
    Last Post: 02-14-2013, 01:45 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