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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.