Pulling Data From Another Workbook

Question

Hello,

I am not sure how to go about this.  I need to pull in data from another workbook but the only issue is that each month the other workbook will be labeled differently.  So for example, the other workbook will be labeled Master file 01312022, then the next month the file will be labeled Master file 02282022 and so on.  The file will contain different values/data in each month's file and will always pull from the same column and row locations.  My question is how can I pull data into the excel file each month from the newly named workbook? Currently, I have a sumif formula which contains the folder location/name of the file (see below). For next month, how can I avoid having to type in 02282022 into each cell to update the formula. 

Current location and name for January --> 'S:\Revenue Forecasting[OFFICIAL FACILITY MASTER 01122022.xlsx]

Next months file name -->'S:\Revenue Forecasting[OFFICIAL FACILITY MASTER 02282022.xlsx]

=SUMIFS('S:\Revenue Forecasting[OFFICIAL FACILITY MASTER 01122022.xlsx]MASTER (NEW)'!JV$6:JV$300,'S:\Revenue Forecasting[OFFICIAL FACILITY MASTER 01122022.xlsx]MASTER (NEW)'!$IZ$6:$IZ$300,$B6,'S:\Revenue Forecasting[OFFICIAL FACILITY MASTER 01122022.xlsx]MASTER (NEW)'!$JC$6:$JC$300,"Tokyo")

Answer 1

Hi,

I tried to recreate Your situation as follows:

i. Created a fictional dataset - worksheet Data.

ii. Added 2 worksheets - worksheet Tokyo and worksheet Bengaluru.

In these 2 worksheets > added SUMIFS formula.

- Formula in worksheet Tokyo is: =SUMIFS(Data!D2:D101,Data!B2:B101,"A",Data!C2:C101,"Tokyo")

- Formula in worksheet Bengaluru is: =SUMIFS(Data!D2:D101,Data!B2:B101,"A",Data!C2:C101,"Bengaluru")

Next, I used Find & Replace to modify the SUMIFS formula in 2 worksheets (the bold part in the formulas above) > Find & Replace did it successfully.

If You require further assistance:

- Please share sample workbook (via OneDrive, Dropbox, etc.).

OR

- Please share screenshots of Your situation/question (screenshots should include column & row headers) + relevant information to enable the Community Members to understand Your situation completely & accurately.

If You need help with how to upload the file, please click on this link: support.office.com

OR

Please follow the instructions in this video: https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Thank You!

Answer 2

Hello,

I tried Option 1 find and replace but it looks like it is only updating the first cell it finds the formula within the sheet and not all cells even when i click on "replace all". I selected Within "Workbook" as suggested but that did not work.

Not sure what i am doing wrong.

Note: when I click on Replace all, the open files pop up appears and then I have to click on cancel.

Answer 3

Hi,

In My understanding, there are 3 options:

Option 1) Every month > when the file changes > please use Find & Replace to edit the worksheet name in the formulas > Replace All option in Find & Replace will edit all formulas in the worksheet or workbook in 1 click.

- Please ensure the settings of Find and Replace box is as illustrated in the following screenshot:

e.g.1 for Look in option in Find and Replace box - Formulas is selected.

e.g.2 Match case option in Find and Replace box is unchecked.

. . . and other settings.

- In Within option, please change Sheet to Workbook if formulas reside in multiple worksheets.

Option 2) PowerQuery

Option 3) VBA

In My humble opinion, if You are not familiar with PowerQuery and VBA, I will recommend You to try Find & Replace.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!