+ Reply to Thread
Results 1 to 8 of 8

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

  1. 03-25-2024, 12:36 PM #1
    Sean Goldie
    Sean Goldie is offline
    Registered User
    Join Date
    03-25-2024
    Location
    South Africa
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2403 Build 16.0.17425.20058) 64-bit
    Posts
    4

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

    Hi Good day. I have a consolidated monthly spreadsheet that is pulling data from the daily spreadsheets for that month but I have to manually adjust the dates in the consolidated sheet to pull the correct information in. It requires me going through many columns and changing the date to correlate with the correct daily sheet.

    Current my formulas are as follows

    ='U:\Bookkeeper\DTS\2024\APRIL 2024\[01-04-2024.xlsx]00'!$D$9

    and I want to know if there is an easy way to copy the formula down in a column and just increment the date only so the next cell down would be

    ='U:\Bookkeeper\DTS\2024\APRIL 2024\[02-04-2024.xlsx]00'!$D$9

    and the next cell down would be

    ='U:\Bookkeeper\DTS\2024\APRIL 2024\[03-04-2024.xlsx]00'!$D$9

    As you can see it is only the date that is needed to change in each cell but maintaining the rest of the formula without change as the consolidated sheet pulls data from the same cell in each daily sheet. Hope this makes sense and that someone can assist with the solution.
    Reply With QuoteRegister To Reply
  2. 03-25-2024, 12:42 PM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,939

    Re: Help with consolidated sheet pulling data from daily sheets & formula date incrementin

    Welcome to the forum.

    Would this be just for one calendar month?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 03-25-2024, 12:59 PM #3
    Sean Goldie
    Sean Goldie is offline
    Registered User
    Join Date
    03-25-2024
    Location
    South Africa
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2403 Build 16.0.17425.20058) 64-bit
    Posts
    4

    Re: Help with consolidated sheet pulling data from daily sheets & formula date incrementin

    Hi there. 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.
    Reply With QuoteRegister To Reply
  4. 03-25-2024, 01:00 PM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,939

    Re: Help with consolidated sheet pulling data from daily sheets & formula date incrementin

    That's not quite what I meant. In any one sheet, will it just be one calendar month?
    Reply With QuoteRegister To Reply
  5. 03-25-2024, 01:02 PM #5
    Sean Goldie
    Sean Goldie is offline
    Registered User
    Join Date
    03-25-2024
    Location
    South Africa
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2403 Build 16.0.17425.20058) 64-bit
    Posts
    4

    Re: Help with consolidated sheet pulling data from daily sheets & formula date incrementin

    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.
    Last edited by Sean Goldie; 03-25-2024 at 01:07 PM.
    Reply With QuoteRegister To Reply
  6. 03-25-2024, 01:12 PM #6
    Sean Goldie
    Sean Goldie is offline
    Registered User
    Join Date
    03-25-2024
    Location
    South Africa
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2403 Build 16.0.17425.20058) 64-bit
    Posts
    4

    Re: Help with consolidated sheet pulling data from daily sheets & formula date incrementin

    Hi, the consolidated sheet will have a full calendar month.
    Reply With QuoteRegister To Reply
  7. 03-25-2024, 01:13 PM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,939

    Re: Help with consolidated sheet pulling data from daily sheets & formula date incrementin

    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.
    Reply With QuoteRegister To Reply
  8. 03-25-2024, 01:28 PM #8
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,939

    Re: Help with consolidated sheet pulling data from daily sheets & formula date incrementin

    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.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy data from multiple sheets to a consolidated sheet.
    By kayslover in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-27-2021, 08:48 AM
  2. Combined all data from 4 sheets to another sheet as consolidated
    By tariqnaz2005 in forum Excel General
    Replies: 6
    Last Post: 08-30-2020, 03:51 PM
  3. Pulling Data from Multiple workbooks to one consolidated sheet
    By Stokess in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-31-2018, 06:03 PM
  4. [SOLVED] Pulling data together from several sheets for a given date to a another sheet
    By sipa in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2014, 11:57 AM
  5. Column of Consolidated Running Totals from support sheets that change daily
    By santa6 in forum Excel Formulas & Functions
    Replies: 29
    Last Post: 03-17-2014, 06:54 PM
  6. [SOLVED] Date in Consolidated sheet does not match date in sheets being consolidated
    By Lowtech in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-15-2012, 07:23 PM
  7. Pulling data from one sheet and updating certain cells daily
    By myshadeofglory in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-12-2011, 06:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1