Create a table with the values in (for example) Sheet2 columns A & B
A B
733 1
738 2
752 3
etc

in D1 put:

=VLOOKUP(D10,Sheet2!$A$1:$B$20,2,0)

Not sure about your copy/paste requirement - what data?

"izzyt1972" wrote:

I am trying to get a formula that will display a number between 1 and 20,
depending on the contents of a cell. for example I have in "D1" the
following formula =if(d10=733,1,if(d10=738,2,if(d10=752,3........... .) etc
etc. It only goes up to 8 and I run into an error. How do I write my
formula in a cell on a "calculations" worksheet? Also, how can I put this in
a Macro, to copy and paste the data elsewhere on a seperate worksheet?

Any help would be really appreciated. I am stuck.