Offset formula
 

This is an example of the formula I am working on at the moment.
=OFFSET('JOB '!$B$20,MATCH("HD0474",'JOB '!$B$21:$B$38, 0),1)

If I want it to look up the part above (HD0474), but instead of typing it in
each time, get the information from the field A38 on the same sheet, how do I
do this?

Offset formula
 

Try this...

=OFFSET('JOB '!$B$20,MATCH('JOB '!$A$38,'JOB '!$B$21:$B$38, 0),1)

Is there really a blank space at the end of that sheet name like your posted
formula shows?

Rick


"SHELL" wrote in message
...
This is an example of the formula I am working on at the moment.
=OFFSET('JOB '!$B$20,MATCH("HD0474",'JOB '!$B$21:$B$38, 0),1)

If I want it to look up the part above (HD0474), but instead of typing it
in
each time, get the information from the field A38 on the same sheet, how
do I
do this?

Offset formula
 

"Rick Rothstein (MVP - VB)" wrote:

Try this...

=OFFSET('JOB '!$B$20,MATCH('JOB '!$A$38,'JOB '!$B$21:$B$38, 0),1)

Is there really a blank space at the end of that sheet name like your posted
formula shows?

Rick


"SHELL" wrote in message
...
This is an example of the formula I am working on at the moment.
=OFFSET('JOB '!$B$20,MATCH("HD0474",'JOB '!$B$21:$B$38, 0),1)

If I want it to look up the part above (HD0474), but instead of typing it
in
each time, get the information from the field A38 on the same sheet, how
do I
do this?

Thanks for that - using the example above field A38 is on the same sheet as the formula above (called PARTS) not on the JOB sheet.

So I tried
=OFFSET('JOB '!$B$20,MATCH("'PARTS'!$A$32",'JOB '!$B$21:$B$38, 0),1)
and
=OFFSET('JOB '!$B$20,MATCH("$A$32",'JOB '!$B$21:$B$38, 0),1)
and
=OFFSET('JOB '!$B$20,MATCH("A32",'JOB '!$B$21:$B$38, 0),1)

with both giving a #N/A answer instead of 1 (as there is a corresponding use
of that part on the JOB worksheet)

It seems to only work when I actually type the part no in as in the first eg.

Offset formula
 

"Rick Rothstein (MVP - VB)" wrote:

Try this...

=OFFSET('JOB '!$B$20,MATCH('JOB '!$A$38,'JOB '!$B$21:$B$38, 0),1)

Is there really a blank space at the end of that sheet name like your
posted
formula shows?

Rick


"SHELL" wrote in message
...
This is an example of the formula I am working on at the moment.
=OFFSET('JOB '!$B$20,MATCH("HD0474",'JOB '!$B$21:$B$38, 0),1)

If I want it to look up the part above (HD0474), but instead of typing
it
in
each time, get the information from the field A38 on the same sheet,
how
do I
do this?

Thanks for that - using the example above field A38 is on the same sheet
as the formula above (called PARTS) not on the JOB sheet.

So I tried
=OFFSET('JOB '!$B$20,MATCH("'PARTS'!$A$32",'JOB '!$B$21:$B$38, 0),1)
and
=OFFSET('JOB '!$B$20,MATCH("$A$32",'JOB '!$B$21:$B$38, 0),1)
and
=OFFSET('JOB '!$B$20,MATCH("A32",'JOB '!$B$21:$B$38, 0),1)

with both giving a #N/A answer instead of 1 (as there is a corresponding
use
of that part on the JOB worksheet)

It seems to only work when I actually type the part no in as in the first
eg.

And on every one you tried, you put quote marks around that argument.. if
you look at my posted example, you will see I did not use quote marks. If
you put quote marks around text, Excel will not try to assign any meaning to
it other than as a collection of characters... never as a range, formula,
etc.

Rick

Offset formula
 

"Rick Rothstein (MVP - VB)" wrote:

"Rick Rothstein (MVP - VB)" wrote:

Try this...

=OFFSET('JOB '!$B$20,MATCH('JOB '!$A$38,'JOB '!$B$21:$B$38, 0),1)

Is there really a blank space at the end of that sheet name like your
posted
formula shows?

Rick


"SHELL" wrote in message
...
This is an example of the formula I am working on at the moment.
=OFFSET('JOB '!$B$20,MATCH("HD0474",'JOB '!$B$21:$B$38, 0),1)

If I want it to look up the part above (HD0474), but instead of typing
it
in
each time, get the information from the field A38 on the same sheet,
how
do I
do this?

Thanks for that - using the example above field A38 is on the same sheet
as the formula above (called PARTS) not on the JOB sheet.

So I tried
=OFFSET('JOB '!$B$20,MATCH("'PARTS'!$A$32",'JOB '!$B$21:$B$38, 0),1)
and
=OFFSET('JOB '!$B$20,MATCH("$A$32",'JOB '!$B$21:$B$38, 0),1)
and
=OFFSET('JOB '!$B$20,MATCH("A32",'JOB '!$B$21:$B$38, 0),1)

with both giving a #N/A answer instead of 1 (as there is a corresponding
use
of that part on the JOB worksheet)

It seems to only work when I actually type the part no in as in the first
eg.

And on every one you tried, you put quote marks around that argument.. if
you look at my posted example, you will see I did not use quote marks. If
you put quote marks around text, Excel will not try to assign any meaning to
it other than as a collection of characters... never as a range, formula,
etc.

Rick

Thankyou so much, that did work! That was a bit stupid of me!:)