+ Reply to Thread
Results 1 to 28 of 28

Excel tabs linking

  1. #1
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Excel tabs linking

    Hi everyone, not sure if this is the right place to post.

    I would like to create a excelsheet with tab 1 as the master listing,
    with tab 2-3 etc to be used by allocated personnel that which the data keyed in their respective tab will auto consolidate and update into tab 1.

    Is this possible ?

    [Date] [Name] [Descript] [Amount]

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    It is possible. You can do it in 2 ways -

    1. Instant update - as soon as a row is updated in tabs 2, 3, etc, the row gets copied to tab1.

    2. By the click of a button - all data is updated in the tabs and then on a click of a button, the data gets copied to tab1. However in this case, to avoid duplications, tab1 will be completely cleared and all data from tabs 2,3, etc will be copied over.

    Which option do you choose?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    I think i would prefer option.

    However i wouldnt mind trying out option 2 either.


    Scine there is more to learn and test around for find a suitable need.

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    Both will be 2 different approaches and different codes.

    For the 1st option - the data will be updated to the next available row of tab1 and the macro will need to be added to tab2 and tab3's code module.

    For the 2nd option - the existing data in tab1 will be cleared and all the data from tab2, tab3 will be updated from row 2 onwards.

  5. #5
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    I think I'll pick the first option.

    Option 1 can consolidate to an endless list.
    Option 2 is more of like a 1 time(daily control) sort of usage.

    However there's a issue.
    Should tab 2-3 etc remove their row, would that same row disappear from tab 1 ?




    Also on separate thought to due to the above question, is it possible to do a combination of both ?

    Basically tab 1(option2) tab2 (option1) <- With locks with tab3-onwards various users.

    This way, everything keyed into tab3-etc will be autoupdate to tab2,
    and then afterwhich it can be click to be pushed into tab1 as a master archiving record.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    In case of
    Should tab 2-3 etc remove their row, would that same row disappear from tab 1 ?
    option 2 is a better idea. Option 1 cannot handle it.

    Basically tab 1(option2) tab2 (option1) <- With locks with tab3-onwards various users.

    This way, everything keyed into tab3-etc will be autoupdate to tab2,
    and then afterwhich it can be click to be pushed into tab1 as a master archiving record.
    I didnt understand this part. Aren't tab 2 and tab 3 having data of their own, which needs to be fed into tab 1?

  7. #7
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    I can try out option 2.
    Need to poke around with it for awhile.

    One more clarification about it.
    When you say the tab will be cleared out to prevent duplicates,
    does it mean every single entry will be cleared even if same before reupdating?

    Assuming user 1&2 enters 10 rows of data a day, on the tenth day tab one will flush out all 90 entries from day 1-9 before updating 100 entries for day1-10

    On hindsight, the users are able to edit their data which will project a different master data whenever it happens.
    Therefore most likely to make the data secure, the master tab would have to be saved in another spreadsheet.



    Sorry, for the second part I rephrase it.

    Option 1 = tab a
    Option 2 = tab b
    Users = all other tabs.

    The idea is combine both,

    Step one consolidate.
    With user tabs auto updating data to tab a.
    (instant update)

    Step two verify edit & control.
    From tab a the master user then can verify before pushing the data to tab b
    (by the click of a button)

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    When you say the tab will be cleared out to prevent duplicates,
    does it mean every single entry will be cleared even if same before reupdating?

    Assuming user 1&2 enters 10 rows of data a day, on the tenth day tab one will flush out all 90 entries from day 1-9 before updating 100 entries for day1-10
    Yes, this needs to be done to avoid duplicates. The macro will not know which rows have been copied already. If you do not want to delete and re-enter, we can have a column which contains a tick mark or some word / letter to indicate its already been copied. The macro can check if the row contains that indicator, it will not copy if it has the indicator, if it doesnt, it will copy.

  9. #9
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    I think that's a great way!

    How long does it take for one to learn till this stage of Marco?

    Also how long does it take to script the marco in?

    My location does not have much Excel workshops aside for the understand the main concept of what is about.

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    Learning it will take some time but i can code it for you quickly. Should i consider that the last column of each sheet will contain some indicator? What indicator do you choose? I need to put that into the code, so its good if you mention.

  11. #11
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    a tick or something you think look nice would do !
    Its an indicator afterall :3

    [#][date][name][descript][$][indicator]

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    Ok to put a tick mark, you can change the font of that particular column only to webdings. As soon as you put "a" in it (without the quotes), you will get a tick mark. I will give you the code shortly.

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    Which is your last column containing data in your sheets? Are all the sheets having the same number of columns and headers?

  14. #14
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    All the sheets have the same number of columns and headers.
    The last column should be J since i dont have the need to use so many.

    I'll be heading to sleep for now, good nights ^_^
    Last edited by MistralAstral; 05-27-2013 at 10:11 AM.

  15. #15
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    Heya I'm back.

    Also, what do you call this sort of excel skill as ?
    vba/macro/programming ?

  16. #16
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    All mean the same - vba, macros, programming. I will give you the code shortly.

  17. #17
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    Thank you alot !

    that aside,
    if you were me, would there had been a better way/format to utilize or employ ?

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    Sorry...been busy. Will work something out today.

  19. #19
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    So just to confirm what you require -

    You will have multiple users' tabs which will need to instantly feed data into tab 1 right?

    Then all the data in tab 2 will be cleared and on the click of a button, the data from tab 1 will be copied to tab 2?

    How many user tabs will be there? Cant we have the data copied from the user tabs to tab 1 by the click of a button? If you have multiple user tabs, it is a better approach. Otherwise, you need to copy the code to each user tab code window.

  20. #20
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    Yes, what you said is correct about how this excel should work

    as for the users, hmm, i think tentatively put 5 users ?
    Is it hard to copy and edit the marco for additional users ?
    If not i can always try to copy paste =/


    Thanks alot !

  21. #21
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    Can we have a button copy for the 5 users? That is the macro goes thru each sheet and copies the data?

  22. #22
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    Im not sure whats a button copy, but as long it works :3

  23. #23
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    I meant instead of doing an instant copy - which is copy the row to the other sheet as soon as it is filled in the source sheet, we can do a command button click-copy, which is click the command button to trigger the copy. So in this way, any number of rows can be copied v/s the former where only 1 row can be copied at a time and you have to copy the macro to each sheet. If you insert a new sheet and forget to copy the macro in its code window, that data will be missed out.

  24. #24
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    That is a good idea !

    It's idiot proof for people like me :3

  25. #25
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    One last question -

    It would need to be 2 independent macros right? One to copy from the users' sheets to tab1 and then another from tab1 to tab2?

  26. #26
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    Yes that is correct.

    i cant wait to try out this excel marco !
    what started off as rough idea became quite complete now

  27. #27
    Registered User
    Join Date
    05-26-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Excel tabs linking

    heya,

    how's the process going ?

  28. #28
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel tabs linking

    Sorry for the delay.

    Given below are the codes - I have named the sheets as Tab1 and Tab2. You can change them as required.

    The 1st code will clear the contents of Tab1 and populate data from the individual sheets. The 2nd code will clear the contents of Tab2 and populate the data from Tab1 to Tab2.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

+ 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