+ Reply to Thread
Results 1 to 48 of 48

Calculate the Date of an Item Based on the Date of a Similar Item in the Previous Month

Hybrid View

  1. #1
    Registered User
    Join Date
    12-21-2017
    Location
    Wokingham, England
    MS-Off Ver
    office 365 2013
    Posts
    46

    Re: Calculate the Date of an Item Based on the Date of a Similar Item in the Previous Mont

    The errant cell seems to be C15 on the May sheet which displays the correct result from the function, No circular reference arrows are displayed. I have attached the Workbook with additional sheets.

    To reproduce the circular reference in the new workbook do the following:
    1) From the May sheet select the cell containing the function and place the curser in the formula bar.
    2) Invoke the function by pressing return. This will calculate the date for May correctly using the hard value in Apr!C13 as a seed.
    3) Move to the Jun sheet and do the same. In this case we are looking at the result of the UDF in May!C15 rather than a hard value. This should result in the circular reference error message being displayed.
    4) Clearing the message leaves a zero (0) value displayed in the cell containing the UDF.
    5) Inspecting the Error Checking drop down > Circular references from the Formulas ribbon shows the cell reference May!$C$15 as the one containing the circular reference.

    It would seem that we need the value displayed in the cell rather than the contents, a bit like copying a cell containing, say =1+2+3 then using Paste Special>Paste Values123 to get the cell to display 6. I am not sure how to do this in VBA, or indeed if it can be done.
    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. [SOLVED] Index match date, receipt number, invoice number and amount from 3 worksheets
    By sunboy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-16-2020, 12:16 PM
  2. Formula To Calculate Month in a Previous Date
    By breader21 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2020, 10:44 PM
  3. Replies: 13
    Last Post: 11-22-2019, 08:27 PM
  4. [SOLVED] Actual Receipt Date Vs. Original Receipt Date (Compare & Provide Result)
    By Rajeshkumar R in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-05-2018, 07:06 AM
  5. Replies: 5
    Last Post: 02-06-2013, 02:11 PM
  6. Date of Receipt based on In/OUT
    By cmcconna in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2010, 06:47 PM
  7. Auto calculate end date of previous month
    By BusterBoy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-28-2009, 07:57 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