What if, or something else please help.

The issue I am having is i am not sure how to accomplish what i want.
I have two different tables with purchase price / sell price organized by locations and commodities.
What I want to be able to do is select a location for purchase price and select a location for sell price and have excel return the difference between the two locations for ALL commodities. (Point A to Point B)
An advance version I would like would be selection multiple locations and for each leg and returning the (MAX) difference for each (leg/way point A,B,C etc) with the condition that A must be sell able at B and B must be sell able at C) But A at C must NOT ever return a value because of redundancy.

As a bonus in the future I am looking to have the data update for fluctuation of prices but this is not a priority.

I have attached the sheets in a single file.

Mudsplash,

I am assuming that is not your real name however I have been wrong before!

See if this is getting closer to what you want. Pay attention to the orange tabs (zeros have been filtered out in Power Query versus the Red Tabs where zeros have been suppressed using pivot table Value Filter..both having a different effect if Charts are required).

Your Max request I don't fully understand your contraints so not included yet.

If completely off the mark then draw on a piece of paper what you want, upload a photo of that and I will build it out (fancy not required, basic works just fine).

What you have here is an iphone 4 once/assuming I get feedback from you I will build you out an iphone 10 that will update for any new data with a simple refresh.

Finally I am not on the site much so excuse any tardiness with replys.

https://www.screencast.com/t/a7fVuZpZ7

https://www.screencast.com/t/8CwD6MMBx

This is darn close my friend. The version I have done was a simple "IFS" pulling form the 2 sheets. but this is a step closer to what I am looking for. Let me look over all this and give you a better explained response.
PS man its been way too long since iv taken QA with excel RIP college education. LOL.

Let me draw something for you on my surface. And no worries on the time frame mate. This is for "Star Citizen" so a bit of a hobby for me, and I have taken QA with excel a few years ago so RIP college education lol. I remember working through alot of case studies that used alot of what you have done here. After thinking it over my first post was not detailed enough and poorly explained. I already have part of I was looking for with a simple IFS. But I do want to do a "what if analysis" that will show maximum difference between sell price and purchase price of the commodities by using the constraints of location so that in the future when supply and demand affect prices the most profitable commodity is easily demented and where you would need to buy and sell them to make that profit.