Offset Formula Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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???
 
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))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Unfortunately that just brings back a zero value.


Bob Phillips said:
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))


--
---
HTH

Bob

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



BoRed79 said:
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???
Click to expand...
Click to expand...
 
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???
Click to expand...

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
 
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
Click to expand...

He said he wanted the cell next to, that might be why he offsets one column
to the right
 
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

--
Regards,

Peo Sjoblom



BoRed79 said:
Unfortunately that just brings back a zero value.


Bob Phillips said:
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))


--
---
HTH

Bob

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



BoRed79 said:
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???
Click to expand...
Click to expand...
Click to expand...
 
I think the very last number in your formula (the 1) should be a zero
He said he wanted the cell next to, that might be why he offsets one
column to the right
Click to expand...

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

Rick
 
Thanks. This worked great.

Peo Sjoblom said:
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

--
Regards,

Peo Sjoblom



BoRed79 said:
Unfortunately that just brings back a zero value.


Bob Phillips said:
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))


--
---
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.

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???
Click to expand...
Click to expand...
Click to expand...
 
Back
Top