+ Reply to Thread
Results 1 to 7 of 7

Nested IF or LOOKUP Formula needed?

  1. #1
    Registered User
    Join Date
    09-11-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    42

    Nested IF or LOOKUP Formula needed?

    I have attached a table I am trying to work from, I need to produce a report for the quarter periods based on the data in the summary sheet (date added / date deleted).

    If a vehicle was either added or deleted between X dates (quarter periods) I need it to be copied accross to the appropriate sheet i.e

    Line 5 = 1st Quarter (date deleted - 30/01/2009)
    Line 6 = Would need to appear in the 2nd & 3rd Quarter (date added - 11/05/2009 (2nd Q) date deleted - 20/07/2009 (3rd Q))
    Line 7 = 2nd Quarter (date added - 17/02/2009 / date deleted - 18/02/2009)
    Lines 8 & 9 = Not appear anywhere until a date had been inserted against it
    Line 10 = 1st Quarter (date added - 01/01/2009 / date deleted - 02/01/2009)
    Line 11 = Not appear anywhere until a date had been inserted against it
    Line 12 = 2nd Quarter (date deleted - 08/05/2009)
    Line 13 = Would need to appear in the 2nd & 3rd Quarter (date added - 14/05/2009 (2nd Q) date deleted - 18/08/2009 (3rd Q))

    I have tried using IF statement but not sure if I should be using some form of LOOKUP formula.

    Also in column 'G' I have the word 'inception'. Would this effect any formula in any way?
    Attached Files Attached Files
    Last edited by shudder; 09-11-2009 at 11:24 AM. Reason: Nested IF or LOOKUP Formula needed?

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669

    Re: What type of formula do I need?

    Welcome to the forum.

    Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What type of formula do I need?

    I've added a simple set of quarterly "keys" to your summary in columns J:M. These create a unique sequential index for each quarter and as you've noted, the same row may qualify for multiple quarters.

    You can hide/unhide those key columns if you wish by clicking the -/+ sign above column N.

    This is a realtime book now, the quarterly sheets will fill themselves out as you do your work on the Summary. The quarterly sheets only have formulas for the first 100 rows.


    Have a look. The final formulas bringing the data across to the quarterly sheets is INDEX/MATCH, a very robust way of doing a LOOKUP and does not suffer any of the limitations you find with LOOKUP, VLOOKUP or HLOOKUP. It's my favorite lookup technique.
    Attached Files Attached Files
    Last edited by JBeaucaire; 09-11-2009 at 11:47 AM. Reason: title updated, solution reinstated
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    09-11-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    42

    Thumbs up Re: Nested IF or LOOKUP Formula needed?

    Thanks for that, however when I copy and past my raw data it does not bring across the correct information, for instance there were 60 events in the first quarter.

    I have tried to analyse myself however I cannot get a grip on the arrangements, can you take a look at my full list attached.

    Also can you recommend anywhere via self learning or courses which will help me with this type of work?

    Thanks again for you help
    Attached Files Attached Files

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Nested IF or LOOKUP Formula needed?

    When you unhid the key columns, didn't you notice the formulas in J,K,L and M only went down a few rows? You should copy those down as far as you think you'll need rows watched, then hide them again...perhaps 1000 rows or so.

    Then, on each Quarter sheet, you copied the formulas down INSIDE the charts so they go down 500 rows, but the "key" formula in column A only goes to row 100, so you'll need to make sure those get copied down the same number of rows on each quarter sheet.

    Be careful on the quarter sheets, the formula in A4 should not be copied, only the formula in A5 down.

    Lastly, on the summary and the quarterly sheets, try putting your cursor on cell A5 then selecting Window > Freeze Panes. Now scroll down a ways...that should be helpful.

  6. #6
    Registered User
    Join Date
    09-11-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    42

    Re: Nested IF or LOOKUP Formula needed?

    I didn't notice there were formula's in those cells, so now yes with the suggestions you made all is working perfectly.

    I am looking to book myself in an Excel course for this type of work, can you make any recommendations. I have already done NVQ to level 3 (UK qualification) but that was some years ago and didn't touch on anything like this.

    Once again thanks for helping with this, VERY much appreciated.

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Nested IF or LOOKUP Formula needed?

    No, I wouldn't have any recommendations, I've learned by stumbling through it all on my own and with the help of forums like this one. Sorry. Maybe if you search the forum for "tutorial" you will find previous answers to that question.

    =======
    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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