Who can Help me in

[tokhimi]

if col E show qty will auto fill series in col a ,
if qty in col E shown #na col A will not fill up the series number

ItemPart NumberDescriptionQuantity　ab123aaa#N/A　ab124hhh#N/A1ab125ccc10　ab126ddd#N/A2ab127eee12　ab128fff#N/A3ab129ggg15　ab130hhh#N/A　ab131iiii#N/A4ab132jjjj16　ab133kkkk#N/A

 

[tokhimi]

Hi Derek

Thank you very for your help and guidance

Found already

frist row Col A
=IF(AND(E2>0,ISERROR(FIND("-",E2))), MAX(A$1:A1)+1, "")
scond row col A
=IF(AND(E3>0,ISERROR(FIND("-",E3))), MAX(A2)+1, "")
third row Col A
=IF(AND(E4>0,ISERROR(FIND("-",E4))), MAX(A2:A3)+1, "")

folow to last palce just change MAX ( xxx: xxx )

 

[Derek Brown]

I am not sure where we are with your problem, now.
From your last reply, you seem to have a better 'MAX' formula in A2 but you need to copy this down so that you get MAX(A$1:A2) in cell A3; MAX(A$1:A3) in cell A4;MAX(A$1:A4) in cell A5 etc.

However, I cannot see where the FIND function is going to work as, using your examples, you are probably not going to find the text character "-" in a column which will probably only contain numbers?

To make sure that you are using the VLOOKUP formula correctly I suggest that you go to www.youtube.com and search for "MrExcel VLOOKUP". This will give you links to several Podcasts that you can watch and learn some useful tips. Incidentally, you need to specify the range of the lookup table and not just the columns - you had Data'!A:C but it needs to be more specific (for example Data'!A2:C15).

 

[tokhimi]

Hi Derek

teach me How to upload the excel file then i oost the files and let u see ...

 

[Derek Brown]

Sorry but I have not done that myself as I only try to help with answers and have never needed to ask a question myself.
Have a look at:
http://www.mrexcel.com/forum/showthread.php?t=479775