Results 1 to 8 of 8

Thread: Open File (XP)

  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open File (XP)

    Is it possible to open a second spreadsheet, if an entry has been made in a cell of the first sheet, using an "if then" formulae? And how! Thanks for any help. <img src=/S/question.gif border=0 alt=question width=15 height=15>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open File (XP)

    No it would require a macro, formulas can not "do things".

    If you are more specific of your goals, we could write some code for you. It could be based on a worksheet_change event.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open File (XP)

    I have two spreadsheets, one is used to enter the number of sales on a given product. The second sheet would be used to enter the customer information pertaining to each customer. ie: Sheet 1 "Number of sales" cell A3 has an entry of 3. Any entry in a cell, would open up Sheet 2 "Customer Data" and allow the user to enter Customer Data. Then return to sheet 1 when finished.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open File (XP)

    Could you give more clarification, please?
    I am hearing:
    if you change ANY cell in worksheet1 of workbook1, you should open workbook 2.

    Do you mean any cell? a cell in col A? a cell in A1:A100? some other range?
    Do you want workbook 2 opened to any particular sheet and any particular cell in that sheet?

    Is the user going to save/close workbook 2 after they are finished adding info? What if they go back to WB1 without closing it, do you still want to open it? or just use the existing one that is open?
    Steve

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open File (XP)

    1 Any cell in a given range in the first workbook, ie: C3 to G15. And/Or C17 to G35 (Actual cells may be different)
    2 Need to have the second work book open to a certain page to allow the user to fill out info. Page based on where the entry is in the first workbook.
    3. Should return to first workbook when done and save both workbooks.
    See attached workbooks. may help to understand.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open File (XP)

    Not sure what you are after completely. I would suggest:
    1 workbook, not 2.
    Combine all the worksheets in the "dataworkbook" into 1 sheet with the additional column of the Descritption (ie the "sheet name"). I see no reason to create multiple identically formatted worksheets.

    Then I see the "workbook1" as just a summary sheet of the other sheet info. If I "understand" your setup, you should be able to do this summary direclty with a pivot table based on the dataworksheet.

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Steve

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open File (XP)

    The rest of the story is.... There are 5 salesmen. Each has his own "worksheet1". Every month a supervisor imports all of these worksheets into a master that has been setup to calculate how much money each salesman has earned for the month, based on the number entered by the salesman, in the grey cells. (this calc is not incorporated in the "worksheet1", but in the master. The supervisor needs to have verifiable customer names etc, to validate payment. I was attempting to set up a master to do the same thing with the "Data Workbook", that has been filled out by the salesman. If there is an easier way to accomplish this, I'm all ears, or in this case, all eyes. <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open File (XP)

    I would still suggest that each to the five salesman have a workbook of only 1 sheet.

    But barring that, why not have a macro in the master workbook that "imports" all the data from each of the salesmans sheets (1 sheet from each as I recommend, ,or it could be done by grabbing all the sheets from each of them) into 1 sheet [it would have the current 4 columns and 2 additional: the "sheet name" and the salesman name (or ID or whatever)]

    This consolidated sheet in the master could be used to use a pivot to summarize or it could be done as part of the code after the import. If I understand correctly the data should reflect the summary, you shuld not have to manually add it.

    Again, my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •