+ Reply to Thread
Results 1 to 7 of 7

Two way transfer of data between sheets.

  1. #1
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    121

    Two way transfer of data between sheets.

    Have a difficult one but hopefully you guys can help. We have a large spreadsheet. One tab called database has 600 rows and 82 columns. As you can imagine this is a little unwieldly. We have various pivot table on tabs to pull the data but when entering data we have to scroll and scroll to update a cell.

    What I was hoping for what a way to have a 2 way transfer. So for example, tab 2 pulls (in a pivot style) only some selected information, if I wanted to update the information instead of going to the database tab, scrolling and entering the data, instead I could go into tab 2 and change a cell there.

    In other words, changed a cell in database changes what is shown in the tab and changing in the tab changes the database. Can something like this be done? I just worry our database tab is getting to big for the “older” members of staff to deal with. If I add a new row with data on the database tab all the other tabs will add that row, this functionality is essential.

    I have looked into custom views, but frustratingly that required NO tables and our database is one big table with conditional formatting, data validation etc. I ideally don't want to use VBA or formulas but if needs be I can.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016
    Posts
    4,821

    Re: Two way transfer of data between sheets.

    Quote Originally Posted by X82 View Post
    I ideally don't want to use VBA or formulas but if needs be I can.
    So what would you like to use?!

    It sounds like you will have to use VBA, if you have to keep the workbook structure as it is.





    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    121

    Re: Two way transfer of data between sheets.

    Attached is some mock data.

    To summarise, first tab is data, much smaller than ours of course.

    The other 2 tabs are pivot tables showing quick snapshots of certain data for use in emails, prints or presentations.
    What I would like is the ability to click on a cell on the pivot table and type and changes its value. So from true to false or example.
    This change would be reflected on the data page immediately and of course any changes in database would go over to the tabs.
    Attached Files Attached Files

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016
    Posts
    4,821

    Re: Two way transfer of data between sheets.

    Put this in the ThisWorkbook module:
    Please Login or Register  to view this content.
    Put this code in a standard module:
    Please Login or Register  to view this content.
    Now when you double click a row in your pivot tables, the boolean value of the corresponding data row will be reversed, and the pivot updated.

  5. #5
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    121

    Re: Two way transfer of data between sheets.

    Thanks. Just tested and I get this when double clicking:

    "Compile error:
    Invalid Outside Procedure"

    Highlighting this liine:

    On Error GoTo Terminate

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016
    Posts
    4,821

    Re: Two way transfer of data between sheets.

    Whoops! There was a rather essential first line missed out when I copied and pasted the function code! My mistake. It should be:
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    08-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    121

    Re: Two way transfer of data between sheets.

    Thanks.
    It seems to be misbehaving however.
    If I double click the pivot cell, I can edit it. Once I have nothing on the other master sheet changes.
    Also, double clicking any cell (master sheet or pivot) no longer allows me to edit after I have doubled clicked it that first time.

+ 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