| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OFFSET and MATCH functions for cells
I have a spreadsheet and reference file in which I am attempting to import
the values of a cell from the reference file into the cell of the spreadsheet template. For testing purposes, I setup the script below on the reference file and tested. =OFFSET($A$15,MATCH(P16,$C$16:$C$28,0),MATCH(Q16,$ A$16:$A$28,0),MATCH(T16,$F$16:$F$28,0),1). Most of the values imported over as should with the exception of a few that showed #VALUE! This is the cell/column values from the initial cell showed as followed: CATEGORY CATEGORY PLANT TO QUANTITY DATE FROM CODE BUILDER RUB 4560 6/8/2009 ARB USA8 1 RUB 3154 6/8/2009 ARB USA8 2 ATB 203 6/8/2009 ARB USA8 2 RUB 4218 6/9/2009 ARB USA8 1 RUB 2660 6/9/2009 ARB USA8 2 ATB 106 6/9/2009 ARB USA8 2 This is the value from the cells I am trying to match showed as followed: DATE CATEGORY CATEGORY TO QUANTITY FROM BUILDER 6/8/2009 RUB 4560 ARB 1 6/8/2009 RUB 3154 ARB 2 6/8/2009 ATB 203 ARB 2 6/9/2009 RUB 4218 ARB 1 6/9/2009 RUB 2660 ARB 2 6/9/2009 ATB #VALUE! ARB 2 What am I doing wrong? Also, need to be able to use up to 6 MATCH functions in each script and am only able to use 4 before I receive and error that says too many arguements are used. From my understanding, I should be able to use up to 7 MATCH functions in a script. Is this correct, and if so, please tell me how. Thanks- |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
OFFSET and MATCH functions for cells
Neecy,
Also, need to be able to use up to 6 MATCH functions in each script and am only able to use 4 before I receive and error that says too many arguements are used. From my understanding, I should be able to use up to 7 MATCH functions in a script. Is this correct, and if so, please tell me how. You can use as many MATCH functions as you want, within the limits of formula length and the number of arguments allowed by the parent function: Offset takes 5 arguments: OFFSET(reference,rows,cols,height,width) You will only be able to use 4 MATCH functions - since MATCH returns a number, it would only be useful for rows, cols, height, and width. The #VALUE! probably means that your third MATCH function is returning a number greater than 1, in which case you would need to array enter your formula into multiple cells. HTH, Bernie MS Excel MVP "Neecy" wrote in message ... I have a spreadsheet and reference file in which I am attempting to import the values of a cell from the reference file into the cell of the spreadsheet template. For testing purposes, I setup the script below on the reference file and tested. =OFFSET($A$15,MATCH(P16,$C$16:$C$28,0),MATCH(Q16,$ A$16:$A$28,0),MATCH(T16,$F$16:$F$28,0),1). Most of the values imported over as should with the exception of a few that showed #VALUE! This is the cell/column values from the initial cell showed as followed: CATEGORY CATEGORY PLANT TO QUANTITY DATE FROM CODE BUILDER RUB 4560 6/8/2009 ARB USA8 1 RUB 3154 6/8/2009 ARB USA8 2 ATB 203 6/8/2009 ARB USA8 2 RUB 4218 6/9/2009 ARB USA8 1 RUB 2660 6/9/2009 ARB USA8 2 ATB 106 6/9/2009 ARB USA8 2 This is the value from the cells I am trying to match showed as followed: DATE CATEGORY CATEGORY TO QUANTITY FROM BUILDER 6/8/2009 RUB 4560 ARB 1 6/8/2009 RUB 3154 ARB 2 6/8/2009 ATB 203 ARB 2 6/9/2009 RUB 4218 ARB 1 6/9/2009 RUB 2660 ARB 2 6/9/2009 ATB #VALUE! ARB 2 What am I doing wrong? Also, need to be able to use up to 6 MATCH functions in each script and am only able to use 4 before I receive and error that says too many arguements are used. From my understanding, I should be able to use up to 7 MATCH functions in a script. Is this correct, and if so, please tell me how. Thanks- |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
OFFSET and MATCH functions for cells
Thanks Bernie-
How do I array enter my formula into multiple cells? "Bernie Deitrick" wrote: Neecy, Also, need to be able to use up to 6 MATCH functions in each script and am only able to use 4 before I receive and error that says too many arguements are used. From my understanding, I should be able to use up to 7 MATCH functions in a script. Is this correct, and if so, please tell me how. You can use as many MATCH functions as you want, within the limits of formula length and the number of arguments allowed by the parent function: Offset takes 5 arguments: OFFSET(reference,rows,cols,height,width) You will only be able to use 4 MATCH functions - since MATCH returns a number, it would only be useful for rows, cols, height, and width. The #VALUE! probably means that your third MATCH function is returning a number greater than 1, in which case you would need to array enter your formula into multiple cells. HTH, Bernie MS Excel MVP "Neecy" wrote in message ... I have a spreadsheet and reference file in which I am attempting to import the values of a cell from the reference file into the cell of the spreadsheet template. For testing purposes, I setup the script below on the reference file and tested. =OFFSET($A$15,MATCH(P16,$C$16:$C$28,0),MATCH(Q16,$ A$16:$A$28,0),MATCH(T16,$F$16:$F$28,0),1). Most of the values imported over as should with the exception of a few that showed #VALUE! This is the cell/column values from the initial cell showed as followed: CATEGORY CATEGORY PLANT TO QUANTITY DATE FROM CODE BUILDER RUB 4560 6/8/2009 ARB USA8 1 RUB 3154 6/8/2009 ARB USA8 2 ATB 203 6/8/2009 ARB USA8 2 RUB 4218 6/9/2009 ARB USA8 1 RUB 2660 6/9/2009 ARB USA8 2 ATB 106 6/9/2009 ARB USA8 2 This is the value from the cells I am trying to match showed as followed: DATE CATEGORY CATEGORY TO QUANTITY FROM BUILDER 6/8/2009 RUB 4560 ARB 1 6/8/2009 RUB 3154 ARB 2 6/8/2009 ATB 203 ARB 2 6/9/2009 RUB 4218 ARB 1 6/9/2009 RUB 2660 ARB 2 6/9/2009 ATB #VALUE! ARB 2 What am I doing wrong? Also, need to be able to use up to 6 MATCH functions in each script and am only able to use 4 before I receive and error that says too many arguements are used. From my understanding, I should be able to use up to 7 MATCH functions in a script. Is this correct, and if so, please tell me how. Thanks- |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
OFFSET and MATCH functions for cells
Thanks Bernie-
How do I array enter my formula into multiple cells? Neecy "Bernie Deitrick" wrote: Neecy, Also, need to be able to use up to 6 MATCH functions in each script and am only able to use 4 before I receive and error that says too many arguements are used. From my understanding, I should be able to use up to 7 MATCH functions in a script. Is this correct, and if so, please tell me how. You can use as many MATCH functions as you want, within the limits of formula length and the number of arguments allowed by the parent function: Offset takes 5 arguments: OFFSET(reference,rows,cols,height,width) You will only be able to use 4 MATCH functions - since MATCH returns a number, it would only be useful for rows, cols, height, and width. The #VALUE! probably means that your third MATCH function is returning a number greater than 1, in which case you would need to array enter your formula into multiple cells. HTH, Bernie MS Excel MVP "Neecy" wrote in message ... I have a spreadsheet and reference file in which I am attempting to import the values of a cell from the reference file into the cell of the spreadsheet template. For testing purposes, I setup the script below on the reference file and tested. =OFFSET($A$15,MATCH(P16,$C$16:$C$28,0),MATCH(Q16,$ A$16:$A$28,0),MATCH(T16,$F$16:$F$28,0),1). Most of the values imported over as should with the exception of a few that showed #VALUE! This is the cell/column values from the initial cell showed as followed: CATEGORY CATEGORY PLANT TO QUANTITY DATE FROM CODE BUILDER RUB 4560 6/8/2009 ARB USA8 1 RUB 3154 6/8/2009 ARB USA8 2 ATB 203 6/8/2009 ARB USA8 2 RUB 4218 6/9/2009 ARB USA8 1 RUB 2660 6/9/2009 ARB USA8 2 ATB 106 6/9/2009 ARB USA8 2 This is the value from the cells I am trying to match showed as followed: DATE CATEGORY CATEGORY TO QUANTITY FROM BUILDER 6/8/2009 RUB 4560 ARB 1 6/8/2009 RUB 3154 ARB 2 6/8/2009 ATB 203 ARB 2 6/9/2009 RUB 4218 ARB 1 6/9/2009 RUB 2660 ARB 2 6/9/2009 ATB #VALUE! ARB 2 What am I doing wrong? Also, need to be able to use up to 6 MATCH functions in each script and am only able to use 4 before I receive and error that says too many arguements are used. From my understanding, I should be able to use up to 7 MATCH functions in a script. Is this correct, and if so, please tell me how. Thanks- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
OFFSET and MATCH functions for cells
Neecy,
Select multiple cells - say, 2 or 3 or 4 cells within one column - and then to enter your formula press Ctrl-Shift-Enter instead of just Enter. If you do it correctly, Excel will enclose your formula inside curly braces { }, and you will not be able to edit just one cell - you need to edit all four cells at once. HTH, Bernie MS Excel MVP "Neecy" wrote in message ... Thanks Bernie- How do I array enter my formula into multiple cells? "Bernie Deitrick" wrote: Neecy, Also, need to be able to use up to 6 MATCH functions in each script and am only able to use 4 before I receive and error that says too many arguements are used. From my understanding, I should be able to use up to 7 MATCH functions in a script. Is this correct, and if so, please tell me how. You can use as many MATCH functions as you want, within the limits of formula length and the number of arguments allowed by the parent function: Offset takes 5 arguments: OFFSET(reference,rows,cols,height,width) You will only be able to use 4 MATCH functions - since MATCH returns a number, it would only be useful for rows, cols, height, and width. The #VALUE! probably means that your third MATCH function is returning a number greater than 1, in which case you would need to array enter your formula into multiple cells. HTH, Bernie MS Excel MVP "Neecy" wrote in message ... I have a spreadsheet and reference file in which I am attempting to import the values of a cell from the reference file into the cell of the spreadsheet template. For testing purposes, I setup the script below on the reference file and tested. =OFFSET($A$15,MATCH(P16,$C$16:$C$28,0),MATCH(Q16,$ A$16:$A$28,0),MATCH(T16,$F$16:$F$28,0),1). Most of the values imported over as should with the exception of a few that showed #VALUE! This is the cell/column values from the initial cell showed as followed: CATEGORY CATEGORY PLANT TO QUANTITY DATE FROM CODE BUILDER RUB 4560 6/8/2009 ARB USA8 1 RUB 3154 6/8/2009 ARB USA8 2 ATB 203 6/8/2009 ARB USA8 2 RUB 4218 6/9/2009 ARB USA8 1 RUB 2660 6/9/2009 ARB USA8 2 ATB 106 6/9/2009 ARB USA8 2 This is the value from the cells I am trying to match showed as followed: DATE CATEGORY CATEGORY TO QUANTITY FROM BUILDER 6/8/2009 RUB 4560 ARB 1 6/8/2009 RUB 3154 ARB 2 6/8/2009 ATB 203 ARB 2 6/9/2009 RUB 4218 ARB 1 6/9/2009 RUB 2660 ARB 2 6/9/2009 ATB #VALUE! ARB 2 What am I doing wrong? Also, need to be able to use up to 6 MATCH functions in each script and am only able to use 4 before I receive and error that says too many arguements are used. From my understanding, I should be able to use up to 7 MATCH functions in a script. Is this correct, and if so, please tell me how. Thanks- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| offset() with row() and col() functions? | Excel Worksheet Functions | |||
| Match Offset by more than one value | Excel Discussion (Misc queries) | |||
| arguments in MATCH and OFFSET functions | Excel Worksheet Functions | |||
| Match Value and then offset | Excel Worksheet Functions | |||
| Use of offset and match functions with changing arrays, I think??? | Excel Worksheet Functions | |||
Linear Mode
