PDA

View Full Version : Solved: Convert with INDIRECT function



anandbohra
10-15-2007, 12:10 AM
Hi All pl somebody can convert this into indirect function for me.

=VLOOKUP(K2,'D:\Documents and Settings\admin\Desktop\Technical Reports\[26 - 50 Stocks - Nifty.xls]ZEEL'!$B:$H,5,0) &" " &VLOOKUP(K2,'D:\Documents and Settings\admin\Desktop\Technical Reports\[26 - 50 Stocks - Nifty.xls]ZEEL'!$B:$H,7,0)

In this formula Zeel will be by Changing value which i want to take from range A1

like say normal formula is =ABB!F1 then its indirect formula will be
=Indirect("'"&A1&"'!F1") (here A1 contains word ABB

pl reply

matthewspatrick
10-15-2007, 08:44 AM
You are aware that INDIRECT cannot resolve a reference to another
workbook unless that other workbook is open, right?

anandbohra
10-15-2007, 10:47 PM
was not aware about this limitation of INDIRECT function
& today only come to know that even OFFSET Function can not make reference to closed workbook.

ok thanks for the help now atleast i can stop searching for INDIRECT function help no more for my query

Thanking You.
:friends::friends:

p45cal
10-16-2007, 04:33 AM
This vba (macro) line will put your formula correctly in cell C5, using whatever's in cell A1:
range("C5").Formula = "=VLOOKUP(K2,'D:\Documents and Settings\admin\Desktop\Technical Reports\[26 - 50 Stocks - Nifty.xls]" & range("A1") & "'!$B:$H,5,0) & VLOOKUP(K2,'D:\Documents and Settings\admin\Desktop\Technical Reports\[26 - 50 Stocks - Nifty.xls]" & range("A1") & "'!$B:$H,7,0)"
If you put lines like this into a worksheet_change event which then only does something when cell A1 has changed you'll be able to get your information from the closed workbook.

I've attached a file which demonstrates this.

You'd think that if a formula in a cell on a worksheet can get info from a closed workbook, that VBA would too - not the case, I think, although 'evaluate' might do it.

anandbohra
10-16-2007, 04:43 AM
Thanks p45cal

nice solution :clap::clap::clap::clap::clap: