S
sara
I have moved our PO off paper to Excel (BIG move for our
company). I have the buyer see only one sheet, and s/he
enters all the data in that one sheet. I have created and
hidden the 4 other copies that we need, and each gets its
data off the buyer's sheet. I have formatted each sheet
as it is needed (removed fields, etc) for the particular
user.
I created a Macro to unhide and print all the sheets, and
rehide them. It is run from a button on the buyer's
sheet. All sheets are protected, as there are some
complicated formulas on the buyer's and the others are
print-only.
My problem is getting a unique PO Number. I have about 25
buyers in 8-10 locations. They are NOT all connected to a
single server; many share a PC. I tried to use LEFT and
RIGHT and use part of the serial date and time (and I
thought of Julian date), along with a VLOOOKUP to get a
number for the specific merchant. I needed the time in
case the same merchant does more than one PO in a day,
which happens somewhat frequently. Then I PasteSpecial
Value of the date/time combo, added the merchant # to come
up with the PO. I put all this in my Macro, but it won't
work due to protected sheet.
I tried to Unprotect, but it asks for the Password. Is
there another way to guarantee a unique PO Number -
generated when the PO is opened to create or printed (and
then saved permanently in the Excel file?
I ended up creating a separate worksheet just for the PO
Number, and hiding it and protecting it with no passoword,
so the macro can do its thing and get a PO #. I'm just
not sure if I over-complicated the whole thing and there
might be an easier way to get the unique PO #.
Many thanks - I've got a solution, but I'm a little afraid
it's too complicated for anyone else to maintain. I'm the
only IT person (if you call it that) in the company. If
I'm on vacation or leave (?) or something, they'd have to
call a contractor.
Sara
company). I have the buyer see only one sheet, and s/he
enters all the data in that one sheet. I have created and
hidden the 4 other copies that we need, and each gets its
data off the buyer's sheet. I have formatted each sheet
as it is needed (removed fields, etc) for the particular
user.
I created a Macro to unhide and print all the sheets, and
rehide them. It is run from a button on the buyer's
sheet. All sheets are protected, as there are some
complicated formulas on the buyer's and the others are
print-only.
My problem is getting a unique PO Number. I have about 25
buyers in 8-10 locations. They are NOT all connected to a
single server; many share a PC. I tried to use LEFT and
RIGHT and use part of the serial date and time (and I
thought of Julian date), along with a VLOOOKUP to get a
number for the specific merchant. I needed the time in
case the same merchant does more than one PO in a day,
which happens somewhat frequently. Then I PasteSpecial
Value of the date/time combo, added the merchant # to come
up with the PO. I put all this in my Macro, but it won't
work due to protected sheet.
I tried to Unprotect, but it asks for the Password. Is
there another way to guarantee a unique PO Number -
generated when the PO is opened to create or printed (and
then saved permanently in the Excel file?
I ended up creating a separate worksheet just for the PO
Number, and hiding it and protecting it with no passoword,
so the macro can do its thing and get a PO #. I'm just
not sure if I over-complicated the whole thing and there
might be an easier way to get the unique PO #.
Many thanks - I've got a solution, but I'm a little afraid
it's too complicated for anyone else to maintain. I'm the
only IT person (if you call it that) in the company. If
I'm on vacation or leave (?) or something, they'd have to
call a contractor.
Sara