use cell value in a formula

lynx653

lynx653

New member
Joined
Jan 7, 2015
Messages
8
Reaction score
0
Points
0
hi,

here is a formula

='C:\Users\Lynx\Google Drive\mpppm\mpppmP\t2mpppmP\[Modul_Offline_KHB_PERT_T2.xls]BORANG PEREKODAN'!D3

I need to substitute the T2 (in Modul_Offline_KHB_PERT_T2.xls) with a string value in a cell (say it the cell C1)

The C1 shall be T1 or T2 or T3

Help(s) most appreciated ;)
 
Do you mean?



=INDIRECT("'C:\Users\Lynx\Google Drive\mpppm\mpppmP\t2mpppmP\[Modul_Offline_KHB_PERT_" & C1 & ".xls]BORANG PEREKODAN'!D3")
 
INDIRECT with a closed workbook?
 
Good point.... thanks, Bob. I didn't think of that....


.... so basically you can't use the formula I provided if your other workbook is closed.

You can use a PULL function by Harlan Grove (which coincidentally was co-inspired by Bob Phillips :) )... http://numbermonger.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/

which works the same way as my formula, except you need to add the Function to a new module in your VB editor (ALT+F11). Then replace INDIRECT() with PULL()
 
Thanks guys,
I shall look into it.
Will update soon ;)
 
INDIRECT worked flawlessly having the other workbooks open.
 
I managed to paste the PULL function's vba in Module 1 of


mailmerge05.xlsm which is the main file in which the said formula is in B8
 
lynx653 said:
I managed to paste the PULL function's vba in Module 1 of


mailmerge05.xlsm which is the main file in which the said formula is in B8
Click to expand...


... and it worked hopefully ?
 
replacement of

=INDIRECT("'[Modul_Offline_BM_"&C1&".xls]BORANG PEREKODAN'!D3")

with

=PULL("'[Modul_Offline_BM_"&C1&".xls]BORANG PEREKODAN'!D3")

didn't help
 
I didn't use your specific files, but I did test it on my own and it works fine (be it a little slow to respond and return the result).

When you say it didnt't work. What exactly did you get? If you got the #NAME? error, then the function wasn't placed in the right place.
 
NBVC said:
I didn't use your specific files, but I did test it on my own and it works fine (be it a little slow to respond and return the result).

When you say it didnt't work. What exactly did you get? If you got the #NAME? error, then the function wasn't placed in the right place.
Click to expand...


Thank you again...
When I use
=INDIRECT("'[Modul_Offline_BM_" & C1 & ".xls]BORANG PEREKODAN'!D3")
having the other file open I get the desirable result but
when I use =PULL("'[Modul_Offline_BM_" & C1 & ".xls]BORANG PEREKODAN'!D3") with & without having the other file open, I get #REF!
However I am very grateful for the "INDIRECT"
Having other files open shall not be an issue and is advised so that easy to crosscheck on data integrity.
Meanwhile i shall keep trying the "PULL" to further improve my work. Shall deliver first.
 
You have to have the file path in the pull parameter value, the code explicitly looks for a \, that signifies a path.
 
Bob Phillips said:
You have to have the file path in the pull parameter value, the code explicitly looks for a \, that signifies a path.
Click to expand...
Thanks bob, will try & update...

This time INDIRECT didn't work with VLOOKUP :lalala:

=INDIRECT("VLOOKUP($D$13,'[Modul_Offline_BM_"&C1&".xls]PRESTASI UJIAN PENGGAL'!$C$12:$F$500,4,FALSE)") seems impossible...
 
You INDIRECT the range not the function, so VLOOKUP($D$13,INDIRECT(...),4,FALSE)
 
You must log in or register to reply here.
Back
Top