Use a cell as reference to sheet to look in

    I have an excel formula that looks like this....


    ='S:\Sales\Level 1\Daily Reporting\Daily Progress Report\[Bethel Daily Report - November 2017.xlsx]011117'!$M$16


    But the word "Bethel" needs to be taken from a different cell B3, as it will vary each time.
    Same with the phrase "November 2017" which comes from cell C4.



    How can I automate this?

    I'm a novice to formulas after working hard to get a small grasp on vba macros. I wanted to respond to see if I can help start the discussion with an idea because I've been dealing with similar formula issues as of late.


    Without more environment/logistics details, I believe this is not solvable as you're requesting. Embedding a variable path inside a formula needing the same variable path is somewhat of an unsolvable infinite loop. I assume that you have to embed the variables on an active sheet where the formula can access them initially.


    Then, assuming I have the single and double quotes in correct order, which is no guarantee, you might be able to use something like this:


    Code
    ='S:\Sales\Level 1\Daily Reporting\Daily Progress Report\["'"&'activesheet'!$B$3&"' Daily Report - '"&'activesheet'!$C$4&"'.xlsx"]011117'!$M$16

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login