Hi



You may have seen my earlier post about invoice numbers. Well I almost have it cracked I just need to copy some data. My layout is similar to this;

A B

1 X Y
2 PK00001
3 PK00002
4 PK00003
5 PK00004
6 PK00005
7 PK00006

So, for example, if X (A1) is 1, then I would like the value from A2 (PK00001) to be displayed in B1.

Likewise if A1 is 2, then PK00002 and so on.

Is there a formula to do this?

Thanks

I suspect this will (or won't) be useful depending on your context, but give this a try:

Cheers,

I'm real curious as to what you're doing... I still don't understand.

Are you placing values in cells or naming workbooks, or both, or what?

Where are the initials... I see PK, but what if the person isn't Phil?

I know it seems cofusing as I did miss out a piece of vital information in my earlier post (sorry about that :-) )

Each sheet is unique to the staff member, they will have their own copy on their lap top (there's only 18 of us so not so bad).

So, basically what I have done is create a data list right at the bottom of the sheet with a similar set up as I showed above.

NExt I addedd a combo box, linked to this list and the number at the top of the lsit will incrememnet the box.

Next I have added a `save sheet as` button to the work sheet which saves the sheet in the following format;

PK00001, EngineerName, Customer, CustomerLocation.xls

This uses the following macro;

Sub savesheets()

Range("a103").Value = Range("a103").Value + 1

ActiveWorkbook.Save

Range("a103").Value = Range("a103").Value - 1

Dim newfile As String, ssno As String, eng As String, cust As String, loc As String

ssno = Range("J2").Value
eng = Range("E6").Value
cust = Range("e4").Value
loc = Range("j4").Value

newfile = ssno & " " & eng & ", " & cust & " " & loc & " Service Report.xls"
ChDir _
"C:\Documents and Settings\KHS\Desktop\Drafts"
ActiveWorkbook.SaveAs Filename:=newfile

End Sub


As you can see the macro first increments the order number by 1, saves the file, then drops it back down again before saving the file in the required format.

I will finally create a macro which will delete all of the other fields in the sheet when it is opened, this will create a blank sheet each time.

It has been a bit higgldy=piggldy as Ive been working on this ruddy thing for about 4 weeks now and the more ideas I google the further the sheet goes......

Thanks, this works great, now time to `put it in` he he!