+ Reply to Thread
Results 1 to 11 of 11

Using autofill with data from other spreadsheet

  1. #1
    Registered User
    Join Date
    11-02-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    7

    Using autofill with data from other spreadsheet

    Hello,
    I am trying to replace a "pen & paper" client records system with excel spreadsheets. I have separate spreadsheets for entire groups of people for attendance and payments. I need to create an individual records sheet for each client based on the data from the two spreadsheets. This will eventually be a lot of data so I need to be able to use the "autofill" feature to cause it to fill in formulas that import data for each subsequent date. However, when I try to do this, the formula series seems to repeat itself over and over again.

    For example, I have the dates: 10/2, 10/9, 10/19. using the fill series feature, I need it to fill in information from subsequent cells which would look like 10/26, 11/2, 11/9, etc. (since it is taking the data from a set of cells like F1, G1, H1, it shouldn't matter that the dates are not in a consistent sequence, I just need the cells to be in a consistent sequence).

    What is happening instead, is when I use the fill series feature, the dates are just repeating (10/2, 10/9, 10/19, 10/2, 10/9, 10/19). I could just enter each formula by hand, but since there are so many cells to fill and a large number of clients, this would not be practical at all.

    Also, I have only a basic working knowledge of Excel. If the solution involves programming, I will need to know where to put the code.

    If anyone could help me solve this problem, I would greatly appreciate it! I am including mock copies of the spreadsheets to give a better picture of what I am trying to accomplish.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2003/2007
    Posts
    145

    Re: Using autofill with data from other spreadsheet

    Muzikmatt,

    I think I follow what your trying to do. I have made some changes to your spreadsheets that I hope will answer your question.


    Simeon

  3. #3
    Registered User
    Join Date
    11-02-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Using autofill with data from other spreadsheet

    Simeon,
    Thank you, could you please post the spreadsheet with the changes you made?

  4. #4
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2003/2007
    Posts
    145

    Re: Using autofill with data from other spreadsheet

    Sorry About that. I have been having problems with my uploads...
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-02-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Using autofill with data from other spreadsheet

    Simeon,
    Thank you, I really appreciate your help. This looks like what I need except in reverse. I need the individual attendance sheet to reference the dates from the group attendance sheet.

    Thanks again,
    muzikmatt

  6. #6
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2003/2007
    Posts
    145

    Re: Using autofill with data from other spreadsheet

    I see, sorry about that.

    I have reversed the INDEX formula to that it will populate the "John Doe" Workbook with the dates from the "Attendance" Workbook.
    I hope this is what you were looking for.



    Simeon
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-02-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Using autofill with data from other spreadsheet

    Simeon,

    This really helped me out a lot, thank you. I am in the office today trying to make this work with my client records and I have two problems. The first and biggest problem is that the information on the individual client record sheet is dependant on the week number on the attendance sheet. The problem with this is that once the client completes this class, they must complete two more which will be on two other spreadsheets. Also, one client may complete a class as soon as 14 weeks, while another may take 30. My individual records sheet needs to reflect the total number of weeks attended for all three classes, not how many weeks attended for each class. Is there any way I could populate these cells with data from my attendance sheet that isn't based on the week number?

    The second problem is that when I use autofill, the cells that are blank on the group attendance sheet show up as 00-Jan-00 on the individual client record sheet.

    Thanks again Simeon, you've helped me tremendously,
    Matt

  8. #8
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2003/2007
    Posts
    145

    Re: Using autofill with data from other spreadsheet

    Matt,

    I am a bit confused on the first issue. Would you be able to upload a spreadsheet/s and in those highlight and detail what you want in those cells. This would give me a better idea of what you are looking for.

    As for the 00-Jan-00 if you higlight all of the cells and click on conditional formatting, use cell is equal to 0, then format as white font. This will hide all of those annoying dates.


    Simeon

  9. #9
    Registered User
    Join Date
    11-02-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Using autofill with data from other spreadsheet

    Simeon,

    Here are the spreadsheets and hopefully I did an adequate job explaining my problem. Thank you for the conditional formatting solution by the way.

    Matt
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2003/2007
    Posts
    145

    Re: Using autofill with data from other spreadsheet

    Matt,

    I see what you are talking about now. Unfortunatley the only answer that I have at this time is to input the dates in the corresponding Week # for each person. So if John Doe finished Class 1 on Week 25. Then on Attendance Class 2 the dates for that class need to start in Week 26. I don't think there is anyway to get around the Week # refrence on the individual John Doe.xlsx. Sorry I am just not that good yet. I am going to see if another member of the forum would be able to better solve your issue.


    Simeon

  11. #11
    Registered User
    Join Date
    11-02-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Using autofill with data from other spreadsheet

    Simeon,

    It's okay you've been a great help, thank you so much! Hopefully someone out there will see my post and be able to help me finish this....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1