+ Reply to Thread
Results 1 to 9 of 9

automaticaly copy rows into other tab if cell is equal to

  1. #1
    Registered User
    Join Date
    10-19-2009
    Location
    Antwerp
    MS-Off Ver
    Excel 2007
    Posts
    5

    Question automaticaly copy rows into other tab if cell is equal to

    Hello all
    I'm looking for a solution for my worksheet
    i've got a main tab in which appointments are stored they cover about 46 columns (1 row divided in 46 columns = appointment) these rows(apointments) need to be divided amongst other tabs in the same document.
    These tabs are the four teams to which the appointments belong.
    One of the 46 colums is team ( to which team the appointment should belong) and i want the row copied to the specific tab if the cell is equal to team 1 - team 2 - team3 - team4 and the rows copies itself automatically to the required (team)tab
    So each team just has to look at their tab and not scroll through the complete sheet to find their appointments.
    Each day new appointments(rows) are added to the main sheet and after manual input of the team names they should be placed in the specific tab underneath the existing rows.
    Each team in their turn needs to fill in feedback in their tab - this feedback( a cell ) should be copied unto the main sheet aswell so the secretary has a complete overview and not run through all the tabs.
    See the attachment for more info
    We're talking about column AG for team and from row 6 down
    example:AG6 = team2 then the complete row 6 needs to be copied to tab team2 and so on for the other teams
    second need: when information is filled in in team3 AR6 this info needs to be copied autmatically to main AR6 This applies for all info filled in that column not just the cell

    hope you get the idea and thx for the help
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: automaticaly copy rows into other tab if cell is equal to

    Which cells in the main sheet determine which team the row belongs to?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    10-19-2009
    Location
    Antwerp
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: automaticaly copy rows into other tab if cell is equal to

    None - team is decided "manually"

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: automaticaly copy rows into other tab if cell is equal to

    So how do you expect the particular rows to be copied over?

  5. #5
    Registered User
    Join Date
    10-19-2009
    Location
    Antwerp
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: automaticaly copy rows into other tab if cell is equal to

    As soon as the team name is manually filled in in the team column
    example row 7 belongs to team3 - then the secretary manually writes team3 in AG7 as soon as this is done row 7 needs to be copied to the sheet team 3
    same principle applies for the other teams.
    is there a way to say if cells in column AG are equal to team3 then copy row to sheet team3 - cell in AG = team1 = copy row to sheet team1 and so on for the rest of the teams

    thx for the quick replies by the way

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: automaticaly copy rows into other tab if cell is equal to

    Well, that is what I was asking? So we will look at column AG...

    ... please hold.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: automaticaly copy rows into other tab if cell is equal to

    I have attached a sample using formulas...

    However, depending on how much data you have, this may not be most efficient and can take some time to recalculate.

    I added a helper column in the main sheet in AU6, with formula:

    =AG6&"_"&COUNTIF(AG$6:AG6,AG6)

    copied down. This counts how many of each team there are.

    Then in each of the Team Sheets, I added the teamnames to cell A1 and a count of that team in AU1

    Then in A5 of the team sheets I added formula:

    =IF(ROWS($A$5:$A5)>$AU$1,"",INDEX(Flanders!A:A,MATCH($A$1&"_"&ROWS($A$5:$A5),Flanders!$AU:$AU,0)))

    and copied down to row 30 and across all column...

    This took a few seconds to calculate.. it could be more when more data is added...

    ... probably a VBA solution might work best... if so, post in Programming forum and reference this thread...Attachment 55782
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-19-2009
    Location
    Antwerp
    MS-Off Ver
    Excel 2007
    Posts
    5

    Smile Re: automaticaly copy rows into other tab if cell is equal to

    thx for the effort you put in, those are some complicated formulas i'll run a time trial on the original sheet and see how that works ill still post it in the programming section but you are my hero ( for the day)

    thx for all the help the demo works great i'll keep you posted if anything changes

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: automaticaly copy rows into other tab if cell is equal to

    One way to reduce the timing a little may be to use defined ranges instead of whole column references...

    e.g

    Instead of:

    =IF(ROWS($A$5:$A5)>$AU$1,"",INDEX(Flanders!A:A,MATCH($A$1&"_"&ROWS($A$5:$A5),Flanders!$AU:$AU,0)))

    something like this:

    =IF(ROWS($A$5:$A5)>$AU$1,"",INDEX(Flanders!A$1:A$1000,MATCH($A$1&"_"&ROWS($A$5:$A5),Flanders!$AU$1:$AU$1000,0)))

    which assumes you will have at most 1000 records in the main sheet... adjust if you have to..

+ 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