Hi guys, I am brainstorming over a logic problem, if anyone could help it would be greatly appreciated.

I have a spreadsheet with 9 columns and 9 rows, I want the sum of every column and every row to be number 45, and if a number in a cell changes then all numbers auto adjust to again produce 45.

Any clues?

Thanks !

What drives the number change? Presumably if you have a 9x9 grid and
the number in row 4 column 6 changes, then you want that number to
remain static while other combinations are considered- correct? How
many numbers will need to be "locked" while checking combinations? Are
zeroes allowed?

The brute force approach is to write some FOR LOOP code that checks all
the possible combinations, and considers the static cells.

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Range("A1:I9")
If Not Intersect(Target.Cells, .Cells) Is Nothing Then
Application.EnableEvents = False
.Value = .Value
If Target.Row < 9 Then
.Rows(9).Formula = "=45-SUM(A1:A8)"
.Columns(9).Formula = "=45-SUM(A1:H1)"
.Value = .Value
Else
.Rows(1).Formula = "=45-SUM(A2:A9)"
.Columns(1).Formula = "=45-sum(A2:I2)"
End If
.Value = .Value
Application.EnableEvents = True
End If
End With
End Sub


In article ,
kernelwiz wrote:

Hi guys, I am brainstorming over a logic problem, if anyone could help
it would be greatly appreciated.

I have a spreadsheet with 9 columns and 9 rows, I want the sum of every
column and every row to be number 45, and if a number in a cell changes
then all numbers auto adjust to again produce 45.

Any clues?

Thanks !

I'd use solver to do it. Let's say A1:I9 is your 9x9 grid, A10:I10 and
J1:J9 is your SUM() of the corresponding row and column, and J10 is the
grand total.

Set up solver:
1. Target cell = J10 - value of 810 (45x18)
2. By changing cells A1:I9
3. Constraint A10:I10 = 45
4. Constraint J1:J9 = 45
5. Constraint A1:I9 = int (if you only want integer)
6. Run solver

Now let's say you want A1 = 6, simply remove A1 from "By changing
cells" and run solver.


Hope it helps.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=467243

That's an incredibly theoretical question.. many more questions come from it
- Do you want the cell that changed to remain the same??
- do you want the other cells in the row that are changing to have a linear
change (ie they ALL change by the same value) or should they changed based on
how big they already are?? (weighted average)
etc, etc

Either way, you have to come up with your method.

To implement it, I believe you will have to trap the 'old' value of the cell
and the new value. That way you can take the difference and spread it out
(as you define it) over the othe cells in the row/column).

Set a module level variable in the selection_change event every time you
change the selection. This will be your old value (** Might store the old
cell address as well)

Then kick off the routine that updates columns and rows in the change event..

If you can't




"kernelwiz" wrote:


Hi guys, I am brainstorming over a logic problem, if anyone could help
it would be greatly appreciated.

I have a spreadsheet with 9 columns and 9 rows, I want the sum of every
column and every row to be number 45, and if a number in a cell changes
then all numbers auto adjust to again produce 45.

Any clues?

Thanks !


--
kernelwiz