Setting Up Excel Data In Aggregated Way That Can Feed Dashboard Or Daily Report
Aug 19, 2013
I want to set up daily charts to monitor various things but Im not sure how best to set up the data in order to do this so it calculates automatically as data is refreshed.
I want to run daily activity data that tells me how many people attend each room per day. the aggregated data would include:
Number of attendances, by room (there are 11 rooms), by day
Each attendance grouped by category of patient (up to 25 categories)
Each attendance by type of attendance (various groups)
How should I set up the data to show these things in a way that it can be linked to a text file and refreshed daily on opening? the data at the moment is in rows per attendance rather than grouped in any way.
I would like the data to have dates and months etc automatically genetared by the attendance date in order to graph the above indicators, as well as a % atttendance in relation to capacity per room etc
simple excel template that I could try to manipulate my data into so I can get started. The data is generates using a transact SQL query and saved as a text file which I wabt to use for the daily stats.
View 6 Replies
ADVERTISEMENT
Mar 27, 2013
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
View 4 Replies
View Related
Aug 21, 2014
Sheet 2 to be referred for preparing report in Sheet1. Data is available in Sheet2. I need to prepare a daily report of how many documents have been received from "To Reliance" folder, 'Reject' Folder & how many documents have been sent from "From Reliance" Folder.
View 5 Replies
View Related
Feb 23, 2008
Last week I posted a question related to formatting a cell to return a Day of the Week versus a numerical representation IE "Wed" instead of 02/20/2008 12:00AM. The solution provided worked for me:
1) Format cell to DDD MM/DD/YYYY HR:MN. Cell range (A1:A500)
2) Format destination cell with DDD. Cell range (B1:B500)
3) Destination cell (B1) = to original cell A1
4) B1 displayed data as "Wed"
However, the issue I still have is; I wanted to create a pivot table summarizing a year activity by Day of Week (in other words 7 entries for the year) and the pivot table still recognized all the MM/DD/YYYY. I ended up with a table displaying every day of the year instead of a yearly summary by Day of Week. Is there some way to strip out all the other numerical data from the new column I created to run a pivot table by Day of the Week for a whole years activity?
View 2 Replies
View Related
Feb 24, 2014
I want to get into dashboards, I've looked at video on YouTube etc. I now know how to design them but what do I need to know is what search phrase etc I need to use cos I'm a bit confused. How do I get data from other pages onto first screen where dashboard is.
View 3 Replies
View Related
Jan 4, 2013
a daily report I have to create. On a daily basis I will be provided with dispatch information from a warehouse. What I need to do is create some sort of report that will add the data and then calculate totals on a daily basis. There are five columns on the daily report that I will be provided with but I am only interested in two colums: that is the product code and the quantites. I will then save under a week number or month.
View 9 Replies
View Related
Apr 3, 2013
I have a 2 sheets in a workbook by the name Entry and DataStore. I am entering the daily data in Entry sheet and then manually updating the data in DataStore sheet. Is there a macro to automate this.
I have uploaded the sample file with the expected output comments to the below link:
Free large file exchange service without size limits.
View 8 Replies
View Related
Jun 10, 2008
i have facing a big problem nowadays.problem is that, i have to regularly update manually(copy & paste) "oil filling", " stock" & "meter reading", coming from every day by the supervisors of our company for verious sites spreading accross the our state, nearly 1305 site. i have attached the master file(which should be updated) with the reports coming from the supervisors(Rosan & Jhon) in another sheets. the master file is same form as i given. is their any way of automatic update by any macro.
View 2 Replies
View Related
May 8, 2014
I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form:
| Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.
View 3 Replies
View Related
Aug 22, 2010
Code for creating a speedometer type Dashboard in Excel?
I am using Excel 2010, but I would definitely like to see any previous version examples.
View 3 Replies
View Related
May 8, 2012
Call center stats: I have a SQL database which is LIVE. I would like to create a live report on excel from this database. (update my excel table automatically)
View 6 Replies
View Related
Jun 3, 2014
I have created a dashboard in excel and want to convert it into an interactive web page.
View 3 Replies
View Related
Nov 14, 2013
I have an excel 2007 script that downloads daily stock data and prices. I do analysis and graph the data.
I would like to keep the download the same, but modify my analysis so as to obtain weekly and/or monthly data.
I am sure this is commonly done, and is not rocket science, but so far I am baffled as to the procedure / algorithm to do this.
View 1 Replies
View Related
Mar 4, 2014
I upload the form here
[URL]
View 1 Replies
View Related
Jul 12, 2013
I have created a excel document. Would like to create report using the data.
create reports using excel data
View 3 Replies
View Related
Nov 3, 2013
I am trying to create an interactive dashboard.
I am attaching a sample data file that contains two tabs (data and dashboard). By looking at dashboard tab you will know what I am trying to build. Based on the drop down values I would like to populate the data on the dashboard tab.
sample data.xlsx
View 11 Replies
View Related
Dec 11, 2013
I have a huge Excel spreadsheet with various worksheets and data, I want to merge some of that data into a report using word template. How can I pull simple data like the company infomation into that word report from Excel? Do I need to know VBA to accomplish this?
View 1 Replies
View Related
Jan 4, 2013
Is there any method in excel that can pay the way for drawing figures according to data feeded.
for Ex.
Say
Radius of circle=x
Now this x can be used for drawing the circle and the radius can be entered by user.
View 2 Replies
View Related
Aug 28, 2009
I have set an excel spreadsheet set up to update live data from a URL every 5 minutes. However I am wanting to do try and save a bit of time if possible.
Basically I am monitoring horses bought on a game website. Each horse has the same URL except for the number at the end which goes up in 1 increments. The URL is already set up and when the horse is bought it changes from being blank to having the horses details etc.
At the minute I am able to autofill cells with each uinique URL using fill, then series menu. This is so I dont have to manually enter each URL, as hundreds may be bought each day so it would take too long to do this.
My question is this, am i able to have a automatic series linked to the 'data from web' function in data tab, instead of having to manually adjusting each URL for each individual horse.
View 9 Replies
View Related
Jan 21, 2014
This is a sample of what I am trying to accomplish (file attached). I have information in one worksheet (called MasterList) and a second worksheet called (RecordList). I want to take information from MasterList and RecordList and combine them to produce a report (Results). Assume the user does not have access to MasterList or Results.
The user would enter the UID in RecordList, which then populates information from MasterList. There can be multiple entries for each UID and there is no set number of entries (could be 1, could be 500)
The user enters the UID into RecordList, which populates information from MasterList. The user then adds in the additional information into the fields.
What I want is all the information from the Master List must be reported whether anything exists in RecordList or not. If there is information in the RecordList, display it and on a seperate line for each entry. Each UID then needs to be totalled (which can be done through pivot table later).
View 1 Replies
View Related
Mar 26, 2014
I new to excel and vba , and i have live stock feed data in excel.
The live feed has 17 rows..of ticker names and more column about values.live feed starts at 9.00 to 3.00
I want only 13th column data to save for every 5 min..in new spreedsheet.
I am also including a sample : live crude.xls
View 1 Replies
View Related
Jul 2, 2008
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies
View Related
Jan 14, 2014
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
View 5 Replies
View Related
Oct 27, 2012
It is my general understanding that the change event system within Excel vba is fairly particular as to what will fit the mold of a qualifying change event.
For example, changes that the user imparts to the worksheet and other written code are legitament candidates for change events. However copying down data and cells changing their values indirectly rather than directly may not be considered in the Microsoft change event design.
I would like to know 2 things:
1) Does a cell updated by a market data feed mechanism qualify for a change event?
2) Any list of qualifying change event types. It seems that Microsoft does not have this information.
View 2 Replies
View Related
Aug 27, 2013
I can't seem to figure out to easily aggregate & automate a series of weekly values (ie. forecast volumes by a specific part number) into vertical monthly summaries, by product.
Here's the before picture:
column A column B column C column D column E column F
Part num 1/1/2013 1/8/2013 1/15/2013 1/22/2013 1/29/2013A
100 100 100 100 100B
150 150 150 150 150C
200 200 200 200 200D
250 250 250 250 250
The output after manually doing this would look like this:
column A column B column CPart num Date QuantityA
Jan-2013 500B Jan-2013 750C Jan-2013 1000D Jan-2013 1250
View 5 Replies
View Related
Mar 20, 2014
I have the following scenario:
Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.
So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.
I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.
View 4 Replies
View Related
Nov 3, 2011
Problem I am having:
I want to import a csv file to an Excel workbook. The csv file name does not change, but the workbook which it is pasted into changes name every day (it has a filename which ends in todays date, and is updated daily).
Using the macro recorder I can get the process to work for today, but when as soon as I update the recipient files name the next day, the macro fails.
Code below:
' Sheets("MORCOM").Select
'Range("A5").Select
'Cells.Select
'Selection.ClearContents
'Range("A1").Select
'Workbooks.Open Filename:= _
[Code] .......
View 5 Replies
View Related
Oct 6, 2011
I have an access database where I export a file into excel on a daily basis. The file is saved to a different filename daily because I have added the date to the filename. My question is I have a formatting macro spreadsheet so when I export the file in access it will run the formatting macro in excel to format the sheet appropriately. How do I get the formatting macro to recognize to run if the excel file changes on a daily basis in vba?
This is what I have in the formatting macro:
Sub Auto_Open()
'
' AutoOpen Macro
ChDir "G:SD Forecast and PlanningShortages"
Workbooks.Open Filename:= _
"G:SD Forecast and PlanningShortagesShopWorkingList10052011.xlsx"
How can I get the formatting macro to run on this file when the date changes constantly in the filename?
View 6 Replies
View Related
Sep 18, 2013
if cell A1 is the text UA i want the text to have a value of 2, so that if i have UA in A1 and UA in B1 when i add into C1 the answer is 4.
View 1 Replies
View Related
Feb 15, 2013
One of my job responsibility the following up our store stock, usually we consume constant daily value form our stock, as example we used daily 200 mt of corn, 150 mt do soya bean meal and so on, I would like to creat excel sheet to deduct this value from the previous column daily automatically.
View 1 Replies
View Related
