Indirect Help

[davehazle]

Is it possible to use the Indirect funstion to pull,not necessarily from another file, but to dictate the worksheet within that file to pull from?

Let me exlain:
=VLOOKUP('[CREDIT GAP.xls]12.01.05'!$A2,'[CREDIT GAP.xls]12.01.05'!$A$2:$E$71,1,FALSE)

This formula resides on my master report page. In the above formula, credit gap.xls is a seperate file. There are dated worksheets across the bottom of the credit gap file. On my master page I have the date I plan on currently working with in cell F1. Can Indirect be used, not to access the credit gap file, but to change the date (or worksheet) my Vlookup is pointing to within the credit gap file?



Much apreciated.

 

[WillR]

INDIRECT can be used, but the target workbook needs to be open.

 

[just_jon]

Is it possible to use the Indirect funstion to pull,not necessarily from another file, but to dictate the worksheet within that file to pull from?

Let me exlain:
=VLOOKUP('[CREDIT GAP.xls]12.01.05'!$A2,'[CREDIT GAP.xls]12.01.05'!$A$2:$E$71,1,FALSE)

This formula resides on my master report page. In the above formula, credit gap.xls is a seperate file. There are dated worksheets across the bottom of the credit gap file. On my master page I have the date I plan on currently working with in cell F1. Can Indirect be used, not to access the credit gap file, but to change the date (or worksheet) my Vlookup is pointing to within the credit gap file?

If furtehr explination is required, fastest contact is 908-221-1490


Much apreciated.

Yes, but keep in mind credit gap MUST be open.

=VLOOKUP(INDIRECT("'[CREDIT GAP.xls]" & F1 &
"'!$A2"),INDIRECT("'[CREDIT GAP.xls]" & F1 & "'!$A$2:$E$71"),1,FALSE)

But with a column of 1 aren't you just returning A2?

ALSO, best to delete that phone number.

 

[davehazle]

I have read the beginings of a workaround for having the Credit GAP file opened. Would this workaround apply in my situation? I ask this because the date in this formula:
=VLOOKUP('[CREDIT GAP.xls]12.01.05'!$A2,'[CREDIT GAP.xls]12.01.05'!$A$2:$E$71,1,FALSE)

Needs to be changed in roughly 20 column each week, and obviously twice per column.

 

[just_jon]

I have read the beginings of a workaround for having the Credit GAP file opened. Would this workaround apply in my situation? I ask this because the date in this formula:
=VLOOKUP('[CREDIT GAP.xls]12.01.05'!$A2,'[CREDIT GAP.xls]12.01.05'!$A$2:$E$71,1,FALSE)

Needs to be changed in roughly 20 column each week, and obviously twice per column.

What is the purpose of this formula?

As it stands, you are ALWAYS going to find A2 in A2 -- you are looking a value from your search array.

 

[davehazle]

Oh, I understand what you are asking, Also part of this sheet are colums as follows.

=VLOOKUP('[PC GAP.xls]12.01.05'!$A2,'[PC GAP.xls]12.01.05'!$A$2:$E$71,2,FALSE)

=VLOOKUP('[PC GAP.xls]12.01.05'!$A2,'[PC GAP.xls]12.01.05'!$A$2:$E$71,5,FALSE)

=VLOOKUP('[PC GAP.xls]12.01.05'!$A2,'[PC GAP.xls]12.01.05'!$A$2:$E$71,4,FALSE)

Essentialy, I am just looking up values that change each week in the current weeks table for a bunch of columns.

 

[davehazle]

so for the first column, i supose i could just reference the actual cell value, but it just seemed natural to work with the same vlookup formula I had been using for the others.

 

[just_jon]

Oh, I understand what you are asking, Also part of this sheet are colums as follows.

=VLOOKUP('[PC GAP.xls]12.01.05'!$A2,'[PC GAP.xls]12.01.05'!$A$2:$E$71,2,FALSE)

=VLOOKUP('[PC GAP.xls]12.01.05'!$A2,'[PC GAP.xls]12.01.05'!$A$2:$E$71,5,FALSE)

=VLOOKUP('[PC GAP.xls]12.01.05'!$A2,'[PC GAP.xls]12.01.05'!$A$2:$E$71,4,FALSE)

Essentialy, I am just looking up values that change each week in the current weeks table for a bunch of columns.

So you're really importing a table from varying sheets in another file then?

 

[davehazle]

yes, various portions of a table, but the table changes each week (changes are made on a new worksheet titled with the weeks name 10.03.05, 10.10.05 , 12.01.05 , ect...)

 

[just_jon]

Do this instead

='[PC GAP.xls]12.01.05'!A2

Copy over/down as needed.

When you want to change the sheet name, then select all cells and click

Edit | Find

then select Formulas in the Look in box, make sure Find entire cells only is not checked.

Enter 12.01.05 in the find what box then Click Replace.

Enter the new sheet name in the Replace with box and click replace all.