Help with consolidated sheet pulling data from daily sheets & formula date incrementing

Welcome to the forum.

Would this be just for one calendar month?

That's not quite what I meant. In any one sheet, will it just be one calendar month?

Hi AliGW

Thank you for the warm reception. I am glad I found this forum. It will be a great help moving forward.

I would be using a consolidated sheet for each month which would be linked to each daily sheet in that specific month. SO I will have 12 consolidated months files
So short of me having to change each cells formula with the incremented date I was hoping there is an easier way.

I have uploaded samples but not sure how to show the attachement here.

You've just repeated what you already told me.

So, will all of the April 2024 URLs be on one sheet with no other month?

Just a 'yes' or 'no', please.

Try this:

=LET(d,DATE(A1,MONTH(DATEVALUE(A2&" 1")),1),l,DAY(EOMONTH(d,0)),"'U:\Bookkeeper\DTS\2024\APRIL 2024\["&TEXT(SEQUENCE(l,,d,1),"dd-mm-yyyy")&".xlsx]00'!$D$9")

It uses the data in cells A1 and A2 - see if you can make it work for you.

Well, that's probably because you failed to answer my questions and it had to be a guess.

Happy to look again, but ONLY if you answer my questiins first AND explain in DETAIL what is 'not working'.

please bear with me as I am not as advanced as you when it comes to microsoft excel.

You don't need to be advanced in Excel to answer questions, which, by the way, you still haven't. If you want help, you need to do so. I'll have another look.

So, you are going to need to test this for yourself.

Remove ALL values from cells F6 to F36, then paste this into F6 and hit ENTER.

=IFERROR(INDIRECT("'C:\Bookkeeper\DTS\2024\APRIL 2024\["&TEXT(DATE(RIGHT(F1,4),MONTH(DATEVALUE(TEXTBEFORE(SUBSTITUTE(F1,"'","")," ")&" 1")),B6:B36),"dd-mm-yyyy")&".xlsx]00'!$E$6"),"")

Does it produce the correct results? If not, what is not working and in what way? Be very specific. If you aren't, then I shall find it very difficult to assist further, as I'll be stabbing in the dark (again).

If it DOES work, I can explain how to adapt it for other columns.

In what way is it not working? Is the result wrong? Is it failing to pull anything at all? Are the source sheets in the correct location (on drive C)?

I know what it's supposed to do - there's no need to repeat that. What I need to try to work out is why it's failing, so concentrate don that, please, in your responses.

OK - I have tested this and it WORKS.

Please paste this into F6 and drag copy down:

=IFERROR(INDIRECT("'C:\Bookkeeper\DTS\2024\APRIL 2024\["&TEXT(DATE(RIGHT(F1,4),MONTH(DATEVALUE(TEXTBEFORE(SUBSTITUTE(F1,"'","")," ")&" 1")),B6),"dd-mm-yyyy")&".xlsx]00'!$E$6"),"")

The source workbook needs to be open. If this isn't what you want, then I think you'll need to look at VBA.

No, that's my error. Please change the formula to this and copy down:

=IFERROR(INDIRECT("'C:\Bookkeeper\DTS\2024\APRIL 2024\["&TEXT(DATE(RIGHT(F1,4),MONTH(DATEVALUE(TEXTBEFORE(SUBSTITUTE($F$1,"'","")," ")&" 1")),B6),"dd-mm-yyyy")&".xlsx]00'!$E$6"),"")

Is this now working?