You might want to check the file you downloaded. I don't see a column W and I don't see any formulas. Maybe some of what you are saying will make more sense when I see some formulas. However, at the moment I'm only vaguely getting the requirement.
My question/problem is that I want the number from gallons sold to use in formula on PPG-Premium without manually looking up row/column, current formula =F164 * .1874. New formula to (=1589.98 * .1874)
Can you explain this part a bit more?

However, looking at the data, this is an application that is just screaming to be done in a database.