+ Reply to Thread
Results 1 to 13 of 13

Auto Update, Daily Data

  1. #1
    Registered User
    Join Date
    03-04-2018
    Location
    uk
    MS-Off Ver
    office365
    Posts
    7

    Cool Auto Update, Daily Data

    Hey guys,

    Reasonably new to Excell, but think i hve a pretty solid basic understanding.

    I'm looking to create a file where i can paste a new days worth of data in every day and pull specific parts out of it for the sheet im trying to make.

    I want to create a sheet that will update relevant to what day of the week it is. ( I Want To Send An Update Every Day - Week To Date ) e.g. if it is Friday i want the data to be pulled up until thursday (as friday, business hasnt closed yet) and for the fri,sat,sun cells to remain blank etc.

    I need to report to pull data from a 28 date period, but from a possible 31 different sections of data.

    I need to have the individual 28 days of data, plus a section for the combined 28 days, Week to date (Monday to current day) and Last week to same day (Monday to same day last week that yesterday was, if today is friday, to be last week monday until thursday)

    Id like it so i can literally just copy and paste todays data and it would auto populate the cells with the correct info etc.

    i dont know if id need to have like 31 sections to paste the 28 days and the 3 multi day reports to, then i just update the 3 multi day reports manually and copy and paste a new days data and the old 28th day would drop off (i delete it) or if it would be better to have like a rolling template that with every day that passes, the information that the cells take from move down to a new day ( so that after 500 days etc, id have 500 days of pasted data, and it just keep rolling on etc.

    I hope this makes some sort of sense? its really hard to try and explain what im looking for.

    Thanks in advance for you help.


    Peace,

    Milk

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Auto Update, Daily Data

    Hi Milk and welcome to the forum

    Quote Originally Posted by MilkMyDragon View Post
    I hope this makes some sort of sense? its really hard to try and explain what im looking for.
    How about we start with you showing us what you mean rather than trying to explain in words alone?

    Upload a sample workbook that shows your data layout and what you want the output to look like.

    I will say that when reporting like this it's 99.99% of the time better to have all the data in one list rather than in different "sections".
    If you need sub data sets it's easier to make those from one big data set than it is to make a big data set from lots of smaller ones.
    It just makes life easier! And unless you'd pasting in many many thousands of rows of data per day, the 1mil+ rows of data Excel currently allows should be more than enough to keep you going for a long time to come.

    BSB

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Auto Update, Daily Data

    Hi Milk and welcome to the forum,

    Excel has a newer tool called Get & Transform that will do what I think you want. You would simply add a daily file into a folder and then open the workbook that has the code in it and refresh the data. See it explained at:

    http://excelunplugged.com/2015/02/10...n-power-query/

    If you build the Power Query (Get & Transform in 2016) it will use all files in that folder to arrive at an answer. You add a daily sheet and run the code again and it includes it in the report/data.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    03-04-2018
    Location
    uk
    MS-Off Ver
    office365
    Posts
    7

    Re: Auto Update, Daily Data

    How do you make an attachment?

  5. #5
    Registered User
    Join Date
    03-04-2018
    Location
    uk
    MS-Off Ver
    office365
    Posts
    7

    Re: Auto Update, Daily Data

    My sheet is a bit of a Beast, so cant really screen shot it. Goes to BV329 :p

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Auto Update, Daily Data

    Here is another link on the ability of Power Query to use all those files/data and simply refresh itself.

    https://support.office.com/en-us/art...d-52d1a76e7158

    Based on your question, I think you need to start looking at this newer tool built into Excel.

  7. #7
    Registered User
    Join Date
    03-04-2018
    Location
    uk
    MS-Off Ver
    office365
    Posts
    7

    Re: Auto Update, Daily Data

    Hi Marvin,

    Thank you for your quick replies, would it be possible, to be able to email you my file or something? and you have a look at it? might more sense of what im meaning?

  8. #8
    Registered User
    Join Date
    03-04-2018
    Location
    uk
    MS-Off Ver
    office365
    Posts
    7

    Re: Auto Update, Daily Data

    sorry, i dont have the Power Query Option on my bar, i searched for it and its not an option?

  9. #9
    Registered User
    Join Date
    03-04-2018
    Location
    uk
    MS-Off Ver
    office365
    Posts
    7

    Re: Auto Update, Daily Data

    uploaded my file online if any one wants to have a look to better understand my issue?
    Last edited by MilkMyDragon; 03-04-2018 at 04:44 PM.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Auto Update, Daily Data

    Quote Originally Posted by MilkMyDragon View Post
    How do you make an attachment?
    Quote Originally Posted by MilkMyDragon View Post
    My sheet is a bit of a Beast, so cant really screen shot it. Goes to BV329 :p
    No. Not a screen shot. Attach a representative sample Excel workbook ... it save retypiing data.

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Auto Update, Daily Data

    continue from post above....

    and if your file is too big (over 1 MB) - save it as xlsb file then try to upload here.

  12. #12
    Registered User
    Join Date
    03-04-2018
    Location
    uk
    MS-Off Ver
    office365
    Posts
    7

    Re: Auto Update, Daily Data

    hopefully it attached
    Attached Files Attached Files

  13. #13
    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
    79,415

    Re: Auto Update, Daily Data

    If you have Office 365, you do have PowerQuery. It’s now called Get & Teansform and is to be found on the Data ribbon.
    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 to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Macro with button to update daily report with data from the previous day
    By Brenton77 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-27-2015, 02:25 PM
  2. Macro to update daily tracking [copy data from 1 workbook to another]
    By excelnewbster in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-08-2012, 03:38 AM
  3. Update Daily Data To My Email (Outlook) Help
    By neelpatel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2012, 10:16 AM
  4. Repetitive data update (daily)
    By draxz in forum Excel General
    Replies: 4
    Last Post: 07-17-2012, 11:39 AM
  5. Excel - Auto update of daily web data into excel
    By angelfre in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-02-2010, 11:45 AM
  6. Formatting a series of cells to update data daily
    By jlt6jlt6 in forum Excel General
    Replies: 5
    Last Post: 09-30-2009, 02:22 PM
  7. Auto update daily report problem
    By SteveG in forum Excel General
    Replies: 4
    Last Post: 02-09-2006, 04:30 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1