| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
External reference not working?
(Excel 2003) Using INDEX, SMALL, and ROW functions in a formula per the
examples in artical "How to look up a value in a list and return multiple corresponding values". When I use the example verbatim, everything works fine. But when I change the parameter for the comparison value to retrieve from a different sheet in the same workbook, the formula no longer finds a match. example: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) changed to: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=TL! $C$2,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$ B$7,SMALL(IF($A$1:$A$7=TL!$C$2,ROW($A$1:$A$7)),ROW (1:1)),2)) The value in the referenced cell is identical to the value in the local cell. What am I doing wrong? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
External reference not working?
Hi,
May be you should put 'TL'! instead TL! Thanks, -- Farhad Hodjat "Todd Lietha" wrote: (Excel 2003) Using INDEX, SMALL, and ROW functions in a formula per the examples in artical "How to look up a value in a list and return multiple corresponding values". When I use the example verbatim, everything works fine. But when I change the parameter for the comparison value to retrieve from a different sheet in the same workbook, the formula no longer finds a match. example: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) changed to: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=TL! $C$2,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$ B$7,SMALL(IF($A$1:$A$7=TL!$C$2,ROW($A$1:$A$7)),ROW (1:1)),2)) The value in the referenced cell is identical to the value in the local cell. What am I doing wrong? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
External reference not working?
While it is always a good design to add the apostrophes regardless it is not
necessary if there are no spaces in the sheet name -- Regards, Peo Sjoblom "Farhad" wrote in message ... Hi, May be you should put 'TL'! instead TL! Thanks, -- Farhad Hodjat "Todd Lietha" wrote: (Excel 2003) Using INDEX, SMALL, and ROW functions in a formula per the examples in artical "How to look up a value in a list and return multiple corresponding values". When I use the example verbatim, everything works fine. But when I change the parameter for the comparison value to retrieve from a different sheet in the same workbook, the formula no longer finds a match. example: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) changed to: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=TL! $C$2,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$ B$7,SMALL(IF($A$1:$A$7=TL!$C$2,ROW($A$1:$A$7)),ROW (1:1)),2)) The value in the referenced cell is identical to the value in the local cell. What am I doing wrong? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
External reference not working?
The formula uses an array; reset the array using F2 followed by (Ctrl +
Shift)+Enter. That should restore your functionality. -- Kat "Todd Lietha" wrote: (Excel 2003) Using INDEX, SMALL, and ROW functions in a formula per the examples in artical "How to look up a value in a list and return multiple corresponding values". When I use the example verbatim, everything works fine. But when I change the parameter for the comparison value to retrieve from a different sheet in the same workbook, the formula no longer finds a match. example: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) changed to: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=TL! $C$2,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$ B$7,SMALL(IF($A$1:$A$7=TL!$C$2,ROW($A$1:$A$7)),ROW (1:1)),2)) The value in the referenced cell is identical to the value in the local cell. What am I doing wrong? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
External reference not working?
That was it. Thank you much!
"Kat" wrote: The formula uses an array; reset the array using F2 followed by (Ctrl + Shift)+Enter. That should restore your functionality. -- Kat "Todd Lietha" wrote: (Excel 2003) Using INDEX, SMALL, and ROW functions in a formula per the examples in artical "How to look up a value in a list and return multiple corresponding values". When I use the example verbatim, everything works fine. But when I change the parameter for the comparison value to retrieve from a different sheet in the same workbook, the formula no longer finds a match. example: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) changed to: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=TL! $C$2,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$ B$7,SMALL(IF($A$1:$A$7=TL!$C$2,ROW($A$1:$A$7)),ROW (1:1)),2)) The value in the referenced cell is identical to the value in the local cell. What am I doing wrong? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
External reference not working?
You are most welcome!
-- Kat "Todd Lietha" wrote: That was it. Thank you much! "Kat" wrote: The formula uses an array; reset the array using F2 followed by (Ctrl + Shift)+Enter. That should restore your functionality. -- Kat "Todd Lietha" wrote: (Excel 2003) Using INDEX, SMALL, and ROW functions in a formula per the examples in artical "How to look up a value in a list and return multiple corresponding values". When I use the example verbatim, everything works fine. But when I change the parameter for the comparison value to retrieve from a different sheet in the same workbook, the formula no longer finds a match. example: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) changed to: =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=TL! $C$2,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$ B$7,SMALL(IF($A$1:$A$7=TL!$C$2,ROW($A$1:$A$7)),ROW (1:1)),2)) The value in the referenced cell is identical to the value in the local cell. What am I doing wrong? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| External Data Refresh not working | Excel Discussion (Misc queries) | |||
| External Links not working | Excel Discussion (Misc queries) | |||
| Working With Pivot Table And External DB | Excel Discussion (Misc queries) | |||
| External Links not working | Links and Linking in Excel | |||
| External References not working right. | Excel Worksheet Functions | |||
Hybrid Mode
