Produce A Report In Specific Layout From Varying Data
Sep 18, 2009
My last post here was brilliant, I was very close to figuring the problem out myself (I'm learning) and the help I got here really was useful to help me finally get what i wanted to work.
Unfortunately, this request isn't so simple, as I haven't a clue what to even search the forum for to help me with this.
I have attached a simplified example to try and explain what i would like to happen.
Sheet1 holds information which is regularly updated, so the number of rows can vary, as the number of customers vary.
I want to produce a management report from this data, with a specific layout.
I have put the layout into Sheet2 - the cells highlighted in yellow are there simply as a reference to show the cell from which column would go where.
I produced the top report, by filling the cells with =and then clicking on the cell on sheet1 that I wanted displayed there.
I thought that if I then selected the table and filling down, it would produce the same layout table for all the other rows - but I was wrong.
Another problem is that, this report is needed to be entirely automatic, so I don't want to fettle filling down cells for the number of rows. I will need it to count the number of rows in Sheet1 and produce a report in the specific layout for each company in sheet 2.
It may be an option to use a button create the report, to save the report into a new workbook with automatic file name containing the date and also print a copy out automatically on the default printer.
I don't know how to go about this task at all, so I would really appreciate some help, even if it is pointers for what i should be searching the forum for!
Any way to produce a report per row of an excel spreadsheet. For example if my spreadsheet contains the names of 10 people in column one and lots of columns of data per person. Is there an easy way for me to pull all that data to produce a one page report for each person listed with the data within it?
I must produce a report that details elapsed time between two dates and times.
The duration can span a number of days, I need to report just the elapsed working time, I can calculate the days with "NETWORKDAYS" but how can I calculate only the elapsed working time 08:00 to 17:00?
Is there a really good tutorial for laying out multiple database queries in excel? Or even just for automating reports between Excel and Access? Here is my situation: Monthly, Quarterly & Annually I report on performance standards such as: duration, best practices, cost-benefit analysis, etc. The information is pretty standard and is pulled from Excel Reports. Access is used to filter this information by appropriate month, quarter, year, and/or data type. In access I calculate the performance averages, max and min. Then this is in linked back to Excel to update the charts and summaries. My concern is how much time I spend rebuilding this system every month (about 4 business days)
I currently have a report with so many large array formulas that it is virtually unusable. I want to use the DSUM formula in place of my array formulas but I am vexed in regards to creating a DSUM formula that I can put in the top-left cell of my report and then copy down to the bottom right side of my report. Currently, the array-formulas sum data from a large list when it meets two criteria -- one part of the data-record in the list must match the row label of the current row in the report and another part of the data-record in the list must match the column label in the current column of the report. I have found with the DSUM formula that you can use a "formula" criteria in place of a static criteria...the problem I am having is that the part of the formula that points to the data-set must be relative while the part that points to the formulas criteria needs to be static -- in order to be able to make one DSUM formula and copy it down and over I would need my formula criteria to have the opposite setup (with the data-set part static and the formula criteria as relative).
I have a workbook with 2 worksheets, Sheet1 & Sheet2. Sheet1 has a list of products on it, with a code, pack size and cost.
Sheet2 has got 8 columns in it.
The first column will be A , B , C
The second will be D, E, F
Third will be G, H, I
Going right through to column 8 which will be V, W X
In the second column in each on I will be have a (sell by date) So B, E, H, through to W will have sell by dates in them.
What I am looking for is a macro that will give me a list of short sell by dates when I click the button. So if today’s date is 02/12/09 when the button is clicked it will give me a list of all the product that have lets say a month sell by date left, I would like to be able to change the month to anything I want, just in case I want to do a longer check on the sell by date.
When it finds a short date I would like it to check against the code, so in the first column the code will be in A, it will match that code up against the code in Sheet1 and give me a list of the Code, and the Product from sheet1 and the sell by date that’s in Sheet2. The problem I have is when data is put into the second column on sheet2 which is D.E.F, I would want it just to check that column and not the A,B,C from column 1.
I am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:
Those lists will be called up based on 7 values in column “D”: “G” “152” “J” “X” “D/E” “V” “R”
So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.
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.
There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.
In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are
5700 entries that show up 2x 504 that show up 3x 24 that show up 12x
I want to call a macro with a varying name that is within a module with the same name.
I have a module called Test1 and within this, a macro name called test1 I have a module called Test2 and within this, a macro name called test2 etc
On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.
The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3() Test1.Test1 End Sub
The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3() Test2.Test2 End Sub etc
However, if I try it so that the calling of the macro varies as below , it does not work:
Sub Test3() MacroToCall = Sheets("Sheet1").Range("A1").Value MacroToCall.MacroToCall End Sub
firstly i have attached said spreadsheet 1st sheet is named purchase order 2nd sheet is named purchase order numbers
upon opening the spreadsheet i would like on sheet purchase order to automatically input the next available number from column a in sheet purchase orders ( in this case next available is st010 and put that into cell k12 on sheet purchase order
i then want to enter the relevant data in sheet purchase order cells b16 , b23 , g23 , i23 and d28
i then want to click a button with macro attached to transfer that data in to sheet purchase order numbers
I have a very long list in columns A and B, about 1000 rows. I was just wondering how would i be able to setup an automatic formula that can produce the data in column D and E?
Ideally, i would the sequence running every 5 rows but iam doing this manually and running the sequence every 5 rows would be way to slow.
I have two tables each containing a column with Account ID. I want to in table A put a column that will return a yes or value of 1 if the ID in that row shows up in the ID column in table B. I have windows 7 with excel 2007
I have a workbook where employee scheduling is done on a monthly basis in 15 minute intervals. It is laid out like this:
******** ******************** ************************************************************************>Microsoft Excel - Key Support Services 090813.xlsx___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2C2D2E2F2G2H2=ABCDEFGH2Time8/1/20098/2/20098/3/20098/4/20098/5/20098/6/20098/7/200938:00 AM 48:15 AM 58:30 AM 68:45 AM 79:00 AM 89:15 AM 99:30 AM 109:45 AM 1110:00 AM 1210:15 AM 1310:30 AM 1410:45 AM 1511:00 AM Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The times go from 8 AM - 8 PM, and all of the cells within the range B3:AF51 are drop downs with the employee names listed. They are conditionally formatted so that we can easily see who is scheduled to work when, and on what days.
The problem that I have is that I like this layout for inputting the data because I can see it all very clearly, but it is not good for archiving or saving past months data. I want to be able to take that view and change the format so that it can easily be summarized in a Pivot Table for all of the historical information.
So, I need to take the date and time and format both of them into a column, put the client name (will be the sheet name) in the next column, and then the name of the staff that worked during that time.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1DateTimeClientStaff28/1/20098:00 AMClient1Employee138/1/20098:15 AMClient1Employee148/1/20098:30 AMClient1Employee158/1/20098:45 AMClient1Employee168/1/20099:00 AMClient1Employee178/1/20099:15 AMClient1Employee188/1/20099:30 AMClient1Employee198/1/20099:45 AMClient1Employee1108/1/200910:00 AMClient1Employee1118/1/200910:15 AMClient1Employee1128/1/200910:30 AMClient1Employee1138/1/200910:45 AMClient1Employee2148/1/200911:00 AMClient1Employee2158/1/200911:15 AMClient1Employee2168/1/200911:30 AMClient1Employee2178/1/200911:45 AMClient1Employee2188/1/200912:00 PMClient1Employee2198/1/200912:15 PMClient1Employee2Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
From there I can take a completed month, and quickly run some code to make the change, and add it to the historical tracking where it is all summarized.
I have a data feed that gives me a summary of a set of data. I want to be able to work back to what would be the original data (this unfortunately isn't available). The attached excel file as an example of what I am trying to do.
I am not too sure about the working with the dates etc.
I have a worksheet that has similar data but some maybe different lengths. What im trying to do is to be able to have them all in line so where the diagram no. is on the worksheet they are all lined up. On the excel sheet attached I want TURN NO in column J to line up with turn no in Column A each time.
I have a stacking program but it does not keep the first column associated with the data that is stacked as well as the first row. This is how the database looks now:
CREATE TABLES LIKE BELOW?HR #55551 July '07 HR #5721 July '07 HR #10232 July '07 HR #9822 July '07 HR #55 34 August '07 HR #57 731 August '07 HR #102 2,898 August '07 HR #98 1,557 August '07 HR #55 1,929 September '07 HR #57 1,653 September '07 HR #102 1,988 September '07 HR #98 1,097 September '07
The current VBA prgram just stacks the columns in one column going from right to left.
I have created a template workbook for individuals who work on different sites. The spreadsheets used by each individual have the same headers. The sheets all sit in the same folder in dropbox.
I am wanting to produce a summary workbook within the dropbox folder that automatically populates when an individual updates their workbook.
I have attached mock example of the individuals worksheet for your information.
The questions that I have are as follows:
1. Is it better within an individuals workbook that they record their activity all on one sheet as opposed to having a spreadsheet per months activity? It seems tidier to have a sheet per month but does this make it more difficult to produce a summary workbook?
2. The individual is required to record a patients individual drugs on separate lines but they need to record a unique identifier, age, *** and month worked. Do they need to record all this for each line of drug or is there an easier way?
3. How should the summary sheet be produced - pivot table or sheet with formulas linked to the individuals workbooks?
I have a one column spreadsheet. The column contains this data:
1 Name 2 Address 3 City 4 State 5 Zip 6 Telephone 7 Fax 8 URL 9 10 11 Name 12 Address 13 City 14 State 15 Zip 16 Phone 17 URL 18 19 Name 20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
I have a spreadsheet that tracks hours for employes and I need to get the data in a different layout to import to a database i'm building. The example spreadsheet has 2 tabs, the first is the format it's currently in and the second in the one I need it in.
the sheet is very long and monitors the sales activities of each individual every day extending vertically down the sheet until the present day.
my first priority was to get the information recorded but now i am wanting to use it. how would an expert arrange this data to make analysis easier going forward?
ultimately i will be wanting to look at individuals, groups of individuals, certain types of activity etc within various time periods.
Is there an way to make an YES and NO button where when i click YES it pop-up a box asking for confirmation on the action and if you confirm it then the data wrote into A1, A8, D1, D8 will be saved into "Product Exit" worksheet then the data into A1, A8, D1, D8 will be cleared.
aswell as a NO button but the NO button will save into Product not sold IF confirmed TRUE and then cleared and if confirmed FALSE will just clear A1, A8, D1, D8. if it cant be done with the confirmation can it be done without it example ?
I would like to state how fantastic a resource this forum has been to me as a beginner. So keep up the good work! The issue I have is that my spreadsheet contains one column which shares two types of data: Component Type and Tag Number. What I would like the script to do is:
1. create a new column 2. move the Tag Number data from its current column into the newly created column 3. ensure that the data is still on the same row as it was previously
One thing to bear in mind is that the tag numbers consists of various formats (spaces/no spaces), but always begin and end with brackets - "(XTU U-532-934)". I have included and example of the current state of the data and what I would like to look like after.
I would like to change the data layout from the "Original Table" to the "Final Table" as in attached file. Belinda_June-25-07.xls. How do I use VBA to do the change? In my original table, I may have several hundred rows and more than 36 columns for the time periods.
I have a workbook that has three worksheets. The ASU Database sheet contains part numbers and descriptions including specific details that are selected from pull down data validation lists that are named on the third worksheet. I want to have a front end on the first worksheet that has similar data validation lists for the user to select from to create a search criteria that will create a worksheet with only the rows that meet this criteria. I am not sure what method to use to accomplish this.
