Update cell contents WITHOUT calculating?

[Ciais]

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

[Paul]

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).

[Ciais]

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

[Paul]

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.

[Ciais]

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!