Payroll worksheet with VLOOKUP issues

• Hello

I am having issues with this VLOOKUP. It is on the tab ' Instructor Pay Data', column 'Z' Total Pay. =IFERROR(IF(W2<>"0",VLOOKUP(Y2,'Master Pay Rates'!\$A\$11:\$C\$2002,3,FALSE),"-"),"-"). The tab 'Master Pay Rates' I am sure is setup correctly, and the data validation for these names is on the tab 'Instructor and Location Payrate' columns 'Z' and 'AA'

Any help on this is appreciated.

James

Files

• Hello and Welcome to the Forum

Thanks a lot for attaching your sample file.

A couple of quick remarks :

1. From looking at your formula, there is no evident mistake

2. What is your expected result ?

3. Since everything is password-protected, there is no possibility to edit ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi again,

By looking into more details .... would appear to me :

1. Formula in cell Y2 should be : =X2&IF(J2="Express Cycle",J2,"")&W2

2. Formula in cell Z2 should be : =IFERROR(VLOOKUP(Y2,'Master Pay Rates'!\$A\$11:\$C\$2002,3,FALSE),"-")

This would require to be tested ... since I cannot do it

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Sorry, the password for the protected sheet is the word Password.

• Thanks.

Just tested both modifications as per post #3 ... can confirm they do operate as expected ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Just a quick question on the tab Instructor Pay Data. When I click cell 'Y2', the formula is =X2&IF(J2="Express Cycle",J2,"")&W2 Where is "Express Cycle" coming from? Am I reading this correctly J2 on this worksheet is MEGA All Levels?

Thanks

• Hope the modifications are working fine.

You are right : "Express Cycle" does represent an exception to the general rule and it would, indeed, come from Column J

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Does "Express Cycle" need to be there for the exception? Can it be any name as long as it's not MEGA All Levels, which is what is in J2?

• I know I have a lot of questions, and I am learning very much here. Can the command button on the tab 'Instructions' prompt a question before clearing the sheets? I would like the question to be "Would you like to clear the 'Instructor and Location Payrate data?' If yes, let the macro run as is, if no, can a new macro be added that will not clear the data on the tab 'Instructor and Location Payrate data'

Thanks

• Does "Express Cycle" need to be there for the exception? Can it be any name as long as it's not MEGA All Levels, which is what is in J2?

May be I was not clear enough ... "Express Cycle" only needs to be part of your formula ... if and only if you do have to manage "Express Cycle" in your reference pay rates sheet ... otherwise, you can obviously remove it ....

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• I know I have a lot of questions, and I am learning very much here. Can the command button on the tab 'Instructions' prompt a question before clearing the sheets? I would like the question to be "Would you like to clear the 'Instructor and Location Payrate data?' If yes, let the macro run as is, if no, can a new macro be added that will not clear the data on the tab 'Instructor and Location Payrate data'

Thanks

Yes ... obviously a Pop-Up Message can be shown with what is called a "Yes-No Message Box" ...

Since the are two areas to be cleared ... the initial question is :

Are you going to ask only ONCE to clear both areas ???

or

Are you going to ask the question TWICE to give the total freedom to the User ???

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• By the way ... you have not yet confirmed .... that your initial Vlookup question is now fixed ....

Meanwhile ... for illustration purposes ... below your Button has a Yes-No Message Box ... to be tested ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

Edited once, last by Carim ().

• Oh yes, the original VLOOKUP issue has been solved. THANKS! Let's ask the question twice: Are you going to ask the question TWICE to give total freedom to the User ???

• Glad to hear the Lookup issue is fixed

Regarding the suggestion for a Yes-No Message Box in post #13 :

1. Have you tested it ?

2. Do you feel confident enough to amend the example above in order to have the Message Box appearing Twice ... ?

For sure, you know the best way to Learn is ... through practice

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• I did get an error when I did this second message. I will ask for a bit more assistance on this.

Gràcies!

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Once you have tested the macro, feel free to share your comments

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• LEGEND! You are the best! I have this worksheet set with your BIG help and my little bit.

Thanks so much, this ticket is resolved.

Gràcies!

• Thanks a lot for your Thanks AND for the Like

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!