+ Reply to Thread
Results 1 to 10 of 10

Clear cells that contain a formula IF?

  1. 12-03-2017, 05:26 AM #1
    930310
    930310 is offline
    Registered User
    Join Date
    12-03-2017
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Google Spreadsheets
    Posts
    5

    Clear cells that contain a formula IF?

    I have created the following spreadsheet that allows the users to insert any given date between 1924 and present and it'll return the oldest living Americans on that day.

    https://sites.google.com/site/930310jl/

    The design is like this:
    s6XMppT.png

    I am now trying to figure out a formula that makes the entire row (in the By year sheet) empty if the age of the retrieved person is less than 110 (so that the 119-year-old in the example above wouldn't be listed if the date was earlier than 24 sep 1990 or later than 30 dec 1999, but adding an IF(DATEDIF(C10;A8;"Y")<110;"") to the current formula in B10:

    IF($B$6>YEAR(TODAY());"";IF($B$6<=1923;"";IF(ROWS(Blad1!$D$3:$D$1461)>=VALUE(A$8);"";INDEX(Blad1!$A$3:$B$1461;SMALL(IF(Blad1!$D$3:$D$1461>=VALUE(A$8);ROW(Blad1!$A$3:$B$1461)-ROW(Blad1!$A$3)+1);ROWS(F$10:F10)))) ))

    Only returns an Error because it is a circular dependency. Is there any way to sidestep this and make a formula that only returns the people who were aged 110+ on the given date?
    Reply With QuoteRegister To Reply
  2. 12-03-2017, 10:51 AM #2
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Clear cells that contain a formula IF?

    Hi 930310- I'm not seeing a circular reference, but your INDEX function appears to be missing a column parameter. Perhaps: 
    Please Login or Register  to view this content.
    ...or you could change the referenced range to a single column: 
    Please Login or Register  to view this content.
    Last edited by leelnich; 12-03-2017 at 11:06 AM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
    Reply With QuoteRegister To Reply
  3. 12-03-2017, 11:45 AM #3
    930310
    930310 is offline
    Registered User
    Join Date
    12-03-2017
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Google Spreadsheets
    Posts
    5

    Re: Clear cells that contain a formula IF?

    Hi.

    Your formulas do not help since they don't return the date of birth.

    The circular reference is the formula: =IF(DATEDIF(C10;A8;"Y")<110;"";IF($B$6>YEAR(TODAY());"";IF($B$6<=1923;"";IF(ROWS(Blad1!$D$3:$D$1461)>=VALUE(A$8);"";INDEX(Blad1!$A$3:$B$1461;SMALL(IF(Blad1!$D$3:$D$1461>=VALUE(A$8);ROW(Blad1!$A$3:$B$1461)-ROW(Blad1!$A$3)+1);ROWS(F$10:F10))))
    ))) in B10.

    The formula first runs the DATEDIF formula when C10 is empty, which returns a zero value and then the index function and returns the person.

    As I explained above, I want A10:E10 to be blank if the age of the person returned to B10 from the formula is less than 110.
    Last edited by 930310; 12-03-2017 at 11:47 AM.
    Reply With QuoteRegister To Reply
  4. 12-03-2017, 12:41 PM #4
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Clear cells that contain a formula IF?

    I understand your issue, I was merely suggesting that you have ANOTHER problem which (on my machine) generated a #REF! error.
    Re: the circular reference, I see 2 possible approaches:
    1) Remove the C10 reference in your DATEDIF() function. Retrieve or calculate that value without making ANY reference to row 10.
    Then you can test if B10 ="" in the remaining cells.
    2) A bit of a hack: use the DATEDIF() test as a Conditional Formatting formula which formats the font color same as background color.
    Last edited by leelnich; 12-03-2017 at 12:46 PM.
    Reply With QuoteRegister To Reply
  5. 12-03-2017, 01:34 PM #5
    930310
    930310 is offline
    Registered User
    Join Date
    12-03-2017
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Google Spreadsheets
    Posts
    5

    Re: Clear cells that contain a formula IF?

    I don't encounter the issue that you have.

    1) I don't see how this will work
    2) I tried using conditional formatting but it did not work. When I used the formula =DATEDIF(C10;A8;"Y")<110 the cells (C10:E10, not B10) turned white even if the value was above 110, the other columns (A10:C10) didn't turn white either when it was less than 110 (D10:E10 did however turn white).
    Reply With QuoteRegister To Reply
  6. 12-03-2017, 01:47 PM #6
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Clear cells that contain a formula IF?

    2) You just need to use some absolute references. With B10:E109 selected, CF formula is: =DATEDIF($C10;$A$8;"Y")<110
    Last edited by leelnich; 12-03-2017 at 01:49 PM.
    Reply With QuoteRegister To Reply
  7. 12-03-2017, 02:05 PM #7
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Clear cells that contain a formula IF?

    Quote Originally Posted by 930310 View Post
    ...1) I don't see how this will work...
    Where does C10 value come from? Can B10 fetch it directly from the source instead of reading C10?
    Barring that, if Google Sheets allows hidden columns, put the static values currently in column C in column F and hide it. Then B10 formula: 
    Please Login or Register  to view this content.
    ...and C10 formula: 
    Please Login or Register  to view this content.
    Last edited by leelnich; 12-03-2017 at 02:10 PM.
    Reply With QuoteRegister To Reply
  8. 12-03-2017, 02:28 PM #8
    930310
    930310 is offline
    Registered User
    Join Date
    12-03-2017
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Google Spreadsheets
    Posts
    5

    Re: Clear cells that contain a formula IF?

    I managed to solve the issue by using conditional formatting for each column.

    Thanks for your help.
    Reply With QuoteRegister To Reply
  9. 12-03-2017, 02:31 PM #9
    930310
    930310 is offline
    Registered User
    Join Date
    12-03-2017
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Google Spreadsheets
    Posts
    5

    Re: Clear cells that contain a formula IF?

    Quote Originally Posted by leelnich View Post
    Where does C10 value come from? Can B10 fetch it directly from the source instead of reading C10?
    Barring that, if Google Sheets allows hidden columns, put the static values currently in column C in column F and hide it. Then B10 formula: 
    Please Login or Register  to view this content.
    ...and C10 formula: 
    Please Login or Register  to view this content.
    The C10 value is the value in Blad1!B3 (the birthdate of the person that will be in By year!B10).
    Reply With QuoteRegister To Reply
  10. 12-03-2017, 03:02 PM #10
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Clear cells that contain a formula IF?

    It seems you have figured this out, glad I could help.
    Quote Originally Posted by 930310 View Post
    The C10 value is the value in Blad1!B3 (the birthdate of the person that will be in By year!B10).
    To complete my other line of thought, if C10 fetches that value using a lookup formula that doesn't depend on other cells in row 10, you could replace [the C10 reference] in B10 with a copy of that formula. You eliminate the circular reference by bypassing C and going directly to the source.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA to Clear Certain Cells of Formula only.
    By oneblondebrow in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-15-2017, 12:47 PM
  2. VBA to Clear 1 Cell and Clear other cells Formula only
    By oneblondebrow in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-17-2017, 12:56 PM
  3. VBA to Clear Cells that Contain #N/A and also VBA to re populate formula in #N/A cells
    By Gerry Pasveer in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-04-2016, 02:00 PM
  4. Trying to Clear the same part of a formula in multiple cells...w/ No luck
    By JF1998 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-15-2016, 03:37 PM
  5. week end formula & clear cells button
    By bassett in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-26-2009, 11:56 PM
  6. Look Through Sheet & Clear Cells which do not have formula
    By pr4t3ek in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2009, 12:14 AM
  7. Formula to clear cells based on another cell value
    By mrdata in forum Excel General
    Replies: 2
    Last Post: 02-22-2008, 05:58 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1