G
Guest
PROBLEM: In Excel 2000, I have a table with (w/) a formula (described in the
Background section below) that finds & inserts pay rates from another
workbook (let's call it WB1) based on multiple criteria. I now have a 2nd
workbook (call it WB2) w/ additional rates that I want my table to also
check. I tried editing the formula to include the new workbook but ended up
getting an error. So, for simplicity's sake, I modified my existing formula
to find the rates for those criteria which weren't found by WB1 (basically,
the bottom half of my current table).
However, aside from correctly displaying the rate for the first company it
finds, it's incorrectly displaying the last rate found in WB2 for all the
remaining companies. I can't seem to figure out why it's doing this.
Eventually I want to have 1 unified formula for the whole table, but for now
(while I'm troubleshooting this part of the formula), I want to fix this
formula to find info from WB2 only.
BACKGROUND: My original formula finds companies that match multiple
criteria on one of my worksheets and then inserts the pay rate for these
companies in a separate column on that worksheet based on a pay rate table
located, currently, in a different workbook (WB1). This array-entered
formula works fine and is as follows:
=IF(L2="Level
1",100,INDEX('[WB1.xls]Coded'!O$3:O$340,MATCH(1,('[WB1.xls]Coded'!$S$3:$S$340=$AD2)*('[WB1.xls]Coded'!$K$3:$K$340=CONCATENATE($J2,"/",$K2,"/",$L2)),0)))
where from the WB1.xls file, col O contains the pay rates to be found and S
contains the Company names to be matched against based on values in my sheet.
From my worksheet, row 1 has headers in it. Col J is "Type", col K
"Program", col L "Model", col AD "Company", and col AJ is where I want the
rates to appear. In the formula, columns, J, K, & L are concatenated with
/’s to match the values found in col K of the WB1.xls Coded sheet (Example of
col K: Networks/Res/Home; following the format $J2/$K2/$L2).
For the bottom half of my table where rates weren't found in WB1, I
simply changed the formula to look only in WB2. The modified array-entered
formula is:
=IF(L140="Level
1",100,INDEX('[WB2.xls]Rates'!$O$2:$O$411,MATCH(1,('[WB2.xls]Rates'!$C$2:$C$411=$AD140)*('[WB2.xls]Rates'!$D$2:$D$411=$K140)*('[WB2.xls]Rates'!$E$2:$E$411=$L140)),0))
where in WB2, col O still contains the pay rates, but the following
locations are different from the original formula: col C contains the
Company names to be matched against, col D "Program", and col E "Model".
Unlike WB1, no concatenation is needed because in WB2, the Program and Model
are in separate columns. As stated earlier, in my table, col AD is
"Company", col K "Program", & col L "Model". I don't need to match col J
"Type". Sorry if this is lengthy but I wanted to be as specific as possible.
Background section below) that finds & inserts pay rates from another
workbook (let's call it WB1) based on multiple criteria. I now have a 2nd
workbook (call it WB2) w/ additional rates that I want my table to also
check. I tried editing the formula to include the new workbook but ended up
getting an error. So, for simplicity's sake, I modified my existing formula
to find the rates for those criteria which weren't found by WB1 (basically,
the bottom half of my current table).
However, aside from correctly displaying the rate for the first company it
finds, it's incorrectly displaying the last rate found in WB2 for all the
remaining companies. I can't seem to figure out why it's doing this.
Eventually I want to have 1 unified formula for the whole table, but for now
(while I'm troubleshooting this part of the formula), I want to fix this
formula to find info from WB2 only.
BACKGROUND: My original formula finds companies that match multiple
criteria on one of my worksheets and then inserts the pay rate for these
companies in a separate column on that worksheet based on a pay rate table
located, currently, in a different workbook (WB1). This array-entered
formula works fine and is as follows:
=IF(L2="Level
1",100,INDEX('[WB1.xls]Coded'!O$3:O$340,MATCH(1,('[WB1.xls]Coded'!$S$3:$S$340=$AD2)*('[WB1.xls]Coded'!$K$3:$K$340=CONCATENATE($J2,"/",$K2,"/",$L2)),0)))
where from the WB1.xls file, col O contains the pay rates to be found and S
contains the Company names to be matched against based on values in my sheet.
From my worksheet, row 1 has headers in it. Col J is "Type", col K
"Program", col L "Model", col AD "Company", and col AJ is where I want the
rates to appear. In the formula, columns, J, K, & L are concatenated with
/’s to match the values found in col K of the WB1.xls Coded sheet (Example of
col K: Networks/Res/Home; following the format $J2/$K2/$L2).
For the bottom half of my table where rates weren't found in WB1, I
simply changed the formula to look only in WB2. The modified array-entered
formula is:
=IF(L140="Level
1",100,INDEX('[WB2.xls]Rates'!$O$2:$O$411,MATCH(1,('[WB2.xls]Rates'!$C$2:$C$411=$AD140)*('[WB2.xls]Rates'!$D$2:$D$411=$K140)*('[WB2.xls]Rates'!$E$2:$E$411=$L140)),0))
where in WB2, col O still contains the pay rates, but the following
locations are different from the original formula: col C contains the
Company names to be matched against, col D "Program", and col E "Model".
Unlike WB1, no concatenation is needed because in WB2, the Program and Model
are in separate columns. As stated earlier, in my table, col AD is
"Company", col K "Program", & col L "Model". I don't need to match col J
"Type". Sorry if this is lengthy but I wanted to be as specific as possible.