Re: One Nest Too Many - Help!

KL wrote...
>You can try the following ARRAY formula (confirm with Ctrl+Shift+Enter, not
>just Enter) which basically eliminates the nesting issue totally and you
>should be able to copy it easily:
>
>=IF('Resource Calcs'!$B2="","",'Resource Calcs'!$B2
>*INDEX(IF(MOD(COLUMN('Resource Calcs'!$BF2:$BS2),2)
>=MOD(COLUMN(INDEX('Resource Calcs'!$BF2:$BS2,2)),2),
>'Resource Calcs'!$BF2:$BS2),MATCH('Resource By Div'!$A$1,
>IF(MOD(COLUMN('Resource Calcs'!$BF2:$BS2),2)
>=MOD(COLUMN(INDEX('Resource Calcs'!$BF2:$BS2,1)),2),
>'Resource Calcs'!$BF2:$BS2),0)+1))
>
>Notes:
>1) this is a relatively expensive formula and may slow your sistem down
>significantly if copied to too many cells.
>2) the good news is that it doesn't seem to be volatile, so it will only
>recalculate if the affected ranges change

You seem to be trying for as general as possible. First, for
simplicity, I'll replace 'Resource Calcs'!$BF2:$BS2 with rng.

=IF('Resource Calcs'!$B2="","",'Resource Calcs'!$B2
*INDEX(IF(MOD(COLUMN(rng),2)=MOD(COLUMN(INDEX(rng, 2)),2),rng),
MATCH('Resource By Div'!$A$1,
IF(MOD(COLUMN(rng),2)=MOD(COLUMN(INDEX(rng,1)),2), rng),0)+1))

Much simplification possible.

=IF('Resource Calcs'!$B2="","",'Resource Calcs'!$B2
*INDEX(rng,MATCH(1,(rng='Resource By Div'!$A$1)
*(MOD(COLUMN(rng)-COLUMN(INDEX(rng,1)),2)=0),0)+1))

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1127512379.891552.222410@g43g2000cwa.googlegr oups.com...
> Much simplification possible.
> =IF('Resource Calcs'!$B2="","",'Resource Calcs'!$B2
> *INDEX(rng,MATCH(1,(rng='Resource By Div'!$A$1)
> *(MOD(COLUMN(rng)-COLUMN(INDEX(rng,1)),2)=0),0)+1))

Thanks Harlan - that's much better in deed.

Regards,
KL