Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Customer Sheets and Customer List In Same Spreadsheet

    I'm new to Excel 2007 so I need the 2nd grade explanation

    I have 20 customers.

    This is what I have:

    I have a sheet for each customer with contact and demographic information in a vertical format, 1 customer per sheet, sheet name will be customers last name, a-z

    This is what I want to do:

    On the 21 sheet I want a detail list various fields, name address, telephone, email, etc from all 20 customers in a horizontal format, 1 customer per line, all customers per last detail list sheet

    When changes are made to the individual customer sheets they are reflected in the last detail list sheet

    When changes are made to the detail list sheet they are reflected in the individual customer sheets

    I need to be able to insert the 21st, 22nd customer alphabetically and have everything move to the right

    Can someone provide me with a sample .xls spreadsheet or point me to tutorial that that demonstrates the above requirements?

    Thanks, John

    PS: What is the correct terminology for what I'm trying to accomplish?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Support4John View Post
    When changes are made to the individual customer sheets they are reflected in the last detail list sheet

    When changes are made to the detail list sheet they are reflected in the individual customer sheets

    PS: What is the correct terminology for what I'm trying to accomplish?
    John,

    The technical term would be IMPOSSIBLE! At least w/o considerable VBA programming. If you only wanted to update in one direction, e.g. Detail to Summary that could be done with formulas. Also you need to put your summary sheet as sheet No. 1 (far left) then you don't have to worry about it moving when you add a new sheet (again this could be overcome with VBA programming).

    Some sample data would be very useful in further guidance. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is not my design/approach I inherited this from my club secretary

    The real file has about 200 records

    one direction, e.g. Detail to Summary that could be done with formulas would be great

    .
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Before we proceed I have to ask..do you want to fix this thing?
    This is really a Membership Database and as such should be done in Access (or other database program) preferably, or at least if you are using Excel why even bother with the detail sheets?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I understand it is an abortion

    Can this be imported into a new Excel sheet, so I can have one row/record per member so he can stille work on it in a list format and I will work on bringing the excel file into a Access mdb?

    Not to worry about the field descriptions

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Yes, it can be done with a little VBA. Give me some time and I'll get on it.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Ok, here's my solution.

    The attached workbook contains the macro and the summary sheet necessary to get this done.
    You need to copy the summary sheet to your a COPY of your production workbook (always work with a copy).
    Then you need to copy the VBA to your workbook copy. I often find the easiest way to do this is to Export the module from my workbook using the VBE then close my workbook and open your copy and import it. It can be done with both workbooks open but it's easy to get confused with two workbooks open. You don't need to worry about the data already in the Summary workbook as the code will overwrite it since you'll have more data that the test workbook but you can delete the data if you wish but DON'T delete row 1!

    After you run the macro on your copy you can then delete all the detail sheets if you want just the Summary in that workbook. You can also delete the Macro (VBA Code) and resave it as a .xlsx type if desired.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wow, thanks for this, worked great, just had to change the following:

    Cells(lCurRow, 3).Value = shtCur.[K3].Value 'FirstName

    I like the way you parsed the csz, normalizing to lowest level

    After further review and working with a secretary who doesn't want to give up Excel

    How would I create a form similar to the member tab, so when I click a member in the Summary page the form would open with
    that members information displayed?

    I have the form button in the tool bar

    When I click on a member Agnello and form button the form displays the first member Adams

    What kind of look-up vba code can I use to click on member Agnello and have member Agnello display in the form?

    I goggled Excel form with row look-up without success

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    You just need a little code in the form_open event. If you can post another cut down version of your workbook I'll be glad to take a look.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you get sample excel spreadshhet from your solution above?

    Attached Files 2013-09-10 11:07

    File Type: xlsm support4john LYC_Member_Data_Summary-1_MS_20130909_Test.xlsm (33.9 KB, 2 views)

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Try again the file didn't get uploaded.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here you go

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    That's the file I sent you. I need the one you've added the form to.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    As a matter of interest, in the file in post#12:
    1. switch to the sheet [Summary]
    2. put the cellpointer anywhere within the data records, e.g. click on cell [A1]
    3. then, in the top-panel ribbon, select Data then, third item in list, Form

    This will show the data records in 'Form view'.
    With this view, you can click the [Criteria] button, which will show an 'empty Form', and you can then enter a search value (or combination of values etc) and then click the [Find Next] button etc etc.

    Just thought you might like to know that this Form view is there already.

    zeddy

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks zeddy, that was the form I was talking about initially

    RG, Sorry, I was clicking the Form (Standard/Default) button zeddy is refrencing which list all the fields, do I understand correctly that this Form can’t be coded to opened to a specific row? It only does what is available when the form is opened?

    If that is the case, looks like I’ll have to create a custom User Form

    If I create a custom user form, is there Excel a form wizard (like Access) that will create all the field names and labels on the form automatically?

Page 1 of 2 12 LastLast

Posting Permissions

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