Set up a table like this on Sheet2 (BS in A1, 80 in B4)

BS 130
Rock 40
Lime 205
Concrete 80


=IF(F2="Recycle",+((H2*7*0.25)+5),If(iserror(Vlook up(F2,Sheet2!$A$1:$B$4,2,False)),"???",Vlookup(F2, Sheet2!$A$1:$B$4,2,False))



In code you would do

if range("F2") = "Recycle" then
Range("G2") = Range("H2").Value * 7 * 0.25 + 5
else
res = Vlookup(range("F2"),Worksheets("Sheet2").Range("A1 :B4"),2,False)
if iserror(res) then
Range("G2") = "???"
else
Range("G2") = res
end if
end if

Or were you looking for a UDF?

--
Regards,
Tom Ogilvy



"westexp" wrote:


I've known for a long time there must be a better way, but just haven't
had the time to learn vba. I need to add a column "G" that calculates
a rate based on column "F". What I currently have in column "G":

=IF(F2="Recycle",+((H2*7*0.25)+5),IF(F2="BS",130,I F(F2="Rock",40,IF(F2="Lime",205,IF(F2="Concrete",8 0,IF(F2="Sand",30,"???"))))))


In searching the forum, I think I need to create a rate sheet and
accomplish this through vlookup, then loop. Am I on the right track?
Any help is much appreciated. Thanks so much, Laurie


--
westexp
------------------------------------------------------------------------
westexp's Profile: http://www.excelforum.com/member.php...o&userid=33831
View this thread: http://www.excelforum.com/showthread...hreadid=536084