Jul 18 2019 02:09 PM
This formula is used many times in the worksheet so I want to replace the [Filename.xls] with a cell containing the filename. That way when I reference a different workbook I only have to change the filename once instead of many times.
I have [Filename.XLS] in cell K1 and I have [Filename.XLS]SHEET1 in cell K2.
I need the =IF formula to get the filename from cell K1 or the filename and sheet name from cell K2 (either way works fine for me) and index that. I've been trying to use variations of =INDIRECT(""&$K$2&"), but can't make it work either way. Can someone please tell me how to do this?
Thanks for your help.
Jul 18 2019 02:35 PM
Hi
To extract the File name in a Cell, Say A1, type
=Cell("filename")
To use it in a formula say in A2 I just want to keep the Sheet name, Type in A2
=REPLACE(A1,1,FIND("]",A1),"")
Hope that helps
Nabil Mourad