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:

='[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?

Quote from Carim

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...

[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...

[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.

Quote from Carim

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

Display More

This is a sheet formula, not VBA

Quote from Carim

Sorry to disappoint you ...

the proposed expression you are looking for

Code

Cstr(Cells(i,1).Value)

is ... VBA ... :wink:

I know, I meant that I need it in Sheet Formula... not VBA

Quote from Carim

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

Display More

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