+ Reply to Thread
Results 1 to 3 of 3

Split table, but keep attributes attached?

  1. #1
    Registered User
    Join Date
    02-17-2017
    Location
    Munich
    MS-Off Ver
    Office 365
    Posts
    20

    Split table, but keep attributes attached?

    Hello,

    I have an Excel document containing two sheets; on the first sheet, input data are located, on the second sheet, I do perform calculations with the input data.

    Input data: A list of locations which several variables attached, e.g. temperature and altitude, sorted alphabetically.
    Calculation: A location's variable is multiplied with a certain factor (individual per location).

    Thus, what I have is basically a table which is split between page 1 and 2; some location attributes (temperature, altitude) are on page 1, others (multiplication factor and result) are on page 2.

    Now to my problem: The input data table is flexible, meaning that new locations can be added; the location list however stays sorted alphabetically. How can I make sure that the calculation factors on the second page stay attached to the right locations also if a new location is added "in the middle" of the location list?

    An example: On page 1, I have the locations London and Paris, and each city has got an altitude value.
    Location Altitude
    London 10
    Paris 200

    On page 2, I reference this table via "=A1", "=A2" etc, and I add an extra column for the multiplication factor and the result:

    Location Altitude Factor Result
    London 10 5 50
    Paris 200 10 2000

    If I add an extra location "Moscow", alphabetically between London and Paris on page 1, the Paris multiplication factor is wrongly attached to it on the second page. How can I make sure the Paris multiplication factor stays attached to the location Paris on page two?

    Thanks already in advance

    Requin

  2. #2
    Forum Contributor
    Join Date
    12-17-2013
    Location
    ON, Canada
    MS-Off Ver
    MS 365
    Posts
    171

    Re: Split table, but keep attributes attached?

    Requin,

    Could it work with doing away with page 1 and just have one table like you show on your page 2?

  3. #3
    Registered User
    Join Date
    02-17-2017
    Location
    Munich
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Split table, but keep attributes attached?

    Hello Flyboy,

    unfortunately, these pages have to be separated (separation between input data and calculations)...

    Requin

+ 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. how to create a form attached to a table
    By remyte in forum Excel General
    Replies: 2
    Last Post: 11-20-2016, 04:10 PM
  2. Table Structure - To Split up or not to split up?
    By mscola in forum Excel General
    Replies: 2
    Last Post: 09-02-2015, 01:17 AM
  3. [SOLVED] VBA Code that can split the contents of a cell into two cells? (Spreadsheet Attached)
    By Chad Bateman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-28-2015, 09:11 PM
  4. [SOLVED] Can't remember how I created attached table!
    By Clott in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2012, 06:05 AM
  5. Creating a pivot table with attributes that don't "stack"
    By athomas003 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-07-2011, 10:56 AM
  6. Automatically Apply Macro's/Formula's and Pivot Table attributes
    By karobonas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-14-2011, 05:50 AM
  7. Convert to date Table Attached
    By GeoGreco in forum Access Tables & Databases
    Replies: 0
    Last Post: 03-19-2009, 03:12 PM

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