Excel Named Formulas

[goss]

Hi all,

Using Excel 2013.

I setup a Named Formula by entering the name in $E$9, a simple spread in $F:$BE, highlighted all and hit Ctrl+Shift+F3 and accepted Left Column.
So far so good.

I went to another sheet and entered a sample value in $F$3 and beginning in $H$3 I entered =$F$3*Spread52WeeksEvently which is the name of my Named Formula.
I spread the formula to column $BG

The last 2 formulas on Row 3 are returning #VALUE!errors.

How can I adjust the named formula so the correct results are returned regardless of where which column I begin the spread formula?

thx
w

[karedog]

Hi,

You define Spread52WeeksEvently as $F$9:$BE$9 but you recall it at $H$3:$BG$3, so it is offsetted 2 columns, so you cannot just directly call this name.
You should index() to do this.

Use this formula in cell H3 :

Please Login or Register  to view this content.

and copy this formula to right

For cell H3 : COLUMNS($H1:H1)=1, so INDEX(Spread52WeeksEvently,0,1) is INDEX($F$9:$BE$9,0,1) = F9
For cell H4 : COLUMNS($H1:I1)=2, so INDEX(Spread52WeeksEvently,0,2) is INDEX($F$9:$BE$9,0,2) = G9
For cell H5 : COLUMNS($H1:J1)=3, so INDEX(Spread52WeeksEvently,0,3) is INDEX($F$9:$BE$9,0,3) = H9

You got the idea.

Regards

[goss]

Thanks karedog,

Works great!