Problem with IF formula

Posted on 2011-10-06
Last Modified: 2013-11-05

I have a simple form where you fill out info, and it gets put in a review tab. Under "associate review" I want to be able to review the last 20 entries from the review sheet. And, I also have some reporting tabs.

I can't get the formulas to work in the associate review tab to pull the proper data from the review tab.  formula.xls
Question by:nomios
    LVL 17

    Expert Comment

    On your Associate Review sheet, you have #REF! errors in the formulas, these must be corrected for the VLOOKUP formulas on the Reviews sheet to work.
    LVL 17

    Expert Comment

    oops, sorry didn't read the question well enough!
    LVL 50

    Expert Comment

    by:barry houdini
    What does "last 20 entries" mean? You only have 13 entries there now so it's not very clear. I assume you want the lowest down positionally, so if there are entries in Review sheet down to row 100 that your "associate Review" will show the last 20, i.e. rows 81 to 100.

    I think you have a more complex formula than you need - try this setup using a "helper" column. In Associate Review cell AE2 put this formula


    copy down to AE21

    Now in Associate Review A2 put this formula


    and copy across and down to AA21

    Of course this will also pick up some errors from  the Review sheet. If you want to hide the errors try the above (second) formula with IFERROR, i.e.


    Hide column AE if required, see attached

    regards, barry

    Author Comment


    Thank you for your reply, but, this isn't exactly what I'm looking for. Let me try to explain a little more to clarify.

    This sheet is a method of tracking performance. I wanted it to show the last 20 performance reviews from one particular person. I don't want it to track every single reference created for every person. That's what the review tab is for.

    So, in the associate review tab, I want to be able to type in a name in D24. Cells A2-A20 are set to equal the name from D24. Once this occurs, I want it to pull data from the last 20 entries in the review tab from that particular person's name.

    LVL 26

    Accepted Solution

    Hi, Nomios.

    Please attached. Few notes...
    (1) You mention last 20 cases, but file showed 21 so I did that instead. Obviously it's a trivial change if you actually want 20.
    (2) I've add a number of helper columns/cells on both sheets. These are highlighted in yellow.
    (3) I've unlocked "Reviews".
    (4) I've added a load of test cases.

    LVL 26

    Expert Comment

    Thanks, Nomios!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now