I coach High School track and recorded all the kids workouts in DATAEASE - I had to upgrade my PC - (Dataease no longer works) so I am trying to record the kids workouts in EXCEL and be able to produce reports either by Code or NAME. Attached worksheet - SHEET 1 - data entered / SHEET 2 - is a report I would like to produce.
View 3 Replies
How to write code to produce 17 rows of 0s, then 17 rows of 1s ....all the way up to 120. So that would be 2040 rows altogether. It takes forever to do it manually.
View 5 Replies View RelatedI have an expense workbook (Data) with 4 columns (ID, Item, Cost, Date). I would like to create macros that will generate 3 different reports and write to 3 different worksheets.
The first report is sorted and sum up the cost for each item. Please see the worksheet "Item".
The second report is sorted and sum up the cost for each part ID. Please see the worksheet "ID".
The third report is the cost for each month and Year-to-Date cost right next to it. Please see worksheet "Summary".
write the macros for each of these reports assuming that we don't know the number of rows in the "Data" worksheet.
I have this concept of a table of responses that have to be generated every month or on command. These reports export from a website into a certain format, its easy for me to adjust it so it can populate a the table. Although, i want to incorparate my date picker so everytime i pick a certain date a diffrent set of data will populate from the report. it seems very possible, just need to know where to go from. Either a VBA code or a fomula to allow for this to happen.
Heres and example..(cell linked to date picker)........column1 column2 column3,(date picker from microsoft),,each has a corresponding month
.....................................................................jan
.....................................................................feb
.....................................................................etc.
say i pick january, i want these columns to give me my data from a sheet or other workbook(the january report). i want everything linked so it will do this for virtually every month of the year. ive attached an example, the only thing i can think of is to make the cell that changes with when i pick a date to link to the report criteria.
How do I write my code to add a tab labeled "Reports".
This is the code the Macro Recorder gave me, but it does not work.
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Reports"
Sheets("SetUp").Select
I have 2 sheets of data one for data the second for input and calculations.
SHEET 1 (Data)
HEADINGS - FROM / NUMBER
A / 81
C / 99
E / 01
SHEET 2 (Input & Calculations)HEADINGS - FROM / TO / FROM val / TO val
I want to be able to stick "A" in Col1 (FROM) and the number 81 to automatically appear in Col3 (FROM Val). I am unabel to use LOOKUP as my Pocket PC doesen't recognise it.. VLOOKUP and HLOOKUP are ok though, I just can't seem to get them to work for me.
Im wondering is there any way/method to make a certain number of cells randomly produce to an amount you want.
For Example:
1 92
2 150
3 37
4 86
5 38
6 97
7 =500
I have 2 XL sheets with the below data.
Sheet 1
EMP# Ename
111 AAA
222 BBB
333 CCC
Sheet 2
Emp# Marks
222 80
111 90
333 60
Now i want a macro which will compare the data of the two XL sheets with the help of Emp# and produce a new XL sheet which will look like this
Emp# Ename Marks
111 AAA 90
222 BBB 80
333 CCC 60
Any formula where I can in a single cell have 18 Nov - 22 Nov as an output, and drag it to the right so the next cell shows the 25 Nov - 29 Nov and so on. i.e. just the weekday range, in that format?
View 3 Replies View RelatedAny 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?
View 14 Replies View RelatedI have a set of unique customer names in a column A, say A1 to A200 in my data sheet. These customers have route numbers assigned to them (defined by the client) column B - B1 to B200. The route no.s are 1,2,3,4,5,6,7, & 8. The column lists can be of varying lengths but no more than 500 rows. I want to relate the routes to 8 separate sheets in the workbook. In each sheet I want to extract a column of customers based on the route number. So if the first customer in column A is route '1' (column B) it populates the first entry in sheet 'Route 1' with the customer name as so on for the rest of the sheets.
View 9 Replies View RelatedI was trying to produce a list of unique values from a much larger list. I'm using this array function -
{=IFERROR(INDEX(Sheet2!$B$2:$B$100,MATCH(0,COUNTIF($B$1:B1,Sheet2!$B$2:$B$100))), "")}
- which is marvellous and gives me exactly what I want. However my test book only has this dragged down 10 rows and already it takes 20-30 seconds to calculate. The real workbook is likely to stretch to several hundred rows, and to make matters that little more complex where I have Sheet2!$B$2:$B$100 this will relate to a completely separate book on a company network.
Is there any more efficient method of creating this list or will I just have to bear with the calculation speed?
I have the following data which is download as csv, i need a macro to produce result in sheet2, notice if there are duplicate stmt or ledg for the same case No then the units need to be consolidated as shown in sheet2
Outstanding Holdings Case ReportAs At Thursday 4 June, 2009 16:15AccountSourceCase No.Ledg StmtValue DateMessage FeedSecurity CodeDescriptionCcy CodeUnitsExceptionUnsettle UnitsCase TypeKnown SecurityMatch NoCase AgeAge BreakAgent Code:DERIVHOLD ABERDEENJPMC.AEIABIDERIV562272Ledg4-May-09JPMC.AEIABIXT0906AUS 10YR 0309 BOND FAUD-440Hold Singleton CaseYes305779830LATEST CASE NOTES:5-May-09FuturesJPMC.AEIABIDERIV569967Ledg1-Jun-09JPMC.AEIABIYT0906AUS 3YR 0609 BOND FUAUD7-70Hold Singleton CaseYes2599402LATEST CASE NOTES:2-Jun-09FuturesItem Count : 2JPMC.AEIABIFA1569598Ledg29-May-09JPMC.AEIABIQTC0813QUEENSLAND TREASURY CORP 140813 6.00 GBAUD150000015000000Hold Net DifferenceYes3598143JPMC.AEIABIFA1569598Stmt29-May-09305496516891QUEENSLAND TREASURY CORP 6+ BDS 14/ AUG/2013..........
If I put a formular in cell A2 "A1*.10" how can I get the result to come back negative if there are no negative numbers involved?
View 2 Replies View RelatedFiles :- [url]
i have upload 2 worksheets (in a zip file)
i have attached my 2 files,
1 is report of Feb 2007 & other is March 2007
on 1 Branch,
i got 200 Branches reports & i have to compare the people recored and have to identify following things
1) in which Days bukket is fall in Previous report ( compare with current)
2) if the Account # doesnot match then it should be HIGHLighted!
what i have to do is to compare the Account # with Previous & add a New Column & write the previous days (in number) plus highlight or show some remards that doesnot exisits in last report,
I am trying to create a report that takes two user inputs and looks them up on another worksheet in the workbook and returns the right value. As an example I have the user input a date and a job location and on another sheet there is also a date and a job location that has a population associated with it. Is there a way to return this information so that it changes whenever the user changes these two criteria
View 9 Replies View RelatedI'd like to created a 'Filtered Report' in which data changes based on my selection. For example, in this scenario I have 2 columns, 'Part Type'(you may pick Part1, Part2, Part3) and the 'Results' columns. I have displayed 100 entries(which means at this point they're all displayed, no filters).
When I apply the filter, and I choose Part Type to display Part1 and its "Results', I would like to dynamically be able to display somewhere in that Sheet something like this:
Part Type: Part1
Total: This would be a =sum(of the 2 cells below, in this case it'd be 17)
Pass: 14
Fail: 3
But I'm not sure how to dynamically count it based on what I've filtered, so when I change to Part2, it would give me:
Part Type: Part2
Total: This would be another =sum of the 2 cells below
Pass: 25
Fail: 4
I use excel pivot tables a lot for most of my information gathering for reports that I produce. Problem I keep running into is the 64k row limit. I work with up to 200K rows of excel data at a time and just need to pick out a summary of certain columns. I have read (mostly adverts) good things about crystal reports and have been considering giving it a try. It will be a steep learning curve for me, but I was wondering have this group any views on the program before I splash out on books, DVD courses etc.
View 6 Replies View RelatedI have an Excel Spreadsheet WorkSheet Analysis Work there are two reports side by side. A) Source of Truth & B) Analysis Studio.
What i'm trying to do is find out if Analysis Studio Matches off with the Source of Truth.
The columns in Analysis Studio being Column M & Column N have to match the Source of Truth Columns E "Cil account" & Column I "Code Name". Now the problem is that the names in Analysis studio Column M Example Cash is Different to the Source of Truth Coloumn I Example Cash Assets. This is what I would match as Yes being the Same as it is. The other Problem is Analysis Studio Column N has the Account & Name in one cell whilst the Source of Truth has both these in separate Columns. I’m happy if it can just match off Column E being the account to the Analysis Studio Column N. Ideally would love the results to fall either go in Column O being YES it matches or Column P it does not match the Source of Truth.
Am looking for an easy way to produce a list of combinations (maximum 6 numbers) from a range of numbers listed in 6 different columns:
Example
Column A contains : 1,2,3
Column B contains : 7,8,11,15
Column C contains : 12,16,18,19
Column D contains : 17,30,31
Column E contains : 30,31,32,33,34
Column F contains : 37,39,40
The rules are:
In each combination of 6 numbers, numbers should always be taken from ALL 6 columns. In each combination, numbers cannot repeat.
show me a formula to arrive the results.
is it possible to compare two sets of data (can be numerica or alpha) and produce a different outcome comments.
Attached workbook might explain better.
I have a column of data that requires the cell to be blank if the value is 00/01/1900. The data is linked to another cell. IE: cell E3 is linked to cell $D$3 The cell is formatted for dates in the format shown.
View 7 Replies View Relatedi have a purchase order sheet
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 would like excel to automatically suggest the save as file name and location for my workbook when I click the save, save as or close options. The file name should be a combination of values in cells A1 and A2 of Sheet1. The location should be a folder named "Bill" in C:/My Documents. I think I can do it with a beforesave code but I am new to vba.
View 13 Replies View RelatedI 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.
How do you produce a drop down menu in a single cell in Excel
View 9 Replies View RelatedI was trying to use an if statement to produce results based on 2 cells. The number in the 1st cell can be the number 1 to 5, but each number has critera with it. Say the cell is A1 and i enter the number 1. Then i want to check the value of Cell A5, if this is less than 40 then the cell A10 should read 250 and if A5 was over 40 then it should read 285. struggled to get a formula properly to work.
Here are the values for each number: A1 = 1 with cell A5
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 am creating an IF formula that will produce the current date in one cell if another item has been entered in another cell.
For example
cell g (accounts) is blank, cell H (date found).
Once the correct account is entered into cell g the date will autofill in H.
The formula i was using is =IF(G1"",TODAY(),"")
When I enter the function below as a User Defined Function it gives me a "#NAME?" error. I entered in J12 as:
Code:
"=condense(D12,E12,F12,G12,H12)"
I have values for each column is row 12.
Code:
Function condense(ByVal d1, ByVal d2, ByVal d3, ByVal d4, ByVal d5)
condense = d1 & d2 & d3 & d4 & d5
End Function
How do produce a 5 position number from 5 single numbers?
