problems with using name of area and name of spreadsheet in formula

[sowwy85]

=INDEX(' Edu'!A:G,MATCH("X"&A2,' Edu'!H:H,0),5)can work
=INDEX(pp,MATCH("X"&A2,pp,0),5) cannot work

pp is the name of the range defined for Col A to G.
Edu is the name of the spreadsheet but why is that using "pp" cant work in this case?what is the difference?

[corinereyes]

Hi sowwy85,

Is all the data in the same sheet?

[sowwy85]

No its not in the same spreadsheet.

I was trying to combine data from 1 spreadsheet to another spreadsheet.

The name of one spreadsheet is named Edu and the other is named HR. I want to put the data in Edu into HR and the defined range of the data in Edu, i named it "pp". Hence, i need to insert data "pp" into the HR spreadsheet.

My question hence is why does the formula which uses the name of the spreadsheet work but when i insert the name of the defined range into the formula, the formula does not work.

=INDEX(' Edu'!A:G,MATCH("X"&A2,' Edu'!H:H,0),5)can work
=INDEX(pp,MATCH("X"&A2,pp,0),5) cannot work

[Richard Schollar]

Hi

You state that range pp is defined as ' Edu'!A:G, but then you use pp to replace the range in your Match formula that was previously:

MATCH("X"&A2,' Edu'!H:H,0)

You see the problem? pp does not contain column H (and also Match needs a single column/row range (or array)).

Maybe if you define pp to be:

' Edu'!A:H

and then use the formula:

=INDEX(pp,MATCH("X"&A2,INDEX(pp,0,8),0),5)

Richard

[sowwy85]

thanks very much for helping to spot my mistake!

CHEERS!