auto-compensate proportions based on another cell's value

Thank you so much, this is fantastically helpful and lets me keep some hair on my head. I have a couple of questions as I am definitely not as XLent with code as you are!

1. For the total-to-remain-unchanged, in this case 100%, how can I adjust what I want that total to be? For example, 85% or 12 units or whatever?
2. Is there any way to re-set or undo? I notice that once it's running I cannot undo/step-back, or re-set the numbers to their original settings.
3. How could I set it so that it checks different non-contiguous ranges, e.g. B1:B5, C12:C15 etc., but makes sure that all of them summed keep to the total as set?

Many thanks for your generosity of expertise

Quite a few elements to the post above; I suspect a file outlining a few of the various scenarios with some expected results (i.e. before / after) would help

Resetting, and undoing etc is easy enough however, depending on how flexible you want this to be you may choose to use a (very hidden) audit log (tab) rather than Statics etc.

In summary - once you start to use VBA things get relatively flexibile
for ex. you could establish a prefix which the Event checks for, (and removes), but ensures you circumvent the apportionment logic.

In terms of checking non-contiguous ranges -- presumably you don't mean in parallel? If you do, then the use of the Change Event may not be viable - we'd need to understand how the precedents, impacting those non-contiguous ranges, are being updated. Whilst you can, obviously, update non contiguous cells manually in parallel, c/o SHIFT + ENTER, it would be a little unusual in this type of context.

Thank you, and fair enough - so I think the main thing for me to understand if you could help, would be to know how can I set the 'total' to be something other than 100%?

I'm really a newbie with VBA so please assume my ignorance!

Non-contig. ranges: what I mean here I think is in sequence not parallel, and all the cells would be in the same column. So the event would still be a single change to a single cell in that column, but there may be spaces to ignore between the cell ranges, so the cells to be checked sequentially would be B1:B12 then B15:B17 etc.
I tried to specify that using:
Set rngInterest = Range("B1:B12, B15:B17")
but the total seemed to go over 100% in certain circumstances, so I just wanted to check if this was the right way to do it.

But, where do you envisage entering the revised total (i.e. was 100% now 85%) whilst, at same time, entering the revised % (28%) ?

I suspect you may end up wanting / needing to change tack altogether
e.g. switching from Change Event to the BeforeDoubleClick Event on these ranges, such that user is prompted to enter new total, and new % via Input Box dialogs.

In relation to the non-contiguous ranges question -- if there are true blanks between these ranges, such that they are discrete areas, you may find you can get away with:

- verifying initial intersect (as you did)
- thereafter relying on the CurrentRegion of the Target {and intersect with B)

but, without seeing the setup it's hard to give definitive answers.

I'm afraid I am heading offline but there are others who will be more than happy to assist, I'm sure, if you can provide feedback re: above.

Hi, I've pulled together the attached which utilises

1. the BeforeDoubleClick Event {to assist with 'total' question}
2. a (very hidden) Log sheet {to assist with 'resetting' a given region at any point to values as per session start}

What you should find is that you can, via double click on any given yellow cell, initiate the new target / new value entry - and subsequent recalc across remainder of region

if you click Cancel on the first dialog you can confirm a reset -- this will leverage the (very) hidden tab which cached the values when the code first ran, in that session
(use of the alternative Static is more complex given the multiple regions)

as noted, the regions can be modified / reset independent of one another, i.e. you can change B10:B12 without impacting B1:B5 and vice-versa.

There are invariably going to be more elegant / succinct approaches but this is what first came to mind and, hopefully, helps you work through the practicalities of what you're trying to achieve if nothing else.

Please Login or Register  to view this content.