Offset Formula Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BoRed79 - 02 Jul 2007 15:54 GMT

I am using an offset formula to bring back data.

I am trying to bring back the cell next to the one that has a label that
matches the file name e.g. the file name is Place and then column a contains
a list of places and column b contains text about those places.

I am using the following formula:

=OFFSET(Data!A15,MATCH(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5),Data!$A$15:Data!$A$37,0),1)

Where Data! refers to the sheet which contains the data and the cells refer
to where on that sheet the data is.

However, the formula is not bringing back the value that is next to the one
which matches the filename, it is bringing back the one below it.

Can anyone see what I am doing wrong???

Reply to this Message

Bob Phillips - 02 Jul 2007 16:05 GMT

perhaps it should be

=OFFSET(Data!A15,0,MATCH(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5),Data!$A$15:Data!$A$37,0))

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I am using an offset formula to bring back data.
>
[quoted text clipped - 17 lines]
>
> Can anyone see what I am doing wrong???

Reply to this Message

BoRed79 - 02 Jul 2007 16:32 GMT

Unfortunately that just brings back a zero value.

> perhaps it should be
>
[quoted text clipped - 22 lines]
> >
> > Can anyone see what I am doing wrong???

Reply to this Message

Peo Sjoblom - 02 Jul 2007 20:32 GMT

This works for me

=INDEX(Data!$B$15:$B$37,MATCH(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5),Data!$A$15:Data!$A$37,0))

this assume you are matching the filename (no file extension) in A15:A37 and
want to return the adjacent cell in B15:B37 to where the match is in
A15:A37?

Btw, since you are searching for "[" you can use find instead since it
shortens your formula with 4 characters

=INDEX(Data!$B$15:$B$37,MATCH(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-5),Data!$A$15:Data!$A$37,0))

if that doesn't work and you are indeed looking for a value in B15:B37 then
your values in A15:A37 have extra spaces or other invisible characters

Signature

Regards,

Peo Sjoblom

> Unfortunately that just brings back a zero value.
>
[quoted text clipped - 25 lines]
>> >
>> > Can anyone see what I am doing wrong???

Reply to this Message

BoRed79 - 03 Jul 2007 08:02 GMT

Thanks. This worked great.

> This works for me
>
[quoted text clipped - 41 lines]
> >> >
> >> > Can anyone see what I am doing wrong???

Reply to this Message

Rick Rothstein (MVP - VB) - 02 Jul 2007 20:12 GMT

>I am using an offset formula to bring back data.
>
[quoted text clipped - 17 lines]
>
> Can anyone see what I am doing wrong???

I think the very last number in your formula (the 1) should be a zero (0)...
that is the Column Offset and you seem to be saying offset it 1 instead of
0.

Rick

Reply to this Message

Peo Sjoblom - 02 Jul 2007 20:21 GMT

> I think the very last number in your formula (the 1) should be a zero
> (0)... that is the Column Offset and you seem to be saying offset it 1
> instead of 0.
>
> Rick

He said he wanted the cell next to, that might be why he offsets one column
to the right

Signature

Regards,

Peo Sjoblom

Reply to this Message

Rick Rothstein (MVP - VB) - 02 Jul 2007 20:57 GMT

>> I think the very last number in your formula (the 1) should be a zero
>> (0)... that is the Column Offset and you seem to be saying offset it 1
[quoted text clipped - 4 lines]
> He said he wanted the cell next to, that might be why he offsets one
> column to the right

Ah yes... a misread on my part. Thanks.

Rick

Reply to this Message

Peo Sjoblom - 02 Jul 2007 21:17 GMT

>>> I think the very last number in your formula (the 1) should be a zero
>>> (0)... that is the Column Offset and you seem to be saying offset it 1
[quoted text clipped - 6 lines]
>
> Ah yes... a misread on my part. Thanks.

Don't know if it's true though. Only the OP can tell <bg>

Peo

Reply to this Message