Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!:) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset Formula | Excel Discussion (Misc queries) | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
1 OFFSET FORMULA, I'M NOT SURE WHAT DOES IT MEAN!!!!!! | Excel Discussion (Misc queries) | |||
Help with Offset formula | Excel Discussion (Misc queries) | |||
Offset formula | Excel Worksheet Functions |