Problem with IF formula

Hi--

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
nomiosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gtglonerCommented:
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.
0
gtglonerCommented:
oops, sorry didn't read the question well enough!
0
barry houdiniCommented:
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

=MAX(0,COUNTA(Reviews!A:A)-21)+ROWS(AD$1:AD2)

copy down to AE21

Now in Associate Review A2 put this formula

=IF(INDEX(Reviews!A:A,$AE2)="","",INDEX(Reviews!A:A,$AE2))

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.

=IFERROR(IF(INDEX(Reviews!A:A,$AE2)="","",INDEX(Reviews!A:A,$AE2)),"")

Hide column AE if required, see attached

regards, barry
27383737.xlsx
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

nomiosAuthor Commented:
Hi,

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.

0
redmondbCommented:
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.

Regards,
Brian.
formula-V3.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
redmondbCommented:
Thanks, Nomios!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.