Variable Cell Reference based on Path

  • I need to pull values from about 80 excel files that are all in the same folder and named the exact same thing except for a datefield. Luckily, the value I need in each workbook is also in the exact same Cell.


    So I am hoping that I can quickly pull all the data I need using this Formula:

    Code
    ='[MRR Operational Reporting V1_05092018.xlsx]Goal Worksheet'!$X$6


    except the date 05092018 is different for each file.


    I have the date parsed out this way in the A column (A2 = 02072018, A3 = 02082018, A4 = 02092018, etc).


    What would the formula be so that I can use the A Column cells as a variable in the above formula?

  • Hello,


    Not sure to fully understand your question ...


    May be Indirect() ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello,


    Not sure to fully understand your question ...


    May be Indirect() ...


    Ok, so I'm going to give a formula that doesn't work just so you understand what I'm TRYING to accomplish:



    I want to turn the date portion of the path... which is 05092018 in the below formula...


    Code
    [align=left][COLOR=#252C2F][FONT=Courier][SIZE=12px]='[MRR Operational Reporting V1_05092018.xlsx]Goal Worksheet'!$X$6[/SIZE][/FONT][/COLOR][/align]


    into a variable based on the A column like this...


    Code
    [align=left][COLOR=#252C2F][FONT=Courier][SIZE=12px]='[MRR Operational Reporting V1_" & A2 & ".xlsx]Goal Worksheet'!$X$6[/SIZE][/FONT][/COLOR][/align]


    again, the way I inserted the variable doesn't work... it was just to show you what I'm trying to do.

  • Thanks for the clarification...


    The only remaining point is the exact format of your data in Column A ...


    If you have actual dates, you would need to convert these numbers back to strings ...


    Code
    ='[MRR Operational Reporting V1_" & Cstr(Cells(i,1).Value) & ".xlsx]Goal Worksheet'!$X$6


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)


  • This is a sheet formula, not VBA

  • This is a sheet formula, not VBA


    Sorry to disappoint you ...


    the proposed expression you are looking for

    Code
    Cstr(Cells(i,1).Value)

    is ... VBA ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Sorry for misunderstanding ... :wink:


    Thought you needed a VBA solution ...


    Since you need a worksheet formula ...


    You have to be 100 % sure your Column A is filled with Text ... NOT Actual Dates ...


    if so ... you can test following formula:


    Code
    =Indirect("'[MRR Operational Reporting V1_"&A2&".xlsx]Goal Worksheet'!$X$6")


    If you happen to have actual dates in Column A ... an additional manipulation will be required ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)


  • It didn't work because the workbooks are all closed... after doing more research, I think I'm up the creek without means of transportation

  • Shame you did not clearly explain your objective in your very first message ... :wink:



    John Walkenback has an excellent trick to Extract a Cell Value from a Closed Workbook ...WITHOUT Opening it ...



    http://spreadsheetpage.com/ind…value_from_a_closed_file/


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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