Please help, for the 3rd time - I'm drowning!
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Please help, for the 3rd time - I'm drowning!

  1. #1
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I posted this twice on the old board and didn't get any response. I really need help with this. Thanx in advance.

    I have a summary workbook that is linked to twenty departmental budget workbooks It combines all their info into a company-wide consolidation. All the individual workbooks are password protected with different passwords

    The problem is that every time I open the summary w/b, I have to manually input all twenty passwords, one after the other. If I just click "o.k." or "cancel" in the password boxes, the workbook opens, but any change in data in the departmental w/b's is not refreshed on the summary w/b.

    I tried writing a macro to open the w/b and input the passwords, but that part of the macro pertaining to passwords was not recorded.

    Can anyone help with an opening macro? Thanx in advance.




  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-26 12:10, Barry Katcher wrote:
    I posted this twice on the old board and didn't get any response. I really need help with this. Thanx in advance.

    I have a summary workbook that is linked to twenty departmental budget workbooks It combines all their info into a company-wide consolidation. All the individual workbooks are password protected with different passwords

    The problem is that every time I open the summary w/b, I have to manually input all twenty passwords, one after the other. If I just click "o.k." or "cancel" in the password boxes, the workbook opens, but any change in data in the departmental w/b's is not refreshed on the summary w/b.

    I tried writing a macro to open the w/b and input the passwords, but that part of the macro pertaining to passwords was not recorded.

    Can anyone help with an opening macro? Thanx in advance.
    Try this:

    Sub OpenPassword()
    ' Password protect the module you put this code in.
    Workbooks.Open Filename:="C:file1.xls", Password:="mypassword1"
    Workbooks.Open Filename:="C:file2.xls", Password:="mypassword2"
    ' And so on

    End Sub


    Word of advice though, password protect the project you put this into otherwise other people could look at the code and get the passwords.

    Regards,

    Gary Hewitt-Long

  3. #3
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Gary

  4. #4
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-26 12:39, Barry Katcher wrote:
    Thanks, Gary

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Posts
    207
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, Barry. I also need to do the same kind of consolidation but the different workbooks are not protected. How did you performed the consolidation? Basically the columns on each workbook are: dept, project, module, cost catagory, Q1, Q2, Q3, Q4, Total. Each workbook is a different department. They are all identical in their layouts. Do I need to click on each cell in the summary workbook to set the Data/Consolidation and map to each of the corresponding cell of the various workbooks? Thanks much.

  6. #6
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Simonf
    Assuming, as you say, all the spreadsheets, including the consolidation sheet, are in the exact same format (same # of rows & columns and same data in each corresponding cell), then you just have to enter one long formula, in the consolidation sheet, in the first cell that contains data. Assume that to be cell A1.

    =[Book1]Sheet1!$A$1+[Book2]Sheet1!$A$1+etc., etc., etc.

    You can enter this manually in the cell or you can open the workbooks and point to the cell in each.

    After you have this one cell, just copy down and then to the right.
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  7. #7
    Board Regular
    Join Date
    Jun 2002
    Posts
    207
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, actually just the column headings are the same. The data are different.

  8. #8
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Sorry, I was unclear. When I said "the same data..." what I meant was "the same type of data...". If, for example, cell F20 in worksheet 1 (or workbook 1) has column heading of "Annual Budget", and row heading of "Supplies", do these headings apply to all of your worksheets (books) for each of their cells F20? If so, the sheets (books) are identical and the formula can be used. Of course, the actual numerical data for each sheet would be different.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com