+ Reply to Thread
Results 1 to 16 of 16

Auto-Fill a data range from a dynamicly growing range with a Count Function...?

  1. #1
    Registered User
    Join Date
    06-05-2014
    Location
    Iowa, USA
    MS-Off Ver
    2013
    Posts
    33

    Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    Hello all,

    I seem to have issues putting my problem into a title, I'm looking to explain my situation here below:


    Please look at the attached excel file to help clarify what I'm looking to do. I'm adding data manually to the bottoms of columns A, B, and C every week. I would like to get metrics off of this data such as average age, total count per week, count per day, etc. What I think would be very helpful and slick is if after manually adding in the data, all of my metrics formulas would automatically update their source ranges, and thus have accurate "real-time" solutions. I mostly understand how to grab the averages and sums and what not, but I'm struggling to find a way to automate the process. there is a lot of drag and fill going on that I was hoping could be made automatic.

    So... As i add data (Cols A,B,C) can my G, H, J, and K columns automatically add in the fields?

    Any other ways of doing this would be accepted and appreciated too! I'm not fond of pivot tables though as I'm not familiar with them and they seem to use up a lot of space with basically the same data I already have...

    Thank you!

    AustinSampleExcel.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    It looks like a Pivot table might be able to help you achieve this?

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

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    Define your data range as a table ( Insert ribbon) then use that table as a Data source for a Pivot Table. There you can group as requested.
    When you add data to your table, right click the PT and select " refresh"

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    Make a table of your data

    Excel 2007 => add => table.

    And although you are not fond of pivot table's, try to learn it.

    A pivot table is a very, very powerfull tool in excell, which expands each new version of excell.

    If you have questions on this topic, just ask.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    I made an example for you, see the attached file.

    I used the formula:

    year, month, day, weeknumber

    I used a column to determine the criteria.

  6. #6
    Registered User
    Join Date
    06-05-2014
    Location
    Iowa, USA
    MS-Off Ver
    2013
    Posts
    33

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    Wow such quick responses, thank you!

    I do need to learn to use pivot tables for sure, it's hard for me to give up on the pure control formulas allow you, though.

    oeldere, I somewhat see what you are doing with those tables (even if it's another language ha) but to me those tables look messy and I'm confused about your criteria.

    After manually adding in another data row, but with a new state, the pivot-table did not update? hmm

    This will be taken over by someone with little to no knowledge of excel, I would love for them to simply copy and paste the data and be done if possible? I realize this is a bit odd of a request... but just trying to make it super easy.

    (oeldere) 327369d1403547655-auto-fill-a-data-range-from-a-dynamicly-growing-range-with-a-count-.xlsx
    Last edited by AustinLe; 06-23-2014 at 03:09 PM. Reason: changed my thinking

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    you get a #Value! because in cell A30 is no date.

    you can avoid errors with a formula => iferror(etc).

    there are no in the file.

    add new data in the file in #5.

    you will see the table will automatic expand.

    again, if you have questions, just ask.

  8. #8
    Registered User
    Join Date
    06-05-2014
    Location
    Iowa, USA
    MS-Off Ver
    2013
    Posts
    33

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    Okay, after a little bit of time looking over what was done, I think I understand it better. The pivot table has some quirks (date format is odd and won't stick to mm/dd/yyyy...) but for the most part should simplify things.

    The criteria date is one day behind though, where would my "-1 / +1" go?

    Correct me if I'm wrong but the week ranges seem wrong as well. does excel use current year or cell year to find the week number and what day does it begin / end? (Sunday, Monday?) - does that make sense what I'm asking?

    THANK YOU
    Last edited by AustinLe; 06-23-2014 at 03:27 PM.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    1)
    Please Login or Register  to view this content.
    in your file the date are dd/mm/yyyy.

    in that case the pivot table also use that format.

    See the attached file.



    2
    Please Login or Register  to view this content.
    See the blue cells in M3 en M6 (in combination with cell I2).

    Change the bleu cells and see what is happening with the criteria.



    3
    Please Login or Register  to view this content.
    The week starts on Sunday (see the attached file).


    I also added the weekday.


    If you have questions, just ask.

  10. #10
    Registered User
    Join Date
    06-05-2014
    Location
    Iowa, USA
    MS-Off Ver
    2013
    Posts
    33

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    Thanks again oeldere, the data is becoming manageable now!

    A somewhat off topic question here. So I'm creating a report to visualize all this data I have, but how do I create multiple charts or graphs from the same pivot-table data? For example, can I make two charts, one with strictly a line chart showing the state totals. Then another chart with just the total counts by week, etc etc etc?? I'm finding that the pivot charts like to show all the data that is selected to show in the tables.

    I have searched around the net but haven't found a straight answer. This may not be possible and could then change the way I'd like to setup my data. I'm looking at the end result being a large multi chart dashboard like page.. would be messy with that many pivot tables.

    They didn't come to a definitive answer here..
    http://www.excelforum.com/excel-char...vot-table.html

    -Austin
    Last edited by AustinLe; 06-23-2014 at 04:46 PM.

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    I'm not familiar with pivot graph, but I think it will be possible to make 2 graph.

  12. #12
    Registered User
    Join Date
    06-05-2014
    Location
    Iowa, USA
    MS-Off Ver
    2013
    Posts
    33

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    When creating two pivot charts from the same pivot table, changing any field or sort option changes both of the charts and not simply the current selection. Can anyone else chime in if they have experience with this perhaps? It seems like there would be a way to do this but I am new to pivots

    Thank you for the responses oeldere, you've been very helpful thus far!

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    2 pivot tables of the same data.

    2 pivot graph of the same data.

    See the attached file.

  14. #14
    Registered User
    Join Date
    06-05-2014
    Location
    Iowa, USA
    MS-Off Ver
    2013
    Posts
    33

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    oeldere, with massive amounts of source data won't this cause a large slowdown?

    This is a less than ideal solution, but it is not your fault in the least. Microsoft needs a work around for this

    How do people who make visual reports and dashboards go about data sorting and organization??

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    Please Login or Register  to view this content.
    Please test it and report the result.


    Please Login or Register  to view this content.
    How did you make your graph at the moment?

  16. #16
    Registered User
    Join Date
    06-05-2014
    Location
    Iowa, USA
    MS-Off Ver
    2013
    Posts
    33

    Re: Auto-Fill a data range from a dynamicly growing range with a Count Function...?

    There isn't a great way to test this as the data set will grow an undetermined amount daily, for an undetermined amount of time. I can't imagine how big this could get in a year... There has to be a general rule of thumb when using that many pivot tables and charts?

    the graphs would be pivot charts... insert tab >> pivot chart. that's it.

    thank you!
    Last edited by AustinLe; 06-24-2014 at 09:56 AM.

+ 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. Replies: 6
    Last Post: 02-06-2012, 10:35 AM
  2. Auto fill a range
    By ghirodoimo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-14-2011, 04:31 AM
  3. Auto Fill formula with growing data
    By chrispulliam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2010, 01:39 PM
  4. Growing range within a Sumproduct.
    By mmartens12 via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-01-2006, 07:50 PM
  5. VBA or Macro to auto fill a selected range
    By Btibert in forum Excel General
    Replies: 2
    Last Post: 08-21-2005, 03:42 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