How do I do a quick lookup?

Hello,

I am trying to create a template for our company's attendance policy where management just needs to input certain information into Excel and have it auto-generated into a Word template that they cannot edit.

I have them listing the attendance occurrences. I just can't find a formula that will help me determine the attendance rating based on the occurrences. I tried assigning points using the "IF" formula, but that got really confusing. Not sure how to use VLOOKUP for this...

Help please!!
10-16-2015-10-25-10-PM.png
10-16-2015-10-29-43-PM.png
KhuezeeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

[ fanpages ]IT Services ConsultantCommented:
It would be helpful if you could provide a sample workbook with the data shown in the two ".png" images you have posted.

The second image, that result in a "Performance Rating", is most likely able to be achieved using one/more/multiple examples of the following in-cell formulae:

MATCH
INDEX
HLOOKUP

The application of these, though, is difficult to determine without seeing the context in which you are referring to the matrix within your own workbook.
KhuezeeAuthor Commented:
Please see attached. I have it linked to a Word doc template that wouldn't work if I sent over to you. It just pulls all the information from this document into a letter format.

Basically, the manager would be able to choose the occurrences from the drop by menu then have the rating auto-generate at completion. This way, nobody can accidentally give an incorrect rating.
SAMPLE.xlsx
[ fanpages ]IT Services ConsultantCommented:
Thank you for the file.

Please could you explain how the total (3.05) in cell [D33] should be used to derive a "Performance Rating"?

I presume we will need some classification of the "Occurrence" values in the range [B25:B34] (probably as an additional column within worksheet [Sheet3]) to determine whether a specific value is a "Late/EQ" or an "Absence".  


The totals of each classification will then influence which column/row should be extracted from the "Performance Rating" matrix (that will also need to be defined within the workbook, rather than as an image pasted onto the worksheet).
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

KhuezeeAuthor Commented:
The D33 was a point system I tried to do before realizing that the number of lates/EQ doesn't equate to the number of absences... it screwed up my point system. )=
[ fanpages ]IT Services ConsultantCommented:
OK... so how do the individual entries within [B25:B34] relate to the "Performance Rating" grid?

Without the correlation between the two, I cannot advise further.
KhuezeeAuthor Commented:
Oh, I'm sorry! Those are the actual occurrences. They are listed in drop down menus to make it easier for managers to select (and to make sure everything is uniformed).

Occurrences that are on the vertical axis:
Tardy (2 tardies = 1 Late)
Late
Early Quit

Occurrences that are on the horizontal axis:
Notified Absence
Notified Absence - Sick
Failure to Report

So if someone has 2 tardies, 1 Late, and 1 notified absence then their attendance rating is Satisfactory. If they have 1 Late then it is Excellent.
psteffCommented:
I will let the others who are already commenting on the technical details continue, but as a side note - depending on your specific needs and the way your organization operates, you may want to forget about the Word template and recreate the needed report  in Excel.  That way you would not need to keep the two applications functioning together all the time.  You could have Excel (via a macro) print a PDF and get your non-editable document that way.
KhuezeeAuthor Commented:
That would be wonderful! How do you suggest I start with that? I'm not too familiar with macros so I didn't want to mess with that. Is there a code somewhere that I can just modify?
psteffCommented:
I am including some additional comments here:
1.      To auto print out the form/report using VBA, you will need to make the Excel workbook into a macro enabled workbook (.xlsm).
2.      You will need to create the finished report (what was going to be a Word Document) preferably on a separate worksheet that will have formulas in it so that it can be auto populated with the items entered on the worksheet that you show.
3.      As I worked through this, if all you need is for it to be printed out, here is the simple code.  With this code it will be printed off on whatever is your default printer.  This code should be put into VBA code area for the worksheet that houses the report.

Sub PrintWorksheets()
Sheets(Array("ReportSheetName")).Printout                            
End Sub

4.      Note that you will need to change the “ReportSheetName” to whatever your worksheet that the report is on is named.
a.      Please note that you will need the quotes around the name.
5.      On your “entry” worksheet, I would recommend putting a button on it to click when you want the macro to execute to print the report.
a.      If you do not know how to add the button, you can search the web for how to do it.
i.      I just searched the web for “excel add button to run macro” and I came up with this YouTube video on it.  If this doesn’t explain it well enough, perhaps some other websites will give you what you need with that search parameter.
ii.      Here is the link to the YouTube video
1.      https://www.youtube.com/watch?v=eLWRqGGdGEQ

I hope it goes well for you.

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
KhuezeeAuthor Commented:
Thank you so much!
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.