+ Reply to Thread
Results 1 to 5 of 5

Update cell contents WITHOUT calculating?

  1. #1
    Registered User
    Join Date
    05-02-2012
    Location
    Italy
    MS-Off Ver
    Excel 2007
    Posts
    7

    Update cell contents WITHOUT calculating?

    Hi everybody, I'm new to this forum!

    I have a little problem with my worksheet, so if you can help me out in any way, I will be very grateful!

    Basically, I installed Morefunc so that I can have the MRAND function to be able to randomize a list of names without repeats.
    Now, what I'd like to be able to achieve is this:

    - Suppose I have my list of names in range A1:A10
    - In range B1:B10 I have the MRAND array, {=INDEX(A1:A10,MRAND()+1)}
    This way, everytime I calculate, the list in B1:B10 is randomized without duplicates.

    Now, if I change a name in A1:A10, the corrisponding "linked cell" in B1:B10 doesn't change its value unless I recalculate, but by doing that I'm also gonna recalculate all the remaining cells because it's an array, so while the name in the randomized list is indeed updated, all cell positions are shuffled, and I don't want that.
    So, is there a way to just "update" the corrisponding cell WITHOUT having to calculate ALL the array?

    I really hope I made myself clear, otherwise I'll try to explain myself more clearly

    Thanks in advance

    Francesco

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Update cell contents WITHOUT calculating?

    Hi Francesco, welcome to the forum.

    The only way I could see accomplishing this is to have two macros - one to create the randomized list, the other two convert the MRAND functions in those cells to point to the cell in column A where the value actually exists. You could then change the values in A1:A10 and B1:B10 would update the individual cells (even though, technically, all would likely be calculating).

  3. #3
    Registered User
    Join Date
    05-02-2012
    Location
    Italy
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Update cell contents WITHOUT calculating?

    Hi Paul, thanks for your quick reply!

    Sorry if I'm such a pain, but... could you give me an example?
    PS: Maybe I forgot to tell you, my worksheet has manual calculation done by macro form buttons
    Last edited by Paul; 05-02-2012 at 05:47 PM. Reason: Removed quote of full previous post.

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Update cell contents WITHOUT calculating?

    As an example, this code should create your formula array in B1:B10 (I don't have MOREFUNC to test, though):
    Please Login or Register  to view this content.
    The following will convert those functions to simple "=A2", "=A5", etc. based on where those values exist in A1:A10.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-02-2012
    Location
    Italy
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Update cell contents WITHOUT calculating?

    Thanks Paul, I'm at work right now but as soon as I get home I'm gonna try it and let you know!

    PS: Sorry about the quote and the wrong forum section

    EDIT: Just tried it and it looks like it's working, thank you very much, you're a genius!
    Last edited by Ciais; 05-03-2012 at 04:05 AM.

+ 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