Named formulas in CHOOSE need to be Relative references when paste

Goal: Named formulas in CHOOSE need to be Relative references when pasted
down rows.

Example: I have a CHOOSE formula of
=IF(AC31=J31,J31*0.5,CHOOSE(B31,("A","B","H","D")))

When I paste this down the rows it will do a relative reference for AC31,
J31 and B31 by changing them to AC32, J32, and B32 and so on. My problem is
it won't make the named formula references"A","B","H","D") relative
references. The Named Formula's are:
"A" =VLOOKUP(D31,'2006 IP Template'!B:C,2,FALSE)
"B" =VLOOKUP(D31,'2006 IP Template'!B:G,6,FALSE)
"H" =VLOOKUP(C31,'2006 IP Payer Mix on Settled'!H:I,2,FALSE)*K31
"D" =VLOOKUP(D31,'2006 IP Template'!B:Y,10,FALSE)
The ultimate goal is: How can I get these named formulas to also be relative
references?? When in row 32 I need these to look at D32, C32 and K32.
Thanks

Bill
You could just put the Vlookups in the Choose command, it just makes the
formula look more complicated
Thanks
Paul

"bill ch" wrote:

> Goal: Named formulas in CHOOSE need to be Relative references when pasted
> down rows.
>
> Example: I have a CHOOSE formula of
> =IF(AC31=J31,J31*0.5,CHOOSE(B31,("A","B","H","D")))
>
> When I paste this down the rows it will do a relative reference for AC31,
> J31 and B31 by changing them to AC32, J32, and B32 and so on. My problem is
> it won't make the named formula references"A","B","H","D") relative
> references. The Named Formula's are:
> "A" =VLOOKUP(D31,'2006 IP Template'!B:C,2,FALSE)
> "B" =VLOOKUP(D31,'2006 IP Template'!B:G,6,FALSE)
> "H" =VLOOKUP(C31,'2006 IP Payer Mix on Settled'!H:I,2,FALSE)*K31
> "D" =VLOOKUP(D31,'2006 IP Template'!B:Y,10,FALSE)
> The ultimate goal is: How can I get these named formulas to also be relative
> references?? When in row 32 I need these to look at D32, C32 and K32.
> Thanks
>

The Choose command will eventually use almost all 29 index values so, it will
be too big for me to paste all the formulas into the Choose command =exceeds
the cell limit (# of characters). Thus, I've set them up as named formulas
for the CHOOSE formula to look-up. Am hoping there's a way to make the named
formula's relative references instead of absolute references when they are
looked-up from different rows.
Thanks

"paul@excelmastery.com" wrote:

> Bill
> You could just put the Vlookups in the Choose command, it just makes the
> formula look more complicated
> Thanks
> Paul
>
> "bill ch" wrote:
>
> > Goal: Named formulas in CHOOSE need to be Relative references when pasted
> > down rows.
> >
> > Example: I have a CHOOSE formula of
> > =IF(AC31=J31,J31*0.5,CHOOSE(B31,("A","B","H","D")))
> >
> > When I paste this down the rows it will do a relative reference for AC31,
> > J31 and B31 by changing them to AC32, J32, and B32 and so on. My problem is
> > it won't make the named formula references"A","B","H","D") relative
> > references. The Named Formula's are:
> > "A" =VLOOKUP(D31,'2006 IP Template'!B:C,2,FALSE)
> > "B" =VLOOKUP(D31,'2006 IP Template'!B:G,6,FALSE)
> > "H" =VLOOKUP(C31,'2006 IP Payer Mix on Settled'!H:I,2,FALSE)*K31
> > "D" =VLOOKUP(D31,'2006 IP Template'!B:Y,10,FALSE)
> > The ultimate goal is: How can I get these named formulas to also be relative
> > references?? When in row 32 I need these to look at D32, C32 and K32.
> > Thanks
> >