How To Require A Formula Be Entered in a Column?

Aqua1967

New Member
Joined
Apr 2, 2013
Messages
12
I have a large spreadsheet that tracks sales and converts currencies for global markets. I'd like to require that entry in a particular column be set up so that a person must enter a formula in that column. I receive reports from a couple dozen people and I combine them into one master spreadsheet.

Here is a specific example of what I'd like to see entered:

(To take a part that costs $1.50 and use the Mexican Exchange rate which I have in cell E4)
=1.50*$E$4

or

(To take a part that costs $2.86 and use the Euro Exchange rate which I have in cell F4)
=2.86*$F$4

Is there a way I can require (perhaps through data validation?) that every one who tries to make an entry in this particular column is required to enter data which begins with the equal sign and includes two $ signs? The exchange rates are listed across the top all in one row (row 4) and I don't want the formulas changing when I paste them into the master document so I use the $ signs.

I'm having trouble trying to figure out how to make this work. Can anyone please help me?

Thank you so much!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi

Instead of using specific cell references in the formula, you could create names with the standard abbreviations for the currencies, that everyone probably already knows. For ex., EUR (Euro), GBP (British Pound), MXN (Mexican Peso), USD (United States Dollar) etc.

This would make the formula to enter something like:

=2.86*GBP
=2.86*MXN
=2.86*EUR

which are easy to remember for everyone, and they don't even have to know where you wrote the exchange rates.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top