Offset nested with =Cell("address"***

[Ropes4u]

I have created the below formula to return an address. I need to use this address in the second formula ("cell address here"), If i am completely off base please point me in the right direction..


=CELL("Address",INDEX($BO$23:$BO$40,MATCH($BE$4,$BO$23:$BO$40,0)))



=OFFSET("Cell Address here",10-$BI$1,0,1,1)

Thank You, john

 

[xld]

Try

=OFFSET(INDIRECT(CELL("Address",INDEX($BO$23:$BO$40,MATCH($BE$4,$BO$23:$BO$40,0)))),10-$BI$1,0,1,1)

 

[barry houdini]

You don't need the CELL function, The INDEX/MATCH part can go straight in the OFFSET, i.e.

=OFFSET(INDEX($BO$23:$BO$40,MATCH($BE$4,$BO$23:$BO$40,0)),10-$BI$1,0,1,1)

and that simplifies to

=OFFSET($BO$22,MATCH($BE$4,$BO$23:$BO$40,0)+10-$BI$1,0)

 

[Aladin Akyurek]

I have created the below formula to return an address. I need to use this address in the second formula ("cell address here"), If i am completely off base please point me in the right direction..


=CELL("Address",INDEX($BO$23:$BO$40,MATCH($BE$4,$BO$23:$BO$40,0)))



=OFFSET("Cell Address here",10-$BI$1,0,1,1)

Thank You, john

Substituting the INDEX bit might do the job:

=OFFSET(INDEX($BO$23:$BO$40,MATCH($BE$4,$BO$23:$BO$40,0)),10-$BI$1,0,1,1)

 

[Ropes4u]

Re: Offset nested with =Cell("address"*** - SOLVED

I cannot thank you enough, You made my weekend!

Thank You, John

 

[Ropes4u]

I am almost there but I now need the column to be dynamic. I need to change the $BO$6 and $BO$6:$BO$23 to move based on another cell value.

2 3 4 5
$BO$6 $BP$6 $BQ$6 $BR$6

 

[barry houdini]

Your original formula doesn't reference BO6.....

If you want the column to be based on a value 2 to 5 in cell Z1 try

=OFFSET($BO$22,MATCH($BE$4,$BO$23:$BO$40,0)+10-$BI$1,Z1-2)

 

[Ropes4u]

Barry - Sorry for the confusion I move some things around when I thought it was working.

The current formula works like a charm if the units never change. The curves change drastically so I can not normalize them of just offset the column. I need to select the column then perform the offset and calculations. Does this make any sense?

BO.....................BP....................BQ....................BR < Column
2......................3.......................4......................5 < Units
0.117763171 0.127656791 0.042409532 0.039719442
0.191365152 0.132975824 0.122381793 0.08423261
0.309128323 0.159570988 0.24961039 0.132169867
0.471052682 0.209215296 0.396226202 0.204760572

=OFFSET($BO$23,MATCH((VLOOKUP(-((AC4-F4)/AZ4),$BO$23:$BO$40,1)),$BO$23:$BO$40,0)+10-$BI$1,0)

 

[barry houdini]

Not sure why you need VLOOKUP within MATCH, you should just be able to use MATCH with a third argument of 1, i.e.

=OFFSET($BO$23,MATCH((F4-AC4)/AZ4,$BO$23:$BO$40,1)+10-$BI$1,0)

Now if the column is variable you can change that to

=OFFSET($BO$23,MATCH((F4-AC4)/AZ4,INDEX($BO$23:$BR$40,0,MATCH(BA4,$BO$22:$BR$22,0)),1)+10-$BI$1,MATCH(BA4,$BO$22:$BR$22,0)-1)

Assuming that BO22:BR22 contains the units and BA4 is the specific number of units

 

[Ropes4u]

I cannot thank you enough it works like a charm. I get a few errors when I copy it down but I suspect that will be resolved easily enough..