INDIRECT formula doesn't update automatically from an external worksheet

That is the way Excel is designed. There may be work-arounds but I think we'd need to see the two worksheets in question.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

The only way I can see to get out of this is to use VBA to rewrite the formulas in lieu of INDIRECT. The code could be set up to fire automatically when you change the value in Cell B18. Would you be interested in such a solution?

I made a few changes to the spreadsheet. I moved the value in Q51 to the Loolups page and gave it a name, DARB. If you change the location of the spreadsheet or the name of the spreadsheet, change cell B1 on the Lookups page.

I listed out the months, gave it a name List_Months and used it as data validation for Cell B18 on the Input Page. I also calculated the last date for the selected month and gave it a name EOM_Date.

OK, by now you should get the idea that I like using names They make formulas easier to understand.
Here is more information on names: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges - all these names are static.

You can hide the lookups page if you wish.

I then changed the formulas for the dates so that only dates for the selected months show.

When you select a month in Cell B18, the program generates the formulas in columns C and D. I have no way of testing this, so I need you to inspect the formulas (and check what they yield) to make sure I created the correct formulas.

If you press ALT-F11, it brings up the VB project browser. If you select (Input Page), you will see that there is some "special" code there. This particular code is called a change event. What this code says is that if you change the value of cell B18 run the program MakeFormulas.

Make formulas is in the Module ModMakeFormula - I rename my modules to help me remember what's in them. The subroutine MakeFomulas does, in VBA, what you were doing with the INDIRECT. It makes a string that looks like a formula. In this case, since we assign it to a cell, it becomes an actual formula as if it were typed in.

You can keep the naming conventions for the tabs referenced in row 68 as you have them now. I'l work that into the code / formulas.

I am making an assumption that this is a year-to-date sheet, and that you may want to do the same thing in 2018. I'll add a cell where you can specify the year and that will carry over to all the formulas and replace 2017 wherever it appears (entering 2017, of course will replace 2017 with 2017 - so nothing happens there ).

As for protecting the sheet. I can add code that unprotects it, runs the code and reportects it before the users can get their hands on it. Do you want a password protection on it or are you just protecting the sheet to keep people from doing things accidentally?

Here is version 2.0.

On the lookup sheet, I added a cell to add the year.

There are now two cells that have the string to point to the source file. Be careful with these in that they are not static. They are actually formulas that look at the year and in the case of Row 68, also the selected month.

Once I got these working, I tacked on a couple more lines of code to do row 68 and wrapped the code between statements to unprotect and reprotect the sheet.

I use the macro recorder to get the syntax of how to do somethings. I never did commit to memory how to protect and unprotect a sheet, so I recorded the macro and then modified the statements. The macro recorder is at least that good.

If you have any question about anything I did in the code, just ask.

If you think you will be playing with VBA, the very first thing you should do is arrange to have Option Explicit put on the first row of each module. The instructions on how to do this follow:

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

We cross posted - you changed mmm-yy to the full month name. You can (a) change them back or (b) play with the formula in cell B3 on the lookups sheet. Changing LEFT(Sel_Month,3) to Sel_month should do it.

It's a good thing you made me go back and look at the formula in cell B3. I missed a couple of 2017s.

That sounds odd. If it is going to fail, it should fail even when the source file is open.

Check that the formula I created is valid. Manually create a the formula in the cell below and compare. Also try pressing F9. Let me know if the formulas are different or if F9 makes it happen.