Lookup function Closed files Excel
 

Hi,
I have many different spreadsheets with large amounts of bank
information in, too large to group all in one neat file, so I have
created a seperate file (call this test1) that takes certain data from
each of the many files. To do this without having to keep them all
open, I have used Insert-Name-Define and defined the area of data in
each file seperately (eg. as data1, data2 etc).
As I have over 200 files I have had to do this many times. Now what I
want to do in test1 is type in the name of the bank and what
information I want (eg. Total Assets) and I want the formula to look
for the correct file and lookup the Total Assets of the specified bank.

This seems impossible to do using the following formula, without having

to name each column and row in each file as something different. With
200 files that would mean having 600 seperately defined areas.

=INDEX(Data,MATCH(Peersheet!B$3,DataC1,0),MATCH(Pe ersheet!$A4,DataR1,0))

- where DataC1 is the column A and DataR1 is the row 1.


Unfortunately for some reason I can't replace the DataC1 or DataR1
simply with A:A or 1:1, it just won't work. Is there any way around
this?
I hope this all makes sense. Thank you,
Simon

Lookup function Closed files Excel
 

Instead of DataR1 and DataRC, try INDEX(Data,1,0) and INDEX(Data,0,1)

wrote:

Hi,
I have many different spreadsheets with large amounts of bank
information in, too large to group all in one neat file, so I have
created a seperate file (call this test1) that takes certain data from
each of the many files. To do this without having to keep them all
open, I have used Insert-Name-Define and defined the area of data in
each file seperately (eg. as data1, data2 etc).
As I have over 200 files I have had to do this many times. Now what I
want to do in test1 is type in the name of the bank and what
information I want (eg. Total Assets) and I want the formula to look
for the correct file and lookup the Total Assets of the specified bank.

This seems impossible to do using the following formula, without having

to name each column and row in each file as something different. With
200 files that would mean having 600 seperately defined areas.

=INDEX(Data,MATCH(Peersheet!B$3,DataC1,0),MATCH(Pe ersheet!$A4,DataR1,0))

- where DataC1 is the column A and DataR1 is the row 1.


Unfortunately for some reason I can't replace the DataC1 or DataR1
simply with A:A or 1:1, it just won't work. Is there any way around
this?
I hope this all makes sense. Thank you,
Simon

Lookup function Closed files Excel
 

Instead of DataR1 and DataC1, try INDEX(Data,1,0) and INDEX(Data,0,1)

wrote:

Hi,
I have many different spreadsheets with large amounts of bank
information in, too large to group all in one neat file, so I have
created a seperate file (call this test1) that takes certain data from
each of the many files. To do this without having to keep them all
open, I have used Insert-Name-Define and defined the area of data in
each file seperately (eg. as data1, data2 etc).
As I have over 200 files I have had to do this many times. Now what I
want to do in test1 is type in the name of the bank and what
information I want (eg. Total Assets) and I want the formula to look
for the correct file and lookup the Total Assets of the specified bank.

This seems impossible to do using the following formula, without having

to name each column and row in each file as something different. With
200 files that would mean having 600 seperately defined areas.

=INDEX(Data,MATCH(Peersheet!B$3,DataC1,0),MATCH(Pe ersheet!$A4,DataR1,0))

- where DataC1 is the column A and DataR1 is the row 1.


Unfortunately for some reason I can't replace the DataC1 or DataR1
simply with A:A or 1:1, it just won't work. Is there any way around
this?
I hope this all makes sense. Thank you,
Simon

Lookup function Closed files Excel
 

That's great thanks Lori! Works a treat, many thanks!

Lori wrote:

Instead of DataR1 and DataC1, try INDEX(Data,1,0) and INDEX(Data,0,1)

wrote:

Hi,
I have many different spreadsheets with large amounts of bank
information in, too large to group all in one neat file, so I have
created a seperate file (call this test1) that takes certain data from
each of the many files. To do this without having to keep them all
open, I have used Insert-Name-Define and defined the area of data in
each file seperately (eg. as data1, data2 etc).
As I have over 200 files I have had to do this many times. Now what I
want to do in test1 is type in the name of the bank and what
information I want (eg. Total Assets) and I want the formula to look
for the correct file and lookup the Total Assets of the specified bank.

This seems impossible to do using the following formula, without having

to name each column and row in each file as something different. With
200 files that would mean having 600 seperately defined areas.

=INDEX(Data,MATCH(Peersheet!B$3,DataC1,0),MATCH(Pe ersheet!$A4,DataR1,0))

- where DataC1 is the column A and DataR1 is the row 1.


Unfortunately for some reason I can't replace the DataC1 or DataR1
simply with A:A or 1:1, it just won't work. Is there any way around
this?
I hope this all makes sense. Thank you,
Simon