Function to filter excel table data in to a new dataset based on value defined in another table

0 votes

I have two tables of data, the first is EmployeeSalaryTbl which holds the salaries for employees over time:

Salary Info

and the second StaffDetailsTbl which has details on the employee discipline etc.

Staff Details

I'd like to repurpose a function I use to calculate total employee wages each month so that it only calculates salaries that are reliant on a specific discipline in a given cell, like programming.

To do this, I wondered if it was possible to filter the EmployeeSalaryTbl[Employee], EmployeeSalaryTbl[Salary Start Date], EmployeeSalaryTbl[Salary End Date], etc. results to only contain the rows where the employee has the Programming discipline through a lookup in the StaffDetailsTbl.

Feb 16, 2023 in Others by narikkadan
• 63,600 points
882 views

1 answer to this question.

0 votes

If you just want to add up the totals and it doesn't have to look attractive, This might be placed in the adjacent column to your table, beginning with the first row you want to examine. then add the values together at the bottom. If you wish to check for different disciplines, you can edit the place where it says programming to make it a cell reference.

=IF(INDEX(StaffDetailsTbl[Discipline],MATCH(EmployeeSalaryTbl[[#This Row],[Employee]],StaffDetailsTbl[Employee],0))="programming",EmployeeSalaryTbl[[#This Row],[Salary]],"")
answered Feb 16, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,600 points
1,125 views
0 votes
1 answer
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10, 2022 in Others by gaurav
• 23,260 points
7,930 views
0 votes
1 answer

Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,600 points
1,534 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
4,052 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
2,000 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
981 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,697 views
0 votes
1 answer

Select data that meet criteria from a table, adding it to a combobox in userform VBA Excel

Fill Combo Box With Matches Sub GetSourceAcc() ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,000 points
884 views
0 votes
1 answer

Another method to extract the last row containing data in a column

The "used range" might not be accurate. ...READ MORE

answered Mar 28, 2023 in Others by Kithuzzz
• 38,000 points
604 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP