Posts by T. Louk

    I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:


    =IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))



    I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using. I'd appreciate any advise provided.
    Thank you,
    Terry

    I have a masterlist in Excel with macro's to pull information from 20 other lists which are identical in format to the master but will contain different information on each (20 different users inputting on diff lines). The lists will have unique names such as P1, P2, etc.


    It is working great. However the only issue I have is that sometimes a user may not have anything to enter so I might be missing a sheet. Is there a way to adjust the macros so that it can skip over a record if it's not present or must I have a "dummy list" opened for any which are missing? Below is a copy of the macro that I'm using if that helps. This is repeated for P1 up through P20.


    Thanks for advance to any help you can give this brain-weary woman!
    Terry


    [vba] 'Sheets("Instructions").Select
    'Range("C9").Select

    'TotRows = ActiveCell.Value
    'TotRows = TotRows + 1
    Sheets("Prop Base").Select
    Range("Q2").Select

    Windows("P1.xls").Activate
    Range("Q2").Select
    'Rows = 2
    Do Until ActiveCell.Value = ""
    If ActiveCell.Value = "y" Then
    Selection.Copy
    Windows("MasterList.xls").Activate
    ActiveCell.Select
    Selection.Paste
    End If
    Windows("MasterList.xls").Activate
    ActiveCell.Offset(1, 0).Range("A1").Select
    Windows("P1.xls").Activate
    ActiveCell.Offset(1, 0).Range("A1").Select
    Loop

    'Windows("P1.xls").Activate
    'Range("Q2").Select
    'Selection.Copy
    'Windows("MasterList.xls").Activate
    'Range("Q2").Select
    'Windows("MasterList.xls").Activate
    End Sub[/vba]

    Re: Shared Workbooks


    Two users would never be updating the same line at any time. Each user will have the lines they need to address identified within the spreadsheet. It's just that the information is received all in one listing and we're trying to have all of them access it at once. Agreed, it has been a receipe for disaster.


    Someone else suggested having users download the sheet, make their additions and save it as a separate file (with unique names) and then using macro's to pull that information back onto the original sheet. I've done that and it will serve the purpose. It's just a bit slower than expected, but it may be the best we can get.


    Thank you for your help and quick response.
    Terry

    Re: Combining Worksheets


    Rich,
    Just wanted to thank you again. I tried your suggestion and it works great. That's exactly what I needed. Thank you once again so much.
    Terry

    Re: Combining Worksheets


    Thanks Rich. Yes, I gave up on the multi-user function. As several of you advised, that was a receipe for disaster. I'm still ducking the darts that are being thown my way for that attempt.


    I will try the functions you've indicated. I really appreciate the help. It might have been clearer if I'd encluded some sample copies of the spreadsheets. If I can't get this to work, I'll try that.
    Thanks again. I really appreciate the help.
    Terry

    I have a project which will have approximately 20 users preparing worksheets which will contain the same format but which will have information stored on separate lines amongst the 20 worksheets. I need to take these 20 worksheets and merge the information that is entered into one Main worksheet formatted in the same manner.


    First question: Can I create a macro to open these 20 individual spreadsheets (if they even have to be opened to copy).


    Second: How would I write a formula to pull the information from each filled cell and merge it onto the matching cell on the main spreadsheet.


    For example:


    The Main Spreadsheet will have 10 columns and 1000 lines. Columns A through E will be prefilled with policy information.


    Individual users will open a copy of this Main Spreadsheet, fill in Columns F through J for the policies that affect them, and then saving the Main Spreadsheet with a unique name for them (ie; User 1, User 2, etc).


    At the end of the day I need to take the 20 documents from various users and combine their information onto the Main Spreadsheet.


    I'd appreciate any help anyone can provide for a quick way to handle this process.
    Thanks,
    Terry

    Re: Allow multiple users simultaneously


    Thank you all for your advise. I'm going to re-think this process and see if I can't find a better way. I've tried the sharing route and as Aaron says, I'm ending up with a lot of irate users and loss data. Thanks again for the quick responses.
    Terry

    Re: Allow multiple users simultaneously


    Thank you Aaron. I've been working within Excel all morning trying to make this "multiple user" think work and once I get beyond 4 users everything breaks down. I'll check into what you advise.
    Thanks again.

    Is there a way to have one Excel spreadsheet on a server and allow multiple users to access that spreadsheet and make updates on the spreadsheet at the same time? I've tried a shared workbook but the users are getting errors that it's already opened by another user and they can only do "read only". Or it tells them they can use "Notify" to let them know when the other user is finished. However, if they chose notify, it's kicking the other user out and not saving the information they input. I'd appreciate any ideas on how to handle this situation.
    Thanks,
    Terry

    Is there a limit on the number of users who can access and work within an Excel shared workbook at a given time? I've created a spreadsheet with some simple formulas applied to five of the columns (ie: If one field = X, display XX in another Field). There are 25 columns used with approximately 1000 rows. I first unprotected the spreadsheet, left five columns open so users could enter data, then protected the spreadsheet and the workbook.


    The problem is that we have approximately 50-70 users who could possibly hit this spreadsheet during the day. We're finding a lot of locking errors occurring and data is being lost or users are not able to save. Many times it will advise them to save under another name.


    Is the problem too many users, protecting the workbook or trying to save formulas inside the spreadsheet on this shared file?


    I'd appreicate any help that can be given quickly. Thank you.
    Terry

    Re: Counting multiple codes as one count




    That did it! Thank you so much!!

    Looks like your suffix always starts in the fifth position. If so, then for example if xxx-5RM is in A1, put the following formula in A2.
    =MID(A1,5,10)


    This will bring back 5RM


    By extending it to 10, it will pick up anything from position 5 to 10 from cell 1. Just copy formula down.
    Good luck,
    Terry :)

    Hi Roy,
    Thanks so much for taking the time to find that information. For this particular project, I think I'll end up using an If statement on the actual letters to pull in the picture signature. However, the coding you provided will be very helpful for a few other projects I have in the works. So this was still very beneficial.
    Thanks,
    Terry

    I'm working with a print/merge program where I have an excel spreadsheet with a list of names and electronic signatures and I'm trying to use a lookup function to extract the signature for the chosen name.


    First I should explain that these signatures are actually scanned and then put into a word document. I cropped them and put them into the Excel spreadsheet.


    I have a list of the names in C2 thru C40 and the corresponding pasted signatures in D2 thru D40. The user types in the requested name in B2.


    Therefore I used the following Lookup
    =VLOOKUP(B2,$C$2:$D$40,2,FALSE)


    However, instead of bringing back the electronic signature, it brings back a 0. Is there something I can do to correct this?


    I appreciate your time and anticiapted help. Thanks, Terry