+ Reply to Thread
Results 1 to 6 of 6

Alphabetize column while moving associated data

  1. #1
    Registered User
    Join Date
    10-27-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    3

    Alphabetize column while moving associated data

    Hello. Hoping someone can help me with this. This is my first post, so apologies in advance if this isn't posted to the correct place or if similar problems have been answered before.

    Attached (I hope!!) is a dummy file with fake data, but replicates the issue I'm coming across. The actual file is a shared doc with the rest of the company and nearly everyone edits it on a daily basis.

    DETAILS:

    The Summary tab has dates in Row1 and PartnerNames in ColumnA. Row1 and ColumnA are hard entered.
    The $$ values (revenue) in the remaining cells are sums of the revenue by partnername from BrandA, BrandB, BrandC tabs.


    If you go to the tab for the Brand tabs, BrandA let's say, Row1 and ColumnA are set up the same way as the Summary tab. But instead of being hard entered strings, they are linked to the Summary tab and pull off of that.
    The revenue, however, is hard entered.

    Every morning, someone who works on BrandA would go into the BrandA tab and hard enter the revenue numbers from the prior day for a given partner.

    All of these values get summed on the Summary tab, by date and by partner.

    The issue i'm coming across is that everyone wants to have all the tabs alphabetized since it makes it easier each day to find the partner and enter the revenue (the real file has hundreds of partners). However, as time goes on, we get new partners. So someone will go to the Summary tab and add the new partner to the bottom of columnA. The Brand tabs will automatically pick up the new partner since it's linked to that cell in the Summary tab. And the Summary tab will pick up the total revenue since it's linked to the Brand tabs.

    At first it seems like I can just alphabetize columnA on the Summary tab. And since the other tabs are linked to it, they all get alphabetized too. But the problem arises when the Brand tabs get alphabetized, but the revenue numbers don't shift along with it. For example, if a user alphabetizes columnA on the Summary tab, then the revenue which is applied to JKL on the brand tabs would now be applied to MNO after alphabetization.

    My question is: How do I get the revenue numbers to move on the Brand tabs after the Summary tab's been alphabetized? I hate to say it, but this needs to be done without macros.

    Any insights/feedback would be super helpful. Thanks in advance. I used the "manage attachments" feature to attach the file so I'm hoping it works after I submit this post.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,227

    Re: Alphabetize column while moving associated data

    Please try at Summary B2

    =SUMPRODUCT(SUMIF(INDIRECT("Brand"&{"A";"B";"C"}&"!A2:A8"),$A2,INDIRECT("Brand"&{"A";"B";"C"}&"!R2C"&COLUMN()&":R6C"&COLUMN(),)))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-27-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    3

    Re: Alphabetize column while moving associated data

    Hi Bo_Ry

    Thank you for the help with this, but on the Brand tab it looks like the revenue values are being assigned incorrectly. Prior to alphabetization, JKL on BrandA, let's say, has a value of $590.74 on 1/1/19. But after alphabetization, JKL moves up and references $884.32 instead, which is not the value that should be associated with it.

  4. #4
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,227

    Re: Alphabetize column while moving associated data

    I don't get it. Please upload your sheet.

    Your sheet BrandA, B, C column A formula is =Summary!A2 it will always sort the same with Summary but data from other columns not move.
    Is this what you want?

  5. #5
    Registered User
    Join Date
    10-27-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    3

    Re: Alphabetize column while moving associated data

    Apologies if it was not clear. You are correct when you say that the data from the other columns on the Brand tabs do not move. However, I need them to move so they can be correctly referenced. Because if someone decides they want to look at revenue by day for BrandA specifically, for partner JKL, then they will need the correct values of $590.74, $250.45, $321.85, etc.

    But if the Summary tab is alphabetized and the values on the Brand tabs do not move with it, then this person would be looking at incorrect values for BrandA, partner JKL.

    Let me know if that makes sense.

  6. #6
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,227

    Re: Alphabetize column while moving associated data

    To me, remove formula at BrandA, B, C column A makes sense.
    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)

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