Offset formula

[SHELL]

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?

 

[Rick Rothstein \(MVP - VB\)]

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]

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




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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[SHELL]

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!