+ Reply to Thread
Results 1 to 4 of 4

Formula to Update data on two tabs in multiple tables

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Formula to Update data on two tabs in multiple tables

    I was given a spreadsheet created by another employee that contains 3 tabs, a "backup data" tab, a "legend" and then a "4 States" tab of 4 tables of region with corresponding maps to show the specific states. The legend tab contains a column with each state, a column with a SUMIF formula that sums the amount of orders within a given state based on the backup data tab, a count column that counts the number of rows in the backup data for a given state, and then a column called region, where I manually set the region based on the geographic location of the state.

    The 4 states tab contains 4 tables, in each table is a header that corresponds to the region that I have given them in the legend tab, columns for state, orders and count. At present, these are simple cut/paste from the state, order and count columns in the legend tab. They also have maps to show a visual of the states included in each region. The current state of the spreadsheet is that we highlight and move the 3 rows of data from one region to the next, in order to be able to see the maps update, for a visual view of what the breakdown looks like after a move.

    I have been asked to update the formulas on the legend and 4 states tab, to make the region column in the legend tab, update the a state, order, and count data on the 4 states tab is moved from one region to another. I am having a very difficult time figuring out how to do this.

    For example, if I highlighted the data for MO in the Northeast region on the 4 States tab, and dragged it over to the Northwest region, I would like for the Region column on the legend tab, for MO to change from Northeast to Northwest.

    Can anyone provide some guidance on how to make this work?

    *Note: I had to remove the maps so that the doc would be small enough to attach.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Formula to Update data on two tabs in multiple tables

    I made a table of the state on tab data.

    After that a vloopup in Legend!D2 = Vlookup(A2,data!$A$2:$B$52,2,0)

    After that a pivot table.

    If you change the region in the table (tab) data, it also will change in the legend tab.

    Then you only have to refresh the data of the pivot table => data => refreh.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    10-09-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formula to Update data on two tabs in multiple tables

    My boss wants to be able to have the legend tab update when she moves a state to a different region on the states tab. The current state, would require that she just update the legend with the new region once a move is made, and she doesn't want to have to do that. Is there a way to do it without having to manually update the data on another tab?

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Formula to Update data on two tabs in multiple tables

    You can manualy enter the data in the legend tab, like you did.

    But I would not advice that.

    The data on the sheet Oeldere can be on every sheet (can also on sheet Legend).

    But again I would use Vlookup to make the changes.

    And what about the earlier state - region combination. This information don't need to be saved?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pivot tables - Multiple Tabs
    By degross77 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-22-2019, 11:38 PM
  2. Find and update multiple sheet tabs with new data based on range of cell values.
    By robcgp1200 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-20-2017, 04:59 PM
  3. [SOLVED] Automatically Reapply Autofilters on Multiple Tables on Multiple Tabs
    By Oksana in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2016, 12:20 PM
  4. [SOLVED] Use data dump to automatically update tables on other tabs
    By IronCladRooster in forum Excel General
    Replies: 8
    Last Post: 12-20-2014, 02:15 PM
  5. Update multiple pivots tables with external data sources at once
    By ayalami79 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2013, 11:53 AM
  6. Update a single table with data from a worksheet with multiple tabs
    By Shaliza Riley in forum Access Tables & Databases
    Replies: 0
    Last Post: 06-26-2013, 01:20 PM
  7. [SOLVED] Update source data for multiple pivot tables on same worksheet
    By Platinum3x in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-19-2013, 12:11 AM

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