absolute column and relative row not working in my formula???

Hi Everyone :-)
I have a below formula which extracts the v and number attached to it I would like to copy this across all columns and change row for each column.
So for example 1st column reference will be B4, the second column would have a reference B5 and so on, it's probably worth to mention that cells I am referencing to are in other sheet hence why there's Ref! before B4 is this why absolute column and relative row not working??

Formula:

Please Login or Register  to view this content.

Thanks
Dan

So, you want to copy this ACROSS columns, but have the ROW parameter change?

You can use INDEX or INDIRECT to do this.

Post an example workbook.

Hope this helps.

Pete

Originally Posted by Pete_UK

So, you want to copy this ACROSS columns, but have the ROW parameter change?

You can use INDEX or INDIRECT to do this.

Post an example workbook.

Hope this helps.

Pete

Hi Pete
Thanks for your replay.
Here is a really simple sample but this should give you idea of what I want to do.
Thanks
Dan

Okay, you can put this formula in B2 of Sheet1:

=MID(INDEX(Index!$A:$A,COLUMNS($A:B)),IFERROR(SEARCH(" v? ",INDEX(Index!$A:$A,COLUMNS($A:B)),1),SEARCH(" v?? ",INDEX(Index!$A:$A,COLUMNS($A:B)),1))+1,3)

then copy it across.

Hope this helps.

Pete

Originally Posted by Pete_UK

Okay, you can put this formula in B2 of Sheet1:

=MID(INDEX(Index!$A:$A,COLUMNS($A:B)),IFERROR(SEARCH(" v? ",INDEX(Index!$A:$A,COLUMNS($A:B)),1),SEARCH(" v?? ",INDEX(Index!$A:$A,COLUMNS($A:B)),1))+1,3)

then copy it across.

Hope this helps.

Pete

Hi Pete
Just want to quickly thank you for your help, your solution worked like a charm :-)
Dan

You're welcome - thanks for feeding back (and for the rep).

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete