+ Reply to Thread
Results 1 to 6 of 6

Formula for changing lines from data entry form to a seperate tab

  1. #1
    Registered User
    Join Date
    06-29-2014
    Location
    Olathe, Kansas
    MS-Off Ver
    2007
    Posts
    3

    Formula for changing lines from data entry form to a seperate tab

    I can not get my brain wrapped around this one. Any help would be appreciated. I am wanting to have a data entry page that changes according to the week number imputed into it. I want it to take the information I put in for that inputed week and transfer it to another tab for the specific person for the correct week(line). Here is the spreadsheet. Any suggestions or help would be appreciated.
    TEST.xls.
    Last edited by uh60doc69; 06-29-2014 at 09:05 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help not sure if excel will do this

    Hi and welcome to the forum

    Thanks for the title change

    Could you included some dummy data in your WB and showed what results you expect
    Last edited by FDibbins; 06-29-2014 at 10:55 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-29-2014
    Location
    Olathe, Kansas
    MS-Off Ver
    2007
    Posts
    3

    Talking Re: Help not sure if excel will do this

    TEST.xls
    Here is the WB with dummy information in it. What I am having issues with is when I change the week getting it to correspond with the individuals tab on the correct week, then the next week changing the week and again corresponding with the correct week. Thanks

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for changing lines from data entry form to a seperate tab

    Thanks

    Try this in D7, copied down and across...
    =IF(INDEX(INDIRECT("'"&$C7&"'!B5:H14"),MATCH('Data Entry'!$C$3,INDIRECT("'"&$C7&"'!A5:A14"),0),COLUMN(A1))=0,"",INDEX(INDIRECT("'"&$C7&"'!B5:H14"),MATCH('Data Entry'!$C$3,INDIRECT("'"&$C7&"'!A5:A14"),0),COLUMN(A1)))

    Note that the index amd match ranges are hard-coded so you will need to adjust them to suite your real data
    Also, by using INDIRECT() you dont need to enter the sheet name, it picks it up from column C

  5. #5
    Registered User
    Join Date
    06-29-2014
    Location
    Olathe, Kansas
    MS-Off Ver
    2007
    Posts
    3

    Re: Formula for changing lines from data entry form to a seperate tab

    Wow. that IF= is amazing. You have already taught me a lot. It appears I still have much to learn. I feel not worthy. I've been a manager for 18 years and have never seen such a formula. Thank you LOL. It works amazingly... but its backwards. I wasn't clear, my apologies.
    I wish to input the weekly information on the Data Entry Tab. (I wish to do this each week) I am putting in the week and then the information and would like it to move that data to the Tabs with the corresponding name and the corresponding week the Data goes to. Wow I do know that I will work with INDIRECT() more. Thank you.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for changing lines from data entry form to a seperate tab

    oh OK, so your data table is on Data Entry? From the looks/sounds of it, that table contents will change each week? If that is teh case, you wont be able to use a regular formula - the "previous week's" data will disapear in the name tabs

    If this is the case, I can think of 2 possible suggestions...
    1. Use VBA to copy the data over with each week-change
    2. Modify how you do your input. Use your name sheets to do the input and then use your 1st sheet (data entry) as the summary sheet. I was going to suggest that you modify your Data Entry sheet, but I think that will turn out to be too involved

+ 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