+ Reply to Thread
Results 1 to 4 of 4

Formula doesn't treat reference cell as blank when link pasted in reference cell

  1. #1
    Registered User
    Join Date
    03-16-2021
    Location
    Hull, England
    MS-Off Ver
    Excel
    Posts
    2

    Formula doesn't treat reference cell as blank when link pasted in reference cell

    I have been attempting to set up my spreadsheet with links to another spreadsheet so any changes will update automatically.

    I also want to have a time stamp in column Q when ever column L is updated.

    I have used a circular reference so that the time won't update each time so have used the following formula:

    =IF(L2<>"",IF(Q2="",NOW(),Q2),"")

    This works perfectly if the cells in column L are blank and get updated manually, however when I paste links to the other spreadsheet in these cells, even though the linked cells are blank it still creates the time stamp. I assume this is because the formula doesn't see the cells as blank because they contain links?

    Is there a way to get the formula to see the cells as blank?

    Thanks in advance.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,937

    Re: Formula doesn't treat reference cell as blank when link pasted in reference cell

    Instead of pasting links, use linking formulas like

    =IF(Sheet1!D2="","",Sheet1!D2)
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    03-16-2021
    Location
    Hull, England
    MS-Off Ver
    Excel
    Posts
    2

    Re: Formula doesn't treat reference cell as blank when link pasted in reference cell

    Thanks Bernie,

    Unfortunately it still doesn't appear to see the cell as blank. Also, it wouldn't be practical to type in formulas all the time.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,937

    Re: Formula doesn't treat reference cell as blank when link pasted in reference cell

    Try it with code:

    1) Copy this code.
    2) Right-Click the sheet tab of interest.
    3) Select "View Code"
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes as needed

    Please Login or Register  to view this content.

+ 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: 2
    Last Post: 03-02-2016, 07:05 AM
  2. [SOLVED] Really? How to include the a cell reference in a link formula?
    By orozvik in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-22-2014, 12:14 PM
  3. Replies: 1
    Last Post: 12-17-2011, 10:14 AM
  4. treat cell contents as reference not value?
    By Matt D Francis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-04-2006, 07:45 AM
  5. Replies: 3
    Last Post: 01-18-2006, 11:00 AM
  6. Use a cell reference in a pasted link formula
    By pfrost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-09-2005, 05:19 PM
  7. De-link a cell reference in a formula.
    By Hari Prasadh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-03-2005, 09: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