| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ISERROR and INDEX Funcion question
I have the following formula in column 4, row 4 of a multi row/column
spreadsheet, where I am referencing a value from one worksheet and looking for the same value in an adjacent worksheet. It's basically a VLOOKUP function. =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)),"",INDEX('Da ily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)) The function works as I want it to; however, if I copy the formula exactly as is to say column 6, row 4, and then change the range from A1:a77 to A81:A90, then the formula does not work. I can't figure out why. Can anyone help? Thanks. -- Allan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ISERROR and INDEX Funcion question
See if this version is amendable
=IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)-MIN(ROW('Daily Activity'!$A$1:$A$77))+1),ROW(1:$1)),2)),"", INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)-MIN(ROW('Daily Activity'!$A$1:$A$77))+1),ROW(1:$1)),2)) -- HTH Bob "Flipper" wrote in message ... I have the following formula in column 4, row 4 of a multi row/column spreadsheet, where I am referencing a value from one worksheet and looking for the same value in an adjacent worksheet. It's basically a VLOOKUP function. =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)),"",INDEX('Da ily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)) The function works as I want it to; however, if I copy the formula exactly as is to say column 6, row 4, and then change the range from A1:a77 to A81:A90, then the formula does not work. I can't figure out why. Can anyone help? Thanks. -- Allan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ISERROR and INDEX Funcion question
What type of data is being returned? Is it text, numeric or could it be
either? -- Biff Microsoft Excel MVP "Flipper" wrote in message ... I have the following formula in column 4, row 4 of a multi row/column spreadsheet, where I am referencing a value from one worksheet and looking for the same value in an adjacent worksheet. It's basically a VLOOKUP function. =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)),"",INDEX('Da ily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)) The function works as I want it to; however, if I copy the formula exactly as is to say column 6, row 4, and then change the range from A1:a77 to A81:A90, then the formula does not work. I can't figure out why. Can anyone help? Thanks. -- Allan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ISERROR and INDEX Funcion question
Hi Bob
Thanks for the quick response. the new formula does not work either. I obviously don't understand why changing the "lookup" range causes the formula not to work. I want it to do the same thing, just based on a different range of data. -- Allan "Bob Phillips" wrote: See if this version is amendable =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)-MIN(ROW('Daily Activity'!$A$1:$A$77))+1),ROW(1:$1)),2)),"", INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)-MIN(ROW('Daily Activity'!$A$1:$A$77))+1),ROW(1:$1)),2)) -- HTH Bob "Flipper" wrote in message ... I have the following formula in column 4, row 4 of a multi row/column spreadsheet, where I am referencing a value from one worksheet and looking for the same value in an adjacent worksheet. It's basically a VLOOKUP function. =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)),"",INDEX('Da ily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)) The function works as I want it to; however, if I copy the formula exactly as is to say column 6, row 4, and then change the range from A1:a77 to A81:A90, then the formula does not work. I can't figure out why. Can anyone help? Thanks. -- Allan . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ISERROR and INDEX Funcion question
It should be numeric, but as it is, nothing is being returned.
-- Allan "T. Valko" wrote: What type of data is being returned? Is it text, numeric or could it be either? -- Biff Microsoft Excel MVP "Flipper" wrote in message ... I have the following formula in column 4, row 4 of a multi row/column spreadsheet, where I am referencing a value from one worksheet and looking for the same value in an adjacent worksheet. It's basically a VLOOKUP function. =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)),"",INDEX('Da ily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)) The function works as I want it to; however, if I copy the formula exactly as is to say column 6, row 4, and then change the range from A1:a77 to A81:A90, then the formula does not work. I can't figure out why. Can anyone help? Thanks. -- Allan . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ISERROR and INDEX Funcion question
Ok, here's one way that the formula can be written...
Let's assume that you want the formula entered in cell C4 then copied down. Array entered: =IF(ROWS(C$4:C4)COUNTIF('Daily Activity'! A$1:A$77,B$4),"",INDEX('Daily Activity'!B:B, SMALL(IF('Daily Activity'!A$1:A$77=B$4,ROW ('Daily Activity'!A$1:A$77)),ROWS(C$4:C4)))) -- Biff Microsoft Excel MVP "Flipper" wrote in message ... It should be numeric, but as it is, nothing is being returned. -- Allan "T. Valko" wrote: What type of data is being returned? Is it text, numeric or could it be either? -- Biff Microsoft Excel MVP "Flipper" wrote in message ... I have the following formula in column 4, row 4 of a multi row/column spreadsheet, where I am referencing a value from one worksheet and looking for the same value in an adjacent worksheet. It's basically a VLOOKUP function. =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)),"",INDEX('Da ily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)) The function works as I want it to; however, if I copy the formula exactly as is to say column 6, row 4, and then change the range from A1:a77 to A81:A90, then the formula does not work. I can't figure out why. Can anyone help? Thanks. -- Allan . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ISERROR and INDEX Funcion question
I'm probably doing something wrong, because this doesn't work correctly
either. It does return a value, but it's the wrong one. "T. Valko" wrote: Ok, here's one way that the formula can be written... Let's assume that you want the formula entered in cell C4 then copied down. Array entered: =IF(ROWS(C$4:C4)COUNTIF('Daily Activity'! A$1:A$77,B$4),"",INDEX('Daily Activity'!B:B, SMALL(IF('Daily Activity'!A$1:A$77=B$4,ROW ('Daily Activity'!A$1:A$77)),ROWS(C$4:C4)))) -- Biff Microsoft Excel MVP "Flipper" wrote in message ... It should be numeric, but as it is, nothing is being returned. -- Allan "T. Valko" wrote: What type of data is being returned? Is it text, numeric or could it be either? -- Biff Microsoft Excel MVP "Flipper" wrote in message ... I have the following formula in column 4, row 4 of a multi row/column spreadsheet, where I am referencing a value from one worksheet and looking for the same value in an adjacent worksheet. It's basically a VLOOKUP function. =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)),"",INDEX('Da ily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)) The function works as I want it to; however, if I copy the formula exactly as is to say column 6, row 4, and then change the range from A1:a77 to A81:A90, then the formula does not work. I can't figure out why. Can anyone help? Thanks. -- Allan . . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ISERROR and INDEX Funcion question
Here's a small sample file that demonstrates this.
zFlipper.xls 19kb http://cjoint.com/?dAwA7hA0GL -- Biff Microsoft Excel MVP "Flipper" wrote in message ... I'm probably doing something wrong, because this doesn't work correctly either. It does return a value, but it's the wrong one. "T. Valko" wrote: Ok, here's one way that the formula can be written... Let's assume that you want the formula entered in cell C4 then copied down. Array entered: =IF(ROWS(C$4:C4)COUNTIF('Daily Activity'! A$1:A$77,B$4),"",INDEX('Daily Activity'!B:B, SMALL(IF('Daily Activity'!A$1:A$77=B$4,ROW ('Daily Activity'!A$1:A$77)),ROWS(C$4:C4)))) -- Biff Microsoft Excel MVP "Flipper" wrote in message ... It should be numeric, but as it is, nothing is being returned. -- Allan "T. Valko" wrote: What type of data is being returned? Is it text, numeric or could it be either? -- Biff Microsoft Excel MVP "Flipper" wrote in message ... I have the following formula in column 4, row 4 of a multi row/column spreadsheet, where I am referencing a value from one worksheet and looking for the same value in an adjacent worksheet. It's basically a VLOOKUP function. =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)),"",INDEX('Da ily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)) The function works as I want it to; however, if I copy the formula exactly as is to say column 6, row 4, and then change the range from A1:a77 to A81:A90, then the formula does not work. I can't figure out why. Can anyone help? Thanks. -- Allan . . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ISERROR and INDEX Funcion question
thanks for the assistance. I did get the formula below to work.
-- Allan "Bob Phillips" wrote: See if this version is amendable =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)-MIN(ROW('Daily Activity'!$A$1:$A$77))+1),ROW(1:$1)),2)),"", INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)-MIN(ROW('Daily Activity'!$A$1:$A$77))+1),ROW(1:$1)),2)) -- HTH Bob "Flipper" wrote in message ... I have the following formula in column 4, row 4 of a multi row/column spreadsheet, where I am referencing a value from one worksheet and looking for the same value in an adjacent worksheet. It's basically a VLOOKUP function. =IF(ISERROR(INDEX('Daily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)),"",INDEX('Da ily Activity'!$A$1:$B$77,SMALL(IF('Daily Activity'!$A$1:$A$77=$B4,ROW('Daily Activity'!$A$1:$A$77)),ROW(1:$1)),2)) The function works as I want it to; however, if I copy the formula exactly as is to say column 6, row 4, and then change the range from A1:a77 to A81:A90, then the formula does not work. I can't figure out why. Can anyone help? Thanks. -- Allan . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| iserror, index & if | Excel Worksheet Functions | |||
| if(iserror()) question | Excel Worksheet Functions | |||
| IF(ISNA.. or ISERROR? question.. | Excel Discussion (Misc queries) | |||
| Iserror question | Excel Worksheet Functions | |||
| #REF,ISERROR, File Not Found question. | Excel Discussion (Misc queries) | |||
Linear Mode
