I recently discovered named ranges and dsum & dmax which I have been able to use them together successfully for my work. About the same time (all in the last 6 weeks or so) I discovered dynamic graphs, etc. I struggeled and developed a fairly nifty dashboard type report that prepares numerous line graphs for data from up to 3 different electric meters for 1) a selected report month 2) a selected range of data (start and end date specified) and 3) total available data history. I also prepared a statistics page using dsum/dmax/dmin and vlookup to prepare an overview report on each meter by month for the entire history of available data. All these worked beautifully as long as the source table of data was a general database and NOT a structured Excel table. However, it required about 40 names in the name manager (not counting any print ranges!) and there were several offset functions included, so the workbook is a little slow - but it works so I was pretty tickled with it.
I took an online course on preparing dashboards which included support for a few weeks, so I submitted my workbook for some help as it wasn't working correctly when I used it as a template for a new client - the most current month of data wasn't populating in the graphs for the current period and when included in the range request -both were returning errors. I was told that my numerous offset definitions were making the workbook slow (which I knew) and that I should have set my table of data up as a structured Excel table and used the table references rather than all the numerous named ranges I had set up. On the presumption that I would be more efficient long term than my first efforts at dynamic ranges and tables, I started a new wb and set up the same data as a structured table and attempted to do the same graphs and statistics reports that my original file did perfectly. I was surprised to discover that I couldn't find a way to get the statistics formulas to work at all with the table. I tried sumifs, dsum, etc and nothing seems to work. Sum of course works, but the whole idea was to be able to pull the sum of the meter data from one column based on the selected month. Likewise, to pull the max of the meter based on the month selected and eventually, to pull the date/time associated with the max for the month using index/match. So far all of my efforts have been complete failures. I don't seem to be able to get any database functions to work with structured tables.
My criteria range is c1:c2
My table headers starting on line 3 are: Month
Time Stamp Date Month Meter1 Meter2 Totalized May-15
My data(currently) runs to line 49635 for 2015 and the first 5 months of this year - so it is a file that is going to get really big before the year is done and I would like to be able to keep it functional to continue data for more than 2 years
{=DSUM(Table1,Meter1,C1:C2)} this doesn't work - returns #NAME?
{=DSUM(Table1,Table1[[#Headers],[Meter1]],C1:C2)} also doesn't work - returns #VALUE!
=SUMIF(Table1[[#Headers],[Meter1]],Table1[[#Headers],[Month]]=C2) also doesn't work - returns 0
=SUMIF(Table1[Meter1],Table1[Month]=C2) also doesn't work - returns 0
I will be the first to admit that I am a complete newbie at the whole dsum/dmax stuff, but I was able to get it work just fine before I introduced the formal table - now I can't get a single formula to work. Do the dsum, dmax and dmin commands even work with structured tables?
I took an online course on preparing dashboards which included support for a few weeks, so I submitted my workbook for some help as it wasn't working correctly when I used it as a template for a new client - the most current month of data wasn't populating in the graphs for the current period and when included in the range request -both were returning errors. I was told that my numerous offset definitions were making the workbook slow (which I knew) and that I should have set my table of data up as a structured Excel table and used the table references rather than all the numerous named ranges I had set up. On the presumption that I would be more efficient long term than my first efforts at dynamic ranges and tables, I started a new wb and set up the same data as a structured table and attempted to do the same graphs and statistics reports that my original file did perfectly. I was surprised to discover that I couldn't find a way to get the statistics formulas to work at all with the table. I tried sumifs, dsum, etc and nothing seems to work. Sum of course works, but the whole idea was to be able to pull the sum of the meter data from one column based on the selected month. Likewise, to pull the max of the meter based on the month selected and eventually, to pull the date/time associated with the max for the month using index/match. So far all of my efforts have been complete failures. I don't seem to be able to get any database functions to work with structured tables.
My criteria range is c1:c2
My table headers starting on line 3 are: Month
Time Stamp Date Month Meter1 Meter2 Totalized May-15
My data(currently) runs to line 49635 for 2015 and the first 5 months of this year - so it is a file that is going to get really big before the year is done and I would like to be able to keep it functional to continue data for more than 2 years
{=DSUM(Table1,Meter1,C1:C2)} this doesn't work - returns #NAME?
{=DSUM(Table1,Table1[[#Headers],[Meter1]],C1:C2)} also doesn't work - returns #VALUE!
=SUMIF(Table1[[#Headers],[Meter1]],Table1[[#Headers],[Month]]=C2) also doesn't work - returns 0
=SUMIF(Table1[Meter1],Table1[Month]=C2) also doesn't work - returns 0
I will be the first to admit that I am a complete newbie at the whole dsum/dmax stuff, but I was able to get it work just fine before I introduced the formal table - now I can't get a single formula to work. Do the dsum, dmax and dmin commands even work with structured tables?