Index match formula works in for one worksheet reference but not a
Allen
thanks - I made the change and it still did not work turns out that I
was referencing the incorrect column. So Column A and B both take
individual names from the other sheets and C match them up.
The first formula references column A because it is looking for
information from the HR list.
MATCH('Scrip To PS Match'!C2,'Scrip To PS Match'!$A$2:$A$3334,0),8))
The second formula (yup got caugth for cutting and pasting) was never
updated to reference column B with is the reference to the application
list .
MATCH('Scrip To PS Match'!C2,'Scrip To PS Match'!$B$2:$B$630,0),8))
Thanks for your help
Eddie
Allllen wrote:
swap
'All Scrip User by last log in '
for
'All Scrip User by last log in'
That space at the end is all important. You have told excel to look for a
file with a different name to the one you think.
--
Allllen
" wrote:
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
|