+ Reply to Thread
Results 1 to 7 of 7

Syncing/linkin of dyamically changing tables and/or ranges. Is this possible?

  1. #1
    Registered User
    Join Date
    11-03-2020
    Location
    Taiwan
    MS-Off Ver
    2013 or 2019
    Posts
    5

    Exclamation Syncing/linkin of dyamically changing tables and/or ranges. Is this possible?

    Hello,

    I did a makeover of this post. It seems that my way of wording caused confusion and misunderstanding, which is completely the opposite of what I wished for.


    I have attached a pdf, where I explain my idea in great detail. The sample workbook is nothing but a mere visualization of that idea and how it may look like. Please do not take the sample workbook as something like a before/after thingy, as I simply am not at that stage yet!

    What I am basically asking is wether this idea of mine is doable and if yes how it may be doable.


    I am very thankful for everyone who is taking the time to read through my summary. If you have any further questions please ask.

    Thank you!
    Attached Files Attached Files
    Last edited by ToCodeOrNotToCode; 11-05-2020 at 07:51 AM. Reason: makeover

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Dynamically updating tables and ranges (VBA or Non-VBA) stuck project

    Pretty pictures
    A workbook would be far more use - nobody (me included) are not going to spend time creating the workbook from scratch.

    See big yellow banner at the head of the page.
    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). 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.

  3. #3
    Registered User
    Join Date
    11-03-2020
    Location
    Taiwan
    MS-Off Ver
    2013 or 2019
    Posts
    5

    Re: Dynamically updating tables and ranges (VBA or Non-VBA) stuck project

    Hi, thanks a lot for the pointer. Although, the thing is that I donīt even have a workbook for this case I could present other than the contents of the picture in excel. No formulas to work with so far.

    So, at the moment I am still looking for ideas on how it could be realized, and then I would start from there.

    EDIT: added an example worksheet nonetheless. However, I am afraid it wonīt help much at this stage.
    Last edited by ToCodeOrNotToCode; 11-03-2020 at 10:48 AM.

  4. #4
    Registered User
    Join Date
    11-03-2020
    Location
    Taiwan
    MS-Off Ver
    2013 or 2019
    Posts
    5

    Re: Syncing/linkin of dyamically changing tables and/or ranges. Is this possible?

    Did a makeover of the title and the first post. It seems that it was not clear at all what I wanted to describe, so I changed it for the better, hopefully.

    If anyone could offer some insight, this would be much appreciated! Thank you.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Syncing/linkin of dyamically changing tables and/or ranges. Is this possible?

    I feel as if the data arrangement on the Master sheet is good, however using blocks of columns on the Input sheet is going to make analysis of that data difficult as compared to a proper row over row arrangement (i.e. Product Name, ID, Date, Amount and Price on each row).
    If we had some illustration of the analysis that is needed that might give us a better idea of how to help.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    11-03-2020
    Location
    Taiwan
    MS-Off Ver
    2013 or 2019
    Posts
    5

    Re: Syncing/linkin of dyamically changing tables and/or ranges. Is this possible?

    Quote Originally Posted by JeteMc View Post
    I feel as if the data arrangement on the Master sheet is good, however using blocks of columns on the Input sheet is going to make analysis of that data difficult as compared to a proper row over row arrangement (i.e. Product Name, ID, Date, Amount and Price on each row).
    If we had some illustration of the analysis that is needed that might give us a better idea of how to help.
    Indeed, you are absolutely right. The reason why I haven't found any better way to date lies in the difference of both the data:

    Sheet 1:
    Each product has an ID, ABC-Position, amount per box, storage location, etc. All stuff that has to be entered only once per row or can only appear once per product.

    Sheet 2:
    Each product has many many rows of sales data, like customer id, sales amount, sales price, etc. for a range of at least 2 years. So that will be hundreds if not thousands of rows. Clearly in this case it is not advised to try and enter those as columns on Sheet 1, let alone because of the issue with copying data into Excel alone (my source data uses rows for all the sales). This is how it looks like (for one Product):

    Customer dd/mm/yyyy Amount Price
    Customer dd/mm/yyyy Amount Price
    Customer dd/mm/yyyy Amount Price
    Customer dd/mm/yyyy Amount Price
    ...
    and many many more rows like this


    Now you know the crux why I find it so difficult to solve this. I thought I surely can't be the first person to try and summarize all product & sales data in Excel, can I? Thus I figured there may be solutions out there. I thought about having different workbooks for different analysis tasks, but since I am dealing with about 600-1000 products at the minimun I only want to enter the data once into Excel. And simply copy paste from my source as well, because everything else takes time.

    Additionally, my point why it should be synced/linked is that after I have entered both types of data in both Sheet 1 and Sheet 2, I will most likely filter my Sheet 1 formatted table or edit it. And then it quickly becomes an arduous task to find the relevant source data on Sheet 2 (e.g. when I entered Pro-B it was on position 2, speak Row 2 on Sheet 1 and Column 2 on Sheet 2, but after filtering it has become position 45, so it is in Row 45 on Sheet 1, but still in Column 2 on Sheet 2, and that is not useful.)


    Question:
    Is it possible in Excel to create a sort of data library where one chooses e.g. a product in a dropdown, and Excel automatically displays that information in a table? When choosing another entry (product) from the drop down the table's contents change to that particular entry's ones. Like something you would see in a lot of software that manages data.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Syncing/linkin of dyamically changing tables and/or ranges. Is this possible?

    It may be easier for someone to understand if we could see a sample of the analysis that you want to preform.
    For my part I used the following Power Query advanced editor code to arrange the data on the Input sheet (see green table in the attachment) into a proper row over row data set.
    Please Login or Register  to view this content.
    Note that the tables on the Input sheet were renamed Product_A (columns B:E) and Product_B (columns F:I).
    Note that Power Query is called Get & Transform in the 2019 version of Excel.
    Let us know if you have any questions.
    Attached Files Attached Files

+ 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. Replies: 13
    Last Post: 06-13-2017, 09:49 AM
  2. Dynamically updating Pivot tables
    By Wizard1001 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 05-31-2017, 12:30 PM
  3. Project getting stuck on a Do While statement
    By archangel1177 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2013, 06:18 PM
  4. [SOLVED] stuck in a little project ---> give unique id to papers
    By rip4life in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 05-25-2013, 05:49 PM
  5. [SOLVED] Auto updating Charts with Dynamically named Ranges
    By Xiophoid in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-19-2013, 01:18 PM
  6. Did you ever get stuck in a project?
    By Mordred in forum The Water Cooler
    Replies: 12
    Last Post: 11-08-2011, 07:05 AM
  7. Updating Pivot Tables Dynamically
    By JagR in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2010, 04:56 AM

Tags for this Thread

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