Dynamic Chart to show the range based on the condition

Hi Guys,

I have created a clustered column chart and scattered chart to show the market rate and the actual rate as shown below.
Final Result.JPG - Final Output

For the actual rate of each job titles with location I have been manually looking for FTE, Terms value by inputting respective values from the data stored in 'Inputs' sheet.
Input series.JPG - To input values for clustered chart based on the respective X axis (Job Title - Location)

Input Data.JPG - Looking for those values by selecting each 'Job Title-Location' 'Pay Rate' and their 'Column Position''

Now am finding it difficult to update the chart for dynamic job titles and location. I have to literally look for the location and their rates and column position to fill the chart. Is there a formula that I can write in X series and Y series to consider values based on the series name or something. I want it to pick values dynamically without having to manually adjust the X and Y series.

Change Input.JPG - Changing input for X and Y series values

Please let me know if my question is clear. I would really appreciate your help and owe you big time!

Thanks in advance!

Hi Oeldere,

Thank you for your reply.

The graph that I have created is clusteted column + scattered chart. I need help in making the graph series dynamic based on the values in series X and Y.

Thanks,