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:J4
The formulas used may not make any sense until you read through this entire post:
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.
In Sheet2!A1:B4 -
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%
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.