PDA

View Full Version : Balance two elements to a number



agnesz
09-01-2009, 02:40 PM
I have this spreadsheet that lists locations vertically and days horizontally. Each location has a plan as does each day. Is there any formula/macro that can adjust each day for each location to hit the that day's plan and that location's plan???

I am attaching a report as a sample. If you row 3. That location is under $26 to the plan and each day should be adjusted to hit that number, but keeping in mind that each day also has to hit the plan. As you can see though there is more than one location???

I don't even know if this is possible, but figured I could at least put it out there. Right now we do this manually and it is becoming impossible to deal with because I have a lot more locations than that.

any thoughts/suggestions would be super appreciated!:help

Benzadeus
09-02-2009, 04:22 AM
Is Plan the value set to the days and locations, like, cell B3?

agnesz
09-02-2009, 07:12 AM
I adjusted my attachment to show where the plans are that need to be hit by location and by day. B3 and the remaining days/locations in the matrix are "preliminary" plans. Those numbers need to be adjusted to hit the TOTAL DAY PLAN (ROW 21) AND THE TOTAL LOCATION PLAN (COLUMN AL). I hope this makes sense. Thanks for taking the time.

Benzadeus
09-02-2009, 03:43 PM
Well, try using my VBA approach:

Sub GetResults()

Const strPlan As String = "B20" 'range where the first Plan value appears

Dim cLast As Long
Dim rng As Range

With ActiveSheet
cLast = .Cells(.Range(strPlan).Row, .Columns.Count).End(xlToLeft).Column

For Each rng In .Range(.Range(strPlan), .Cells(.Range(strPlan).Row, cLast))
If Not rng = vbNullString Then
rng.Offset(1) = rng.Offset(1)
rng.Formula = "=R[-1]C-" & Replace(rng.Offset(1), ",", ".")
End If
Next rng

End With
End Sub

rbrhodes
09-02-2009, 06:30 PM
Hi agnesz,

Your example shows 18 items a day and 28 items a month. Where exactly would the adjustments go?

Afraid I don't get that part.