Hello all!
I hope you are well.
Well as the title suggests i'd like to get a macro going which combines goalseek and the INDEX function.
Basically i have columns labeled A, B, C etc
On same sheet below i have Rows labeled A, B, C etc
so if in A1 i have =INDEX($F$35:$AF$35;MATCH(F84;$F$7:$AF$7;0)) where $F$35:$AF$35 contain values under labeled columns labled A, B, C etc, F84 is the letter, U for this example, $F$7:$AF$7 contain the columns whit letter index labeled A, B, C etc
It gives me the value of U form the $F$35:$AF$35 range.
So it goes through all the columns gets me the right price under that indexed letter.
Hope i didn't get you lost already.
What i want to do is a goal seek macro with indexing capabilities predefined by the user. i.e. do only A to F. by changing the value under indexed location.
I don't want to go by hand and change all the cells across all the columns manually clicking goal seek.
Here is the interesting part. The value of "by Changing cell:" needs to be varied say between 1 to 10 in steps of 1 or 0,5 and and starts incrementing againg once at max value.
Maybe if it will loop...
Set cell: G80 (column label B)
To Value: 1
By Changing cell: G85 (row labeled B)
Set cell: H80 (column label C)
To Value: 1,1 <-- Increments
By Changing cell: G86 (row labeled C)
Set cell: I80 (column label D)
To Value: 1,2 <-- Increments
By Changing cell: G87 (row labeled D)
Indexing is necessary as $G$84:$G$110 contains the mother values to be changed and spaces. Where as $F$80:$AB$80 are the slave values that are to be set.
This is what i got so far, which is not much...
Let me know if i confused you =)
Regards,
Defy
Bookmarks