Formula across multiple sheets

[Linraen7]

Hello,

I am a beginner with excel and need some help.

I have a month-wise worksheet for salaries of the employees (sales Team).

I want to Highlight an employee's name in the salary sheet (4th Month) if he has not been paid his full salary for the past consecutive 3 months.(3 separate sheets).

Problem is the name are not in the same Row/column in each sheet as there are addition & deletions each month.

Kindly advise.
Lin

[NBVC]

Can you post a sample workbook showing your current setup (no confidential data) and what exactly you want to see happening...

[Linraen7]

Hi there,

Have attached the file.

My aim is to recognize employee who is not being paid for consecutive 3 months due to poor performance.

So in the 4th Month I take-off their name from this sheet and send to my manager in another sheet so that they can be given extra training.

Do let me know if I was able to explain my query properly...

[NBVC]

no attachment found....

[Linraen7]

Sorry .... cannot figure out where is the attach option

[Linraen7]

The File...

[NBVC]

Edit your last post, then click Go Advanced. Select the paperclip icon, then Browse and upload the .xlsx.

[NBVC]

See attached.

I started off by naming each range columns B:H in each sheet, I used the month abbreviations.

So go to Jan sheet, select B:H and type Jan in Name Box (just to left of Formula Bar). Repeat for each sheet with appropriate abbrev. name.

Then in Jul sheet, select column B and go to Home|Conditional Formatting|New Rule and select "use a formula to determine which cells to format"

Enter formula:

=AND(B1<>"New Joinees",VLOOKUP(B1,Apr,6,0)=0,VLOOKUP(B1,May,6,0)=0,VLOOKUP(B1,Jun,6,0)=0)

click Format and choose from Fill tab.

Repeat for other tabs.. with appropriate month names in formula.

[Linraen7]

Wow.. this is what I wanted ... Thank You.

But in my original sheet, I need column C to be compared instead of Column B, but I had already named the range starting with column B.

Now I have the below queries...
1) Doesn't the formula work if the comparison column is somewhere in between the range instead of the first.
2) IF "no" then how do I change the range and rename it.

Kindly advise.

Thank You
Lin

[NBVC]

You can create a formula to work with column C using same named ranges, but it becomes more complex... For this it is easier to go to Formulas tab, then Name Manager and change all the start columns to C, and adjust formulas to

=AND(VLOOKUP(C1,Apr,5,0)=0,VLOOKUP(C1,May,5,0)=0,VLOOKUP(C1,Jun,5,0)=0)

[Linraen7]

It worked well !!!

Thank you for your Guidance.

Lin