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

Index match formula works in for one worksheet reference but not a
 

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

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