+ Reply to Thread
Results 1 to 11 of 11

SUMPRODUCT - Adding New Data Daily

  1. #1
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    78

    SUMPRODUCT - Adding New Data Daily

    Hi,

    I was wondering the best way to update a spreadsheet where I have multiple columns with agent performance data being added on a daily basis.

    I have summaries below the main data where I am using the SUMPRODUCT function to adjust the running totals but it is proving to be very cumbersome leaving me having to manually go in and overkey the row value for every single column. Is there a way I can have these update automatically using ranges or something and if so how do I do it?

    I would usually just churn all this through a pivot table but my boss is insisting this is how he wants it is to be done.

    I have attached a screenshot for reference.

    Thanks a lot
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: SUMPRODUCT - Adding New Data Daily

    Summaries would be better on another sheet. Make the ranges cover data that is unlikely to be reached, e.g. A4:A100000. Just be careful when you get near 100,000. Have a warning/condtional format come up.
    How does he want to view a summary, lots of keypresses or one click and their viewable. I know what I'd prefer.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: SUMPRODUCT - Adding New Data Daily

    change your data into an Excel Table then formula's will be auto-extended when you add new data.
    https://support.office.com/en-us/art...9-6c94334e492c

    or a formula solution could be to use offset function.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    but I think you are better off with excel table.
    many offsetfunctions may influence performance of (re)calculation of the sheet.

  4. #4
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    78

    Re: SUMPRODUCT - Adding New Data Daily

    That what is confusing my as my source data IS formatted as a table but I am manually having to resize the table every day too. Is this because this sheet is referencing other sheets and pulling values across?

    I know the offset functions tend to slow things down a great deal and there is a strict deadline in place for this daily.

  5. #5
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    78

    Re: SUMPRODUCT - Adding New Data Daily

    This was the point I made when this spreadsheet was being created and was over-ruled. So it is best to just discontinue the summaries in their current format and switch to summaries on a new tab?

    My boss literally just wants summaries by agent, by day, by week. The method above was his method of choice and it is a lot of work and seems like a long way for a short cut. I'd rather all the summaries updated automatically as soon as new data is entered. The source data IS formatted as a table, which usually updates formulas automatically or with the click of a button if put through a pivot, but that is not working here. I am manually having to resize the table too. I think this may be because the data within some of the columns is referenced from different tabs within the same workbook and not "keyed" data. Is that right?

    Thanks
    Last edited by BigErnKingpin; 02-11-2019 at 09:23 AM.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: SUMPRODUCT - Adding New Data Daily

    What you have shown is just a formatted 2-dimensional data range, not an Excel Table.
    If you convert that range into a table, many new features are available: Table Design tab on the ribbon, automatically Copy formulas, sorting, filtering, Special Total Row: summing, counting, etc.

    click somewhere inside the data
    either: 1) press ctrl-t, or via ribbon or, 2) Insert > table
    Ben Van Johnson

  7. #7
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    78

    Re: SUMPRODUCT - Adding New Data Daily

    Hi ProtonLeah and thanks for the response.

    As you can see from the 2nd attachment this IS an excel table and I use the technique you described on a daily basis when creating pivot tables. When doing such I can literally dump a new days worth of data and my table will automatically accept and expand to accommodate it. I can then go into my pivot table and hit refresh all and my pivot table and corresponding pivot charts all adjust to reflect the new information.

    Here I have 3 seperate "summaries" which all reference the excel table data source and when I try adding new information the excel table does NOT automatically adjust like I thought it would. I manually have to adjust the table range by overkeying the row number every time.
    Attached Images Attached Images

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: SUMPRODUCT - Adding New Data Daily

    Attach a sample workbook (not a picture or pasted copy). 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.

  9. #9
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    78

    Re: SUMPRODUCT - Adding New Data Daily

    The workbook is far too complex and contains too much data for me to go through and make a simplified, desensitized version that I can upload here, so I have marked the thread as resolved as there is no option to delete.

    Thanks anyway guys

  10. #10
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: SUMPRODUCT - Adding New Data Daily

    Ok, so not being able to get an example I can understand and you may not be able to get to the best practise solution because the boss dont want major changes done at risk of not getting his reports on time..

    But still when you have an excel table expanding ranges should not be an issue. I see one potential change you can make that cloud solve your problem.
    Change your sumproduct formula to look like this

    I cant be sure of your tablename but the fieldnames are accurate

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    There are two ways to get to this look
    1. while in formula edit mode put your mouse on the columnheader you want to add to the formula, when you see a black downward arrow click the column and the whole columnrange will be put in your formula s you see above Table1[Agent name]

    2. by manually typing the formula starting with the tablename then the square bracket, as soon as you type the first bracket excel will show you all available column names typing one or 2 letters is often enough to be able to pick your needed column

    When you change the formulas to look like this you should not have any problems with extending ranges manually. As long as they are included in the table


    Now just to be on the safe side in case you can not get it to work (which I doubt) there is also one other trick I want to share. so you can quickly update all ranges. It might also be a good time saver as intermediate solution until you get the new formulas implemented..

    You can also use find and replace to change the row numbers, now to avoid that it also changes a true value in the table that is the same as the row number you should always include the $ sign so how do you do it>

    1. Select the by agent range that has all the formulas that need an extended range (selecting the range also avoids replacing values on other spots in the sheet)
    2. click Find and replace (or CTRL +H)
    3. Put $3041 in Find box
    4 Put $3560 in Replace box (random value picked ofcourse you put there the actual row number to where the data reaches now)
    5. click replace all

    Now all formulas should have been updated with the new last row.

    Just practise on a copy of the sheet for a few times (put in random row number) just to see the effects on the formulas and the fact that is only affects the selected range and not the table above

  11. #11
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    78

    Re: SUMPRODUCT - Adding New Data Daily

    Hi Roel,

    Thank you much for your reply. There are some really helpful tips in here that will definitely help.

    Again thank you everyone who took time out to reply to my query.

+ 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. Transfer Daily Data from Daily Staffing Workbooks into a Running Annual Summary Workbook
    By nurseydiamond in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-07-2015, 07:05 PM
  2. [SOLVED] Sumproduct and Multiple Data (Daily, Weekly Data)
    By JohnMee in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 08-03-2014, 06:54 AM
  3. Replies: 4
    Last Post: 02-01-2014, 10:37 PM
  4. Replies: 3
    Last Post: 10-01-2013, 05:05 AM
  5. [SOLVED] Daily data automatically totaled in new sheet without dragging / re-adding.
    By Nerfmagnet in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-23-2013, 02:31 PM
  6. Replies: 13
    Last Post: 05-18-2012, 10:53 AM
  7. I have daily data...i want to turn into monthly by adding
    By starstill in forum Excel General
    Replies: 4
    Last Post: 05-27-2010, 08:11 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