goal seek in a formula

[superspurs]

goal seek in a formula

because the goal seek tool only allows you to enter a value in the second box 'to value' and not reference a value already in a cell it makes it hard for large volumes of entries.

how would i go about writing a formula that would cover this:

set: M5 (formula in M5 = O5-N5-F5)
To value: G5 (no formula in cell, just value)
By changing cell: O5

Thanks,

[sweep]

Hi,

Goal seek can't be used in a formula, however, you could use VBA

    Range("M5").GoalSeek Goal:=Range("G5").Value, ChangingCell:=Range("O5")

[superspurs]

Brilliant, i tried that earlier but couldnt get it too work (im not great with macros) however i need to change this so it is relative reference, and I can't quite get this right.

ActiveCell.GoalSeek Goal:=ActiveCell.Offset(0, -6), ChangingCell:=ActiveCell.Offset(0, 2).Range

I know its around the goal bit i'm messing up, any idea though?

[sweep]

Close, you don't need the .range as activecell is already effectively a range.

ActiveCell.GoalSeek Goal:=ActiveCell.Offset(0, -6), ChangingCell:=ActiveCell.Offset(0, 2)

Please post your code in code tags in future, lest you suffer the wrath of the mods!