I have two tables, the first table has 6 column and each column has a defined range name as follows:
- Col 1: Broker_Name
- Col 2: Broker_Market
- Col 3: Client_ID
- Col 4: Client_Location
- Col 5: Client_Risk
- Col 6: Client_Amount
The second table organizes and summarizes the data in table 1, so in table two I have the broker name and the brokers market and a third column where it indicates if it is the clients location or the clients risk, and the last column has the value of the location or the risk of the client. The possible values of the third column are LOCATION and RISK.
I want to sum the clients amounts subject to the Broker Name, the Broker Market and the type of value we are taking, LOCATION or RISK, and I'm using the named ranges as an input to the formula.
I want the third condition to change depending of the third column in table 2. If it says LOCATION I want to reference the named range Client_Location and if it says RISK I want to reference the named range Client_Risk.
I have an additional table that connects RISK and LOCATION to the named ranges.