assume the activecell has the current number you want to increment.

as long as they are in order

Dim rng as Range, sNum as String, s as String
With Workbooks("Quote Reference Numbers.xls"). _
Worksheets("Data")
set rng = .Cells(rows.count,1).End(xlup)
if rng.value < activeCell.Value then
msgbox "Problems"
exit sub
End if
End With
snum = format(clng(right(rng.value,4))+1,"0000")
s = left(rng.value,len(rng.value)-4) & s
set rng = rng.offset(1,0)
rng.value = s
ActiveCell.Value = s


If you can't assume they are in order
Dim rng as Range, rng1 as Range, sNum as String
Dim s as String
With Workbooks("Quote Reference Numbers.xls"). _
Worksheets("Data")
set rng = .Cells(rows.count,1).End(xlup)(2)
set rng1 = .Columns(1).Find(activeCell.Value)
if rng1 is nothing then
msgbox "Problems"
exit sub
End if
End With
snum = format(clng(right(rng1.value,4))+1,"0000")
s = left(rng1.value,len(rng1.value)-4) & s
rng.value = s
ActiveCell.Value = s



code is untested and may contain typos.

--
Regards,
Tom Ogilvy


"Theresa" wrote in message
...
Hi:

I have a spreadsheet where I need to have a cell, a quote # (KF-2006-1001)
increment by 1 based on the last used number. I want to store this data
in a
separate file (Quote reference Numbers). How do I have the cell look up
into
my quote reference number file to find the last quote #, then add 1, then
store that value back into the quote reference number file on the next
available row? I also want to write other fields back into the same file.

Thanks....any help will be appreciated.
--
Theresa