The attached (desensitized) worksheet is a copy of a section of one I have been building for the last few weeks which allows our sales director to ensure that our resellers are pricing our products correctly and keeping us strong against our competitors, also listed on the same reseller's website.
The price in column H is the end user price shown on the reseller's website - deducted from that is a 12% margin (column I) to allow for the end user markup of the reseller after buying from our distributor.
The figure in column I is then converted to Euros (column K)
At the moment there is a single formula, from an exchange rate of 1.373 Euros to the Pound, in column K - this would be fine if the exchange rate stayed the same, but of course it doesn't, and at the moment, I cannot sort data on this sheet because as soon as I do, the formula in column J no longer works and I end up with a whole column of 0.00s.
So what I would like to do is either:
(a) have a formula in column J that accepts any exchange rate, and then shows the correct sum in column K
(b) enter the price shown on the reseller website in to column H, and from that get the final sum in one more column with the 12% margin deducted and the conversion from GBP to EUR (not sure if this would be a VLOOKUP function or macro?)
I would greatly appreciate any help