Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index match formula works in for one worksheet reference but not another
Morning
I have a worksheet that will be used to match data from two other worksheets in the same workbook. The matching worksheet goes like this Column A - Names from a HR list =CONCATENATE('All active People Soft'!C2,",",'All active People Soft'!D2,"") Column B - List of names of users from an application =CONCATENATE('All Scrip User by last log in '!E2,",",'All Scrip User by last log in '!D2,"") Column C - A match of names found in both lists =INDEX($A$2:$A$3334,MATCH(B2,$A$2:$A$3334,0),1) Column D - takes the match found in Column C and goes back to the HR list and finds the "manager" (located in the 8th column of that worksheet) in the indexed range - If there is no match it returns "Not in PS" =IF(ISERROR('Scrip To PS Match'!C2),"Not In PS ",INDEX('All active People Soft'!$A$2:$R$3334,MATCH('Scrip To PS Match'!C2,'Scrip To PS Match'!$A$2:$A$3334,0),8)) That all works great - now the issue is that when I formated the fomula to look at the second worksheet. It opens up windows dialog box looking for file to find the link. (update values windows) - it seems to create a link. The formula I want to use looks like this ( for this instace the user id is also the 8th column in the sheet) =IF(ISERROR('Scrip To PS Match'!C2),"Not In PS ",INDEX('All Scrip User by last log in'!$A$2:$Z$3334,MATCH('Scrip To PS Match'!C2,'All Scrip User by last log in'!$A$2:$A$3334,0),8)) Once I get the error and either cancel or even link it a file the formula gets changed to this: =IF(ISERROR('Scrip To PS Match'!C2),"Not In PS ",INDEX('[All Scrip User by last log in]All Scrip User by last log in'!$A$2:$Z$3334,MATCH('Scrip To PS Match'!C2,'[All Scrip User by last log in]All Scrip User by last log in'!$A$2:$A$3334,0),8)) I am guessing that is a reference to a file -- hope someone has ran into this before |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Index/ Match Formula | Excel Worksheet Functions | |||
two worksheet reference formula help | Excel Worksheet Functions | |||
INDEX MATCH formula | Excel Worksheet Functions | |||
Worksheet name / reference as a formula? | Excel Discussion (Misc queries) |