+ Reply to Thread
Results 1 to 1 of 1

GSheets: Conditional Hlookup? Continuous Streak? Please help this Habit Tracker

  1. #1
    Registered User
    Join Date
    Amsterdam, The Netherlands
    MS-Off Ver
    MS Office 16

    Question GSheets: Conditional Hlookup? Continuous Streak? Please help this Habit Tracker

    So this is going to be a messy post but I'll try to make sense of it all...

    It's the start of a new year, and like many others I have all the intention to get my a$$ in gear this year. With the help of Medium, Twitter, Google, and a lot of trying, I built myself a habit tracker that allows me to track my top 10 habits that I want to improve on.

    So far, so good right? Well, this is where I get lazy . As manually tracking these habits every day is a habit that I know that won't stick, I have built a Form that allows me to track from my phone.

    Problem 1
    After transposing the Form input and lifting the date from the time stamp, I tested it and it worked by using this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    (where L1 is the date on the tracker, Form Responses 1! O1 is the extrapolated date from the form timestamp and O4 is the input to one of the questions, Ja=Yes).

    Again, so far, so good. The day after I broke that logic. I filled in the form for that day, then later on realised I made a mistake and filled it out again. Now, the logic of L1='Form Responses 1'O1 no longer works. I tried to correct it by including HLOOKUP (without knowing exactly what I was doing)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    . Using this formula, it ticked the box. That would have been great if it wasn't for the fact that I used the formula on a date in the future, so it should have given a FALSE return.

    I know when I'm beaten, so I hope that better minds can help me. What I need from the formula is the following:
    1. The date on the tracker needs to match the date on the 'Form Responses 1' tab. Where it does not, it should give a FALSE return
    2. If the dates match, it should look for the value on the correct row (Ja=TRUE, Nee=FALSE) and feed this back to the tracker
    3. Where there are multiple occurances of the same date, it should always look for the most recent one

    Problem 2
    Once the problem with the tracking is solved, I get to really see how I'm doing. And I don't know about you guys, but I'm a sucker for streaks (Duolingo anyone?!?). However, the streak count I currently have only looks at the streak for that month and forgets all about it once the month is over. Formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Therefore what I would like to build is a continuous streak. So the "current streak" of the previous month is the starting value of the new month. However, it shouldn't show on the next month until it is actually there. Also, I would like the "Longest streak" to remember a streak value even if it has not occured in this month.

    I hope this all makes sense and that there are GSheet Gurus out there that can help me track my habits.

    If you made it this far, I thank you already and I look forward to any feedback/tips.
    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. Replies: 2
    Last Post: 06-11-2022, 07:19 PM
  2. habit and connection tracker
    By i'mlostt in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-11-2021, 05:45 AM
  3. Longest streak and current streak if data are placed in two columns
    By Lehoi in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-06-2016, 01:29 AM
  4. Addiction/Habit-Changing Sheet Current, Longest, Streaks of Consecutive Values
    By AllanTheCowboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-11-2015, 04:37 PM
  5. Last X games, Max Win and Loss streak, current streak with Excel formulas
    By Biogeek1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-12-2013, 11:00 AM
  6. [SOLVED] continuous conditional formatting
    By bite_tony in forum Excel General
    Replies: 3
    Last Post: 03-25-2013, 08:22 AM

Tags for this Thread


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