+ Reply to Thread
Results 1 to 2 of 2

Sort data with auto updating of cell reference.

  1. #1
    Registered User
    Join Date
    03-23-2022
    Location
    Kraków, Poland
    MS-Off Ver
    Microsoft 365
    Posts
    3

    Lightbulb Sort data with auto updating of cell reference.

    Hi everyone,

    Right now i try make my sheets international, so i try make possibilities to change text in my sheets by picking preferred language. But unfortunately I have a downtime, basically i have problem with sorting data.
    My problem is when I want to sort all the words in one of the languages ​​(attached) my linked cells also change. Here's what I want to do:

    1. I want to add new words in columns and translate it further I want to be able to sort all columns, but point 2:

    2. Cell form: = L14: Q21, should not be changed. for example: cell N14 = D8, so if this is sorted from smallest numbers to largest numbers, it is the number "3" in each language. But if I sort it from largest to smallest, that number is now "4". I would like to ask if it is possible when I sort from largest to smallest cell N14 will change to N14 = D9 = "3", if I switch again it will be N14 = D8 = "3" etc. If I choose English and sort these words alphabetically then N14 = D8 = "one" no "three", according to my assumption, after this sorting, cell N14 should be equal to: N14 = D7 = "three" ...

    3. So my main requirement i want to get is auto reference change creation. if in the starting point cell N14 = D8 = "three", then after sorting it should change analogously, i.e. depending on the sort, it should have one time: N14 = D6 = "three", another time N14 = D7 = "three", N14 = D394 = "three" etc.

    I really hope, that you undestand what i mean by this story

    I will be grateful for all ideas and any help.

    regards
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Sort data with auto updating of cell reference.

    Probably it's not an acceptable solution, but may be, so let;s try: a more complicated formula in L15 (and similar in other cells) shall be used:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    (or it's equivalent with INDEX and MATCH for compatibility with older versions of excel)

    On the other hand, you have quite complicated formulas in column D. I think that for D5
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and copy down would be fine. If you really expecte error you cen use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Second option - store a table with the numbers as numbers in one range and in the other range use Xlookup to "translate it" to currently selected language. This way you will have easily visible and easy to input real values and the translation too.

    Note that depending on your regional settings you may need to use comas not semicolons as argument separators in formulas
    Best Regards,

    Kaper

+ 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. Updating Workbook cell reference based on current worksheet cell value
    By BadKornFlake in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-13-2020, 01:01 PM
  2. Updating Cell Reference each Month
    By asleischow in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-05-2017, 12:02 PM
  3. How to sort corresponding cell data in a worksheet when cell reference is used?
    By envirominerva in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-29-2016, 04:22 AM
  4. Named cell reference not updating like it should
    By KLT in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-06-2013, 12:30 PM
  5. concatenate with updating cell reference
    By bobo1000 in forum Excel General
    Replies: 0
    Last Post: 10-23-2012, 05:27 AM
  6. auto sort columns from reference thru another workbook
    By ancientanubis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-09-2008, 07:17 PM
  7. vb auto data sort when entry 2 cell
    By n_caskey in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2008, 10:13 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