Offset formula error

[ddibs]

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000 } span.s1 {color: #006107 } span.s2 {color: #ab30d6 } span.s3 {color: #0057d6 } span.s4 {color: #a54a29 } span.s5 {color: #33af4a } </style>=OFFSET(CELL("address",INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1)),0,1)

This is my formula and I get an error saying my formula contains an error can anyone tell me that that might be.

Thanks!

 

[ddibs]

I have this formula that says it has an error

=OFFSET(CELL("address",INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1)),0,1)

<colgroup><col></colgroup><tbody>
</tbody>

but when I take off the OFFSET it works just fine.

This CELL("address",INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1))

gives something like $A$7 so it seems to me that this should be accepted by the OFFSET formula for its first parameter.


What can I do to fix this?

 

[Aladin Akyurek]

Re: can the OFFSET reference be a formula?

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000 } span.s1 {color: #006107 } span.s2 {color: #ab30d6 } span.s3 {color: #0057d6 } span.s4 {color: #a54a29 } span.s5 {color: #33af4a } </style>=OFFSET(CELL("address",INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1)),0,1)

This is my formula and I get an error saying my formula contains an error can anyone tell me that that might be.

Thanks!

Are you looking for this?

=OFFSET(INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1),0,0,1)

If not, care to elaborate?

 

[wideboydixon]

What are you hoping to return? CELL("address"..) will return a text string with the address of the cell. Do you want to return "$B$7" as a string or do you want to return the contents of $B$7? Either:

=CELL("address",OFFSET(INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1),0,1))

... or ...

=OFFSET(INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1),0,1)

WBD

 

[Scrappy666]

Hi Ddibs,

Give one of these a try:
=OFFSET(INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1),0,1)
=OFFSET(INDIRECT(CELL("address",INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1))),0,1)

The first one may work, depending on the data.

The original formula was returning the cell reference as text, so it didn't know what to offset.
Using an INDIRECT changes the text into a readable cell reference.

Good luck,
Dan.

 

[ddibs]

Re: can the OFFSET reference be a formula?

No, my formula is a little different this part <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000 } span.s1 {color: #006107 } span.s2 {color: #0057d6 } span.s3 {color: #ab30d6 } span.s4 {color: #a54a29 } </style>=CELL("address",INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1))

prints out the cell reference of a character I am searching for and it works

I then want to take the cell to the right of that selected cell so I place the above formula in the to OFFSET formula and then I get this "the formula you entered contains an error"

 

[Joe4]

Re: can the OFFSET reference be a formula?

I have merged your two threads together. Per forum rule 12 here: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html, please do not post the same question in multiple places.

 

[ddibs]

Re: can the OFFSET reference be a formula?

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000 } span.s1 {color: #006107 } span.s2 {color: #0057d6 } span.s3 {color: #ab30d6 } span.s4 {color: #a54a29 } </style>=OFFSET(INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)),0,1)

This worked thank you all so much!

I was expecting a longer wait time, this was incredible!

 

[ddibs]

=OFFSET(INDIRECT(CELL("address",INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1))),0,1)
perfect solution as well thank you.

 

[Aladin Akyurek]

Re: can the OFFSET reference be a formula?

No, my formula is a little different this part <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000 } span.s1 {color: #006107 } span.s2 {color: #0057d6 } span.s3 {color: #ab30d6 } span.s4 {color: #a54a29 } </style>=CELL("address",INDEX($V$2:$V$76,MATCH(MID(O8,1,1),$V$2:$V$76,0)+1))

prints out the cell reference of a character I am searching for and it works

I then want to take the cell to the right of that selected cell so I place the above formula in the to OFFSET formula and then I get this "the formula you entered contains an error"

Suppose I have Amos in O8 and I have an A in V9. What must the formula you require do and where exactly?