Obtaining named cell from X # of workbooks in the same file path

shakethingsup

Board Regular
Joined
May 21, 2017
Messages
64
Office Version
  1. 365
Platform
  1. Windows
Let's see if I can articulate this and if someone can point me to the write thread. I wanted to see if I can do this on my own this weekend otherwise, I'll get our IT department involved (end of August).

The purpose of this question is to see if I can pull the same named cell value from X number of files in the same file path to create a check list of what's done or what's missing.

1. I have a folder with a variable number of CLOSED excel workbooks: 'C:\Users\mfinm3g\Desktop\Recon'. One month there could be 20 files, and another there could be 25 - due to frequency required.
UOtqjZ1.jpg
gjZyTtI.jpg


2. They will all follow a similar naming convention
1950-8digits-variable name

3. Each file contains the same "named" cell - "Total" on the same sheet.

4. I have a checklist file, Contains all possible files. This checklist can have a variable number of rows. One year it may have 3. The following year 4. In it, it looks like this

Column E pulls the "named" cell - "total" from each closed workbook by creating a file path using A+B+C+cellB1+cellB2

it was able to pull 2 of the files since they exist, but the last was an error. This allows me to follow up.
5jkI6ud.jpg


how can I easily do this?! I'm struggling. I have pieces of the various codes. I just can't piece it together. And some codes open workbooks.


My thoughts as to how to break this problem down
1. use a loop
2. the loop will go through the checklist
3. it will pull the total without opening the workbook
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top