Create Organized Layout For Reporting
Sep 11, 2007
I have a list of all my products in the "Price Listing" sheet organised by a 3 level tree structure (small range of products shown in excel file attached).
I am trying to create a way of getting this list into an organised way (via macro) so that I can print this off and give to my customers. Sheet 1 shows an example of how it might look after the macro is run using the tree structure. I have tried to use pivot tables with no success.I am currently creating this manually each month and updating all new products, then running the prices for each customer but it is taking far to lonng to create the master sheet.
Different customers have different prices and I want to create a price list so I can get someone in the office to output the file to excel, run this macro and create the file without me having to put alot of work into each one, or even be in the office while they are creating this.
Is it possible to create a table of contents for the completed result using either seperare sheets, text formatting
View 9 Replies
ADVERTISEMENT
Jan 17, 2014
So I've got a workbook with three main sheets: Pipe, Fittings, Report. In the pipe sheet I've got 8 charts that are all the same but they're for pricing different types of pipe. I want to assign each line in all charts a category and then in the reports tab have a chart that will add all the prices from all charts in each category. I've tried using the VLOOKUP function but I can't seem to get it to work. I can attach the spreadsheet here if that would make things easier.
View 5 Replies
View Related
Aug 7, 2008
i have a table looking something like this
----A--------------B---------------C----------------D-------------
1---Destination-----Country_Code---Area_Code------ Price_Per_Minute
2---Armenia--------99--------------58, 59, 566------0.098
3---Jordan---------96-------------79,78,77,2--------1.023
4---UK-------------44-------------------------------0.50
5---UK-------------44-------------79,78-------------0.48
what i actually need is to turn this table into 3 columns table, the first column is the destination, the second one is the destination code which consists of the concatenation of the country_code and the area_code columns, and the third column will be the price_per_minute, the main problem is that the Area Code column contains many values and each value should be attached to the country_code and then i need to create more rows for each country, so the my table should look like this:
----A--------------B---------------D-------------
1---Destination----Code-------Price_Per_Minute
2---Armenia-------9958-------0.098
3---Armenia-------9959-------0.098
4---Armenia-------99556------0.098
5---Jordan--------9679-------1.023
6---Jordan--------9678-------1.023
7---Jordan--------9677-------1.023
8---Jordan--------962--------1.023
9---UK------------44----------0.50
10--UK------------4479-------0.48
11--UK------------4478-------0.48
View 5 Replies
View Related
Oct 24, 2013
I have this table with some information that I need to work with. The data is simple, but it has many issues, at least for me, to be able to work with it properly.
Basically, I just need to transfer part of the data from this table to another table (in another order). I failed to use vlookup because I need more than one parameter to search the data (Date, Shift and Sector).
The problem I am having to get the codes from the source table is that the same code sometimes shows up more than once per date, per shift and per sector. It's also not following a numeric pattern. I couldn't elaborate any combination of formulas to get the info I need....
I also can't change the format of the source table, because I'm not the one who made it, it has years already, and is constantly updated day by day by a good old man who doesn't know much about computers.
The file is attached : Example.xlsx
View 4 Replies
View Related
Nov 26, 2013
import it into to Stata to do statistical analysis. I always receive spreadsheets like this:
country
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
[code].....
and I have to manually reorganize it like this to import into Stata:
country
year
value
Benin
1991
20
Benin
1992
254
[code].....
Is there way I can quickly design a macro to do this? The problem is that I generally have a list of about 60 countries, and years from 1991-2011. So, it's really time consuming copying the column of data corresponding to the year, pasting below, repasting the list of countries and the years...then again..then again...then again...I'm using Excel 2010.
View 7 Replies
View Related
Feb 2, 2009
I have been trying to create a report that involves three conditions, but so far I have had no luck using SUM and IF conditions to do this.
I have attached a file with an example of what I would need. Basically, I would need the "Resolved" and "In-Progress" quantities filled in below the "Country Report" for each respective country.
View 3 Replies
View Related
May 15, 2008
I have a 2-column employee file. Column A has the employee id and column B has the manager's employee ID. The reporting layers can get pretty deep. In this example, there are 6 layers of reporting. For example, Employee id 1200 (which is not in column A) has 2 employees. One of them (1712) has several employees, one of whom (1680) has employees reporting to them and so on for several levels.
Is there a way in Excel (2003 or 2007) to summarize the reporting structure? For instance, I need to know all of the employees that report up through emp id 1712 - not just that report directly to 1712, but all those under 1712's tree.
View 9 Replies
View Related
Oct 1, 2005
I'm trying to figure out how to get Excel 2003 to report a value from a
separate column where the value reported by a MIN or MAX function
occurs. For example, say this is my setup:
Column A: time
Column B: value 1
Column C: value 2
I want to create a function using the MIN and MAX functions for cell
ranges in either column B or column C plus I want the value that
corresponds to the MIN or MAX value from column A. Basically, I have
data in columns B and C that occurs at times listed in column A. I not
only need to know the MIN and MAX values for certain ranges in columns B
and C but also the times at which these values occur (across hundreds
and hundreds of values with multiple MINs and MAXs, so this is not
something I want to do manually across multiple workbooks).......
View 11 Replies
View Related
Mar 4, 2013
I have a 2- column data as shown below
column A
Column B
aaaa
3
aaaa
8]
I have a data validation list in a different cell that reports the texts in Column A without repetition. The task for me is to write a formular in another column, which will report all the numbers that correponds to the text i select from my data validation list. For instance if i select bbbb i want the following numbers to appeear in a given column:
9
25
34
4
21
View 2 Replies
View Related
Aug 19, 2008
I run a store, and currently we handwrite all of our reciepts - and then implement them into a "Sales Sheet" on Microsoft Excel. From there we can monitor monthly sales and targets - coincide what we are selling, and figure sellthroughs etc etc.
To make life easier I am looking at typing out reciepts in Excel - within the same workbook as the "Sales Sheet". I would save each reciept as a separate sheet, and would like to know if it is possible to somehow copy data from the "Reciept" worksheet to the "Sales Sheet" worksheet??
View 2 Replies
View Related
Dec 31, 2009
I have a 2 page excel book, 2003, that runs a vlookup off a list from the 2nd page of the workbook. It is a long listing of information. It returns successful info in most of the cases, but in some instances it returns #n/a in one instance where it returned the correct info in others as in:
12345 = dog
12346 = cat
12345 = #n/a
Some instances don't report the corerct info at all while others only report the correct info some of the time like above where 12345 = dog and in some cases it doesn't turn out dog as the anser to the vlookup.
View 9 Replies
View Related
Feb 22, 2010
Assuming the below performance report:
Day 1
Actual 1
Target 1
Achieve 1/1 = 100%
Day 2
Actual 1
Target 1
Achieve 1/1 = 100%
Day 3
Actual 9
Target 10
Achieve 9/10 = 90%
Which method do you use to report the Achievement for the 3 days?
1. Do you take the average of 100%,100%,90% to give 97%
OR
2. Do you sum Actual (Day1+Day2+Day3 = 11) / Target (Day1+Day2+Day3 =12) to give 92%
View 4 Replies
View Related
Jun 19, 2007
I operate a small music management company and I run my accounts using relatively complex Excel Workbooks with different sheets for purchase and sales ledgers, cash in and cash out etc etc. Each financial year has its own workbook. My problem is creating financial reports. For example, if I need to create a report for outgoings and incomings relating to a particular project (a concert tour for example), and if that project was split over several financial years, I find myself spending hours creating pivot tables, and then pivot tables based on other pivot tables etc etc, to just get a clean report on that particular project. What I would love to have is a single report generating system, where I can just enter the name of the project, and get a financial report at the click of a button, based on all the raw data I have painstakingly entered in the individual workbooks and sheets. I reckon I'm too busy to sit down and tackle VBA programming myself, so I wondered if anyone knew of any third party software that might help me realise my dream simply and easily. Someone has already mentioned Crystal Reports, but I don't know whether this will be overkill.
View 3 Replies
View Related
Nov 17, 2009
I have a pretty big worksheet (230 columns x over a thousand rows). Any given column might have 3-4 nonblank cells; the rest are blank. What I want to do for each column is obtain the address of each nonblank cell, then grab the contents of the corresponding cell in the B column and concatenate the results. In the attached example, I want to put the function in d2, and the expected result would be ar 001, fc 001, hw 003.
View 4 Replies
View Related
May 18, 2012
Users copy and paste source data from a report into worksheet 1 each month. Data from last month is deleted and data for the current month copied into worksheet 1.
I am trying to write a formula within worksheet 1 to check that data for the current reporting period only is in worksheet 1. For example all data from last month's reporting period has been removed and the only data in worksheet 1 is the current reporting period.
Reporting period is shown in two columns Year and Period number (1 to 12).
View 4 Replies
View Related
Oct 27, 2012
I have linked Combo Box to Pivot Reporting Filter through VBA. When I Select Combo Box Value, same will be filtered in Pivot table. Combo Box values are constant where as Pivot report filter may not contain all the values which are in Combo Box. If Combo Box value is not available in Reporting filter of Pivot it should throw up msgbox and should select first value of Combo box.
View 3 Replies
View Related
Apr 14, 2007
see attached image. Chart is recognizing the 20 dates but not using the real dates listed in the column. Changed settings multiple ways to try to get it to use the actuals - no luck. I realize 'month's is checked in the image - but I tried 'days' and still no luck. What needs to be changed to make it report actuals listed in the 2 columns?
View 3 Replies
View Related
May 17, 2013
I am looking for a formula or VBA code that will allow me to Select a Team leaders name and all the agents reporting to that team leaders would populate. I have the first part of that problem solved but its getting the agents names to generate uniquely that I find problematic.
Below is a step by step example of how i intend it to work
Step 1
I select the Teamleaders name
Shelaine
Once that has populated the Team Names automatically appear
Shelaine
Score
Andrew
20%
John
30%
Mark
40%
Sarah
50%
June
60%
The scores will populate on its own as I will include a Vlookup formula to generate the scores, Its just the Agents names. I am using Excel 2007
View 2 Replies
View Related
Feb 27, 2009
I need 2 different formulas to generate commission reporting information on the Summary tab of the attached sample Excel file. The first is highlighted in green. For these cells, I need a sum formula that reports the total commissions (column H of the "Data" worksheet) for items Ordered in the month listed in column B of the "Summary" worksheet, but not invoiced until the month listed in the column D, E & F headers of the same worksheet. Date of item order can be found in column A of the "Data" worksheet. Date of invoice can be found in column E of the "Data" worksheet.
Now, the problem that I think I am going into is the way Excel handles dates and times. All columns and data highlighted in orange on the data sheet need to be maintained without being changed, as eventually I am going to have a report setup by our operating program drop in there so that it automates the information without any additional labor by our employees who have varying levels of Excel proficiency. Unfortunately, the report from our operating program cannot simply list a date without a time. Feel free to create any column or field to the right of the orange columns in order to complete formulas based on those orange columns. I will just lock those cells when finished so that coworkers don't accidentally blow the shizel up.
The second sum formula that I need is highlighted in yellow on the "Summary" worksheet. Basically, I need a formula that sums all commissions in column H of the "Data" worksheet for those items that are cancelled AFTER invoicing. Column D of the "Data" worksheet lists the cancellation date. There are explanations for each of these on the worksheets for quick referral.
View 2 Replies
View Related
Nov 6, 2012
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
View 4 Replies
View Related
Feb 15, 2009
I'm making a sign in and sign out sheet for work and when I go to view and then page layout I get not only that but other sheets without the texts. Also, when I go back to my normal view I see dotted lines that show the page size over and over again. Any idea how I can see only what I want to see on page layout and get rid of those dotted lines?
View 11 Replies
View Related
Feb 28, 2009
I have MS Office 2003 with Excel and am comfortable creating formulas and have fun making spreadsheets, but have little experience with multiple tabs. They frustrate me. I'd like to set up a file that would essentially be a year of bookkeeping. Everything in view would be income, outgo, anticipated expenses, and running cash flow for a month. I'd like 12 or 14 tabs (1 per month plus overview and recap capability). If I make any format changes in row height or column width, I'd like those changes to apply to all tabs without having to copy the new layout to each of the other tabs. I think that would run the risk of also copying one months expenses/income erroneously to another month. Is this possible? I'd like to stretch a column, add column or row and have the changes apply to all tabs.
Also, most of the time, I like to have a tinted background behind my lists, but if a cost item needs to be copied from a green "expense list" to a blue "accounts paid" list, the color comes along. Can I copy only the text, not the format?
View 6 Replies
View Related
Oct 7, 2006
I receive a worksheet every month, Say sheet A, and then I change its layout as per our need, call it sheet B. here is an example. (Pleae see attached),
All I need to know is there a way to change lay out every month without cutting and pasting row up and down. I think if I insert a column in sheet A (received sheet) as column A and use match function with Prior months sheet B, and sort sheet A by column A (matching numbers), BUT the problem is the blank spaces.
View 9 Replies
View Related
Aug 4, 2014
Are there any forumla to mass change excel layout of info,
From (3 lines):
EMP_NO EMP_NAME JOIN_DATE CITIZEN_CODEJOB_CODE QUALIFICATION
70446Thaung Thaung Nge 20060630MYA Enrolled NBA
70446Thaung Thaung Nge 20060630MYA Enrolled NDiploma in Nursing
70446Thaung Thaung Nge 20060631MYA Enrolled NSNB EN Licensure Examination
Into
This (1line)
EMP_NO EMP_NAME JOIN_DATE CITIZEN_CODEJOB_CODE QUALIFICATION (1)QUALIFICATION (2)QUALIFICATION (3)
70446Thaung Thaung Nge 20060630MYA Enrolled NBA Diploma in NursingDiploma in Nursing SNB EN Licensure Examination
View 3 Replies
View Related
Apr 9, 2014
I'm a graphic designer with a few years of experience incorporating Excel content in design layouts, and for the first time I've been asked to use Excel to layout content. The client has a complicated spreadsheet that works as an interactive form, taking responses and running them through a hidden page of secret-sauce analytics before outputing results to a final report.
The thing is, they are hoping to use it in person with clients on an iPad, and then also print out all of the individual pages as a consistent-looking booklet.
I can make an excel page look good, but the printing just isn't going according to plan. When I go to print it distorts the pages. I can resize it to print, but then it doesn't work onscreen so well. And that's for one page: in a best-case scenario I'd need all of the pages to work for print and screen AND use the same scale so that they all look uniform.
how to use Excel for clean, consistant page layouts?
View 3 Replies
View Related
Feb 27, 2009
I have a worksheet that look something like the excel.jpg but hundreds of rolls instead
I wonder if there some way i can convert it into something like exel2.jpg without having to do it manually
if excel is not capable can i use access to do it ...
View 7 Replies
View Related
Jun 4, 2013
I have 2 excel files. 1 has 10 columns and about 20.000 rows. Each row is a customer entry and each column specifies data about the customer (column 1 first name, column 2 last name, column 3 email, column 4 city and so on). The 2nd excel file only contains 1 column with 4000 email addresses in it. These are email addresses of people who opted out of our newsletter and other marketing channels.
Now I want to have excel delete all the rows of the first file that correspond to the email addresses of the 2nd file. Obviously the email addresses in both files are identical, though the 2nd one contains fewer entries and only those people we don't want to continue sending emails to.
View 4 Replies
View Related
Jun 12, 2014
I received a CSV file with 2500 entries, but instead of being in 1 column, from 1 - 2500, they are spread across the top of the spreadsheet (1 in each header column). How do I make them go down instead of across??
View 2 Replies
View Related
Nov 27, 2008
My question is explained inside the workbook. Changing a Table layout
View 5 Replies
View Related
Jul 9, 2007
I do not know much about macros so thats is possibly an easy problem to solve. My problem is basically rearranging the rows and columns. Example is below.
M.N= Material Name C.N= Country Name
M.N C.N
1 A
1 B
1 C
1 G
2 A
2 H
3 C
3 F
3 K
4 A
4 C
4 E
Here i have two columns. I would like to take the country list to rows as shown below.
M.N. A B C D E F* * *
1
2
3
4
5
*
*
And if the country has that material name it should show "Y" in the crossing cell. ( eg 1 A, 4 C)
I'd appreaciate your help. If you can help me with how to do this kind of layout changes with macro, it'd be more useful i guess, because i come accross with this kind of issues so often.
View 9 Replies
View Related
