Results 1 to 6 of 6
  1. #1
    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: Row vs Column Forumulas (WinXP and Excel 2003)

    I would approach the situation with only 2 sheets:
    1) the "offering" sheet with the table of info to fill in
    2) an "Individual" sheet.

    The individual sheet could be designed to read ANY of the people (with a pulldown to select) so you could view/print any at any time. You could also create a macro to print all the sheets (it would be a simple loop)

    This has the advantage that you only have 1 individual sheet, if you ever want/need to modify the look, you can do it in one place.

    You mention that you are "somewhat new" to excel and I have not been very specific or detailed.

    If you want, I will try to work up an example for you of what I mean, and I can answer any questions you have after you have a chance to examine it.

    Steve

  2. #2
    New Lounger
    Join Date
    Jan 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row vs Column Forumulas (WinXP and Excel 2003)

    That would be great. I learn best by example, and then I'll know more next time I need to do something. The only thing I really don't want changed is the format of the individual sheet (at least not cosmetic changes). Since each individual gets them I want them to look good. I appreciate your help and will be glad to learn something new. If you do work something up, I'd like just a little bit of help on how to do it myself. Your example will be great but if I don't know how to do it from scratch I will not have learned all I can. Let me know if you need me to do anything else at this point, otherwise I'll wait for your information.

  3. #3
    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: Row vs Column Forumulas (WinXP and Excel 2003)

    How about this.

    I have some changes to the "individual sheet", but I have "hidden" them (for example Cols C, G, K, O have numbers in them indicating the row to lookup, and some numbers in row 1). so the look has not changed. Before you do make changes (especially deleting rows/columns) make sure the cells are empty.

    The "Name" box in H2, has a combobox, select a name from the list and that person's data will be displayed. The combobox does not print, but underneath it there is a cell which will get the selected name and that does print. This allows easier formatting of the name. The combobox has limited formatting.

    There is a macro to "Print all" the individual sheets and a button above the year.

    In the offering sheet, I moved the totals to the top (above the headers). This way they are always visible and you can add new names without having to move them to keep them at the bottom. I changed the formulas to have them "expand" as new data is added.

    You can add/insert/delete names from the list in offering. It is essential, however, that there be no blank names within the list. The various datalists from this are based on dynamic names (insert - names - define to look at the names and assignmet) that count the names and blanks would screw it up.

    If you change the year, your dates will all change to Sundays of the year typed in. The first sunday is calculated from the year, all the others are just calculated as 7 days past the last one.

    After you look and "play" with it to see how it works, post back if you need any questions or you need help with "tweaks"

    Steve

  4. #4
    New Lounger
    Join Date
    Jan 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Row vs Column Formulas (WinXP and Excel 2003)

    I'm somewhat new to Excel, so perhaps part of my problem is not knowing what to search on to solve this problem. I have included a file which shows a relationship between a couple of worksheets. On the first worksheet 'Offering' I populate with dollar amounts that people pledge and it totals them properly. The goal is to automatically populate the other corresponding worksheets which also works well. My problem is the RowColumn formulas are hard coded in each of the 'PersonX' worksheets. If a new person were to join I would add their name on the "Offering" worksheet, then copy the previous person's worksheet with a cut and paste. This is also shown in my file. The problem is (in this example) Person 3 (the original) is identical to Person 4. If you look at the Offering worksheet Person 4 is on line 6. This means in every cell of the worksheet Person 4 I have to manually change the formula to reference row 6 of the offering worksheet. Is there a method to more generically generate this information. Or put another way, can I dynamically define row/column references. I'm open to just about any idea except changing the layout of the individual worksheets. I believe part of the problem is I'm trying to take horizontal data in one worksheet and correlate it to the vertical axis in other worksheets.

    - Tim

  5. #5
    New Lounger
    Join Date
    Jan 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row vs Column Forumulas (WinXP and Excel 2003)

    Thanks for the example. It looks to be exactly what I need. I do have a few questions, mainly along the lines of how to integrate/implement this into my own work. I've never done macros and I'm sure there's more to your example than what I've uncovered so far. What I sent you is a generic templete without actual names and contributions amounts. It was very similar to the actual spreadsheet I use however. I wonder if you would be willing to provide an email address and I can ask some questions directly related to this example offline? Also I'm getting ready to go out of town and won't return until 1/25 so I can't do anything until the end of the month. Thanks again for your help.

    - Tim

  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: Row vs Column Forumulas (WinXP and Excel 2003)

    Please post all your questions here on these boards.

    The only macro is jut to loop thru the names and print all the sheets. The rest is done with formulas.

    To make it a working program just fill in the names and the amounts in the offering sheet and it should work. As you add more names, they will be added to the names list.

    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
  •