Click here for important information about Computing.net.

Hi, I am Charles. I want to use spreadsheet; using the lookup

and match functions to work out the new

salary of some employees.The first table should have the names of the

employees in column one, next the salary,

next the new salary.The second table should have the names of

the employees(in a scattered order as in table

one) and their corresponding commission in %

in column 2.PROBLEM:

if you change the commission of an employee

in table two, the new salary should update it

self in table 1.in table one, if I decide to change the position

of an employee, the corresponding salary and

new salary should update themselves. (if john

was in row 5 and james in row 1; if I type john

in row one, everything about john should follow

automatically).I have been learning spreadsheet, manipulatinf

formulars but got stocked in LOOKUP

functions.Any ideas? Thanks

FYI...You're not being ignored. Based on my initial read, this looks a little complicated, only because you want to change something in Table 2, have those changes cause a change in Table 1, yet you say that Table 1 might not be static.

I'll have to play with this a little and will probably have a few questions.

For now, it's back to my plumbing project.

OK... Try this:

For my example, I am going to use three Tables:

Your Table 1 goes in Sheet1!A1:C4.

Your Table 2 goes in Sheet2!A1:B4

An additional table, Table 3, goes in Sheet1!H1:J4The formulas used may not make any sense until you read through this entire post:

Table 1:In Sheet1!A1:C4 - A B C 1 Name Salary New Salary 2 Sue =VLOOKUP($A2,$H$2:$J$4,2,0) =VLOOKUP($A2,$H$2:$J$4,3,0) 3 John =VLOOKUP($A3,$H$2:$J$4,2,0) =VLOOKUP($A3,$H$2:$J$4,3,0) 4 Fred =VLOOKUP($A4,$H$2:$J$4,2,0) =VLOOKUP($A4,$H$2:$J$4,3,0)By using VLOOKUP for the Salary and New Salary, you can change the position of the name and the data will follow. That's what Table 3 is for. More on that later.

Table 2:In Sheet2!A1:B4 - A B 1 Name Commission 2 Fred 10.0% 3 John 5.0% 4 Sue 4.3%Now for the 3rd table. I put this in Sheet1!H1:J4, but you can put it anywhere you want as long as you adjust the formulas to match.

The reason for Table 3 is to satisfy your desire to move items around in Table 1. If you hardcode the Salaries and the formulas for the New Salaries in Table 1, you won't be able to move names around. By "looking up" the names in another table (Table 3) the Salary data will follow the names.

Please note that I don't know exactly what formula you are using to determine the New Salary, so I'm using one that is often used to increase a number by a certain percentage, in the form of this example:

=200*(1 + 5%) = 210

=H3*(1 + Sheet2!B3)where H3 contains 200 and Sheet2!B3 contains 5%

Table 3:In Sheet1!H1:J4 - H I J 1 Name Salary New Salary 2 Fred 100 =I2*(1+VLOOKUP(H2,Sheet2!$A$2:$B$4,2)) 3 John 200 =I3*(1+VLOOKUP(H3,Sheet2!$A$2:$B$4,2)) 4 Sue 150 =I4*(1+VLOOKUP(H4,Sheet2!$A$2:$B$4,2))Here's how this works:

Hardcode your names and Salaries in Table 3, H2:I4.

In J2:J4, use the VLOOKUP formula I suggested (or whatever formula you plan to use to determine the New Salary) I used VLOOKUP to lookup the name in Table 2 and pull the Commission percentage into the formula.

So Table 3 is actually where the New salary is calculated.

Now, back in Table 1, the VLOOKUP's are doing nothing more than pulling the data from Table 3.

Please note that you can eliminate Table 3 if you add columns to Table 2 that contain the original Salaries and the formulas to calculate the New ones. (You can hide these columns if you don't want to see them) The concept of pulling the data into Table 1 from another table still holds, which is the only way you are going to be able to move names around in Table 1.

I hope that helps. Feel free to come on back if this doesn't make sense.

Thanks, very much

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Yes (14) | ||

No (14) | ||

I don't know (15) |

Discuss in The Lounge

Poll History