I'm not by an means a sophisticated excel user, but not a novice so let me start with that. I'm going to try and describe the situation as best I can.
I have a column where I need to be able to insert different specific vendor names that correspond to their general titles, e.g. records, administration, custody, etc. From this column I created a drop down list using data validation:
Records....ABC Co
Admin......DEF Co
Custody....GHI Co
...............^^These company names form the drop down list
The new drop down was created down a whole column of drop down boxes to populate the specific vendor names. The rows of this column correspond to specific fee structures that various vendors may use, so I can input the fee structures (like hard dollar billing versus hourly versus asset based) and compute a total. For reasons beyond the scope of this request, I must allow any vendor to appear possibly more than once in the column of drop down lists but also CANNOT have multiple fee structures on any given row:
Vendor.......FeeType1.....FeeType2....FeeType3...Total
ABC Co........................$1000.........................$1000
DEF Co........0.03%.........................................$200}Notice I had to use this company twice for two different
DEF Co.........................$100..........................$100}fee structures...
GHI Co..........................................$20/hr.......$40
^^ These are drop downs
Now, down the same page what I'm trying to do is create a new column from the previous drop down menu. On the corresponding rows, I need to be able to identify where any vendors appear on the fee list and then SUM all of the total fees. The goal is to be able to open a drop down to a company name and auto populate the totals.
Vendor..........Total Fees
ABC Co..........$1000
DEF Co...........$300
GHI Co...........$40
^^ drop downs
I've searched all over the web and tried IF, VLOOKUP, INDEX/MATCH, etc. to find the total fees that correspond to the company name that I drop down, but no example I can find seems to fit my situation and everything I've tried doesn't work properly. Can anyone help? Thanks in advance for any advice.
Andrew
I have a column where I need to be able to insert different specific vendor names that correspond to their general titles, e.g. records, administration, custody, etc. From this column I created a drop down list using data validation:
Records....ABC Co
Admin......DEF Co
Custody....GHI Co
...............^^These company names form the drop down list
The new drop down was created down a whole column of drop down boxes to populate the specific vendor names. The rows of this column correspond to specific fee structures that various vendors may use, so I can input the fee structures (like hard dollar billing versus hourly versus asset based) and compute a total. For reasons beyond the scope of this request, I must allow any vendor to appear possibly more than once in the column of drop down lists but also CANNOT have multiple fee structures on any given row:
Vendor.......FeeType1.....FeeType2....FeeType3...Total
ABC Co........................$1000.........................$1000
DEF Co........0.03%.........................................$200}Notice I had to use this company twice for two different
DEF Co.........................$100..........................$100}fee structures...
GHI Co..........................................$20/hr.......$40
^^ These are drop downs
Now, down the same page what I'm trying to do is create a new column from the previous drop down menu. On the corresponding rows, I need to be able to identify where any vendors appear on the fee list and then SUM all of the total fees. The goal is to be able to open a drop down to a company name and auto populate the totals.
Vendor..........Total Fees
ABC Co..........$1000
DEF Co...........$300
GHI Co...........$40
^^ drop downs
I've searched all over the web and tried IF, VLOOKUP, INDEX/MATCH, etc. to find the total fees that correspond to the company name that I drop down, but no example I can find seems to fit my situation and everything I've tried doesn't work properly. Can anyone help? Thanks in advance for any advice.
Andrew
