If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 news 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- |
Thread Tools | |
Display Modes | |
|
|