I have a workbook of about 30 worksheets which makes extensive use of Data Validation. Essentially, data entry cells is validated against values within some predefined ranges (there are 6 ranges in total).
I am preparing documentation for this workbook and would like to document the data validation being used. Is there a way to extract this information? Where/How is it stored?
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 RelatedI'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies View RelatedI have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies View RelatedI have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
I added an attachment with an example. You will see a matrix, with in the most left column an ID, then the headers of the matrix has different numbers that mean something.
What I want to do is, you fill in a number in the combobox, then press on a button and a report will be made on a new worksheet. With a list of the numbers and dates in the matrix that the ID has. Like shown in the example sheet in the workbook.
breeze.
Here is my situation:
X Y Z
Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep
A
B
C
The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.
I have attached a spreadsheet that has the following tabs: powerhouse report, influent clarificatioin report, and data history. The PH report and Influent report are supposed to take data from the data history tab. which is named POWER. My problem is that the PH report does take data from the data history tab but the Influent report doesn't.
View 2 Replies View RelatedI would like to transfer data from one tab to another to for a report.
First off I would like to take all the rows that have "O" in column B. But only if the year in coloumn Q or R is this year. (Q is order data so I show all new orders taken this year) (R is despatch date so I show all orders despatched this year)
There will be around 25 orders total
I would then like to add a total to the bottom of this set of data.
Then move 4 rows down and bring all the data that has a status "HP" in column B regardless of any other criteria.
There will be upto about 100 Hot prospects (HP's)
My data is held on tab "BRAND" and I would like to generate the report on "BRAND HP"
My data has a header row in row 1 and holds data from A:T
The data is of ever increasing length.
I want to create a report from an existing excel spreadsheet that will show the first row with column headings. I only want certain columns from the spreadsheet to show on the report (see attached). On the left (1st column), I want to show the data from each of the last 7 days. Then I want to show data for rolling last year to date, then for last rolling year to date, and finally for last rolling 30 days.
View 2 Replies View RelatedI have a large list of items (up to 3000) that are listed by category, description, part number and weight in columns C-F. Column A is where a user will enter a quantity in the rows of the items that they want. Once the user is done picking their items they hit a button that I have written a macro in, to copy and paste only the rows that contain a quantity in, on to another sheet. My problem is that for these items (that contain a quantity) I need to have a line number (in column B) that starts at 1 and counts up for each line item. Issues to consider:
-Line item 1 may not always be the top item. (because the user may not choose the top item)
-There will be spaces inbetween the line items since the user may not pick all of their chosen items in order.
-Users can pick an item at the bottom then pick one above it. (The Line item numbers will need to readjust to include the new number in the middle)
This has been bugging for a few days now cause whenever I think I finally get it I keep creating circular references
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies View Related I have created a excel document. Would like to create report using the data.
create reports using excel data
I need to extract and format a large data base of county real estate tax parcels for a report. See attached. How can I combine these or is it possible.
View 2 Replies View RelatedI have made several stabs at extracting data from an inventory report, but keep hitting roadblocks and would appreciate some help in my 1st VBA project.
I have attached a spreadsheet with sample data - the data is in a fixed format, and I have overwritten unnecessary data, but all the data to be extracted is in Columns D, O, and P.
My approach was to go down column D testing for non-blank fields that do not begin with "Total", thus creating an array of item names. Then I would add the code that would go across adding Quantity and Value into the array.
trying to do anything other than the basics with Excel. I would like creating a macro to import data tables contained in a report that I receive in HTML format.
The Report looks like the following. I de-identified the data
VRAAPCO1 VER 8.1 VANMANDY ADMINISTRATOR
PAGE 1 01201010 CONNECT SUMMARY CPU 046E16
REPORT DATED: JUN 12, 2012 17:53
INFORMATION AS OF: JUN 11, 2012 23:09
EXTRACT FILE(S) USED:
DDName=SVSAM DSN=SYSL.VRA.SVSAM.EXTRACT
DDName=MVSAM DSN=SYSL.VRA.MVSAM.EXTRACT
MASKING CRITERIA: MGROUP=FSCSC OR MGROUP=FSCORE OR MGROUP=FSTECH OR MGROUP=FSBSM OR MGROUP=FSCCS OR MGROUP=FSEMFE OR
[Code] ........
The column group represents a security profile for an application. I am auditing the access to that application. They either start with the '#' symbol or the '@' symbol.
I receive this report once a quarter. This being my first time. I had to manually cut and paste the data tables from the HTML report file into a spreadsheet.
This is what I would like to be able to do in excel using VBA. The report name is the same every time. 'FS.ASET.Users.htm'
1. Would like to create excel spreadsheet with a macro button and master worksheet that already has the headers.
Group Userid User Name Owner Create Date Special Operations
Auditor Revoked ADSP GRPACC UACC Authority
2. That looks in a network directory(will be the same everytime) for this htm file(will be same name every time)
3. Pull only rows that start with # or @. Place them into single worksheet with sorted by 'Group' ascending.
4. A stretch goal would be to have the macro create a different worksheet within the spreadsheet for each profile name, I.e.,@UFSGUAR, #ZCBSAU, and copy only those rows that start with that profile name into each subsequent worksheet.
First I copy and paste the text into Excel. All of the data is in column A because of varying lengths, which seems to work fine. I've already written VBA code (shown at the end of this posting) that reads the contents of each cell in column A, pulls out the key information (like the actual error code or invoice #), stores the fields in variables, switches to a separate tab that has a more user-friendly layout, locates the next empty cell in the appropriate column for the field, and puts the variable contents into the cell. I realize that the code currently has some variables that aren't being used and other basic issues, but that's not really what I'm after at this point.
The report itself includes billing errors, with each billing error separated by a series of asterisks. My current code works just fine for sections of the report like the first section in the sample below, where the billing error only contains a single invoice, error code, description, and action item. If the entire report was formatted in that manner, my task would be finished because it does those sections perfectly well.
But...if you notice in the sample report below, the third billing error contains more than one invoice, error code, description, and action item. Obviously, using my current code, when the macro encounters that section, it continues to function, but the rows do not align properly in the separate worksheet because there are extra invoices, error codes, descriptions, and action items. I just cannot figure out a way to have the macro keep the information aligned. In other words, in the third section of the sample report below, the billing error contains more than one invoice and item.
One approach that I've thought of is to try to count the number of rows between the cell containing the previous set of asterisks and the cell containing the next set of asterisks, because it's the information between each set of asterisks that I need to work with. But I haven't found a way to do that and don't even know if that's the direction I need to go or not. I'm pretty much lost at this point.
Here's a sample of the text report as it appears in column A (the fields are actually longer than this - I tried to simplify as much as I could for this posting): ....
It does everything I need it to do but I would like it to sort 3 columns and then merge the data. I have included a sample with a "WishReport" tab the shows what I am looking for. If possible (not imperative) I would like it to add a "unit cost" column and provide a grand total.
Option Explicit
Public Const SZ_INPUT As String = "SheetData"
Public Const SZ_OUTPUT As String = "Report"
Public Const I_FIRST As Integer = 0
Public Const I_LAST As Integer = 1
Public Const I_DATA As Integer = 2
Public Const ROW_HEADER As Long = 1
Public Const ROW_DATA As Long = 2
Public Const COL_FIRST As Integer = 1
Public Const COL_PROJECT As Integer = 2
Public Const COL_LAST As Integer = 5
Sub CombineProjectData()
Dim lInputRows(I_FIRST To I_DATA) As Long
Dim lFirstInstance As Long, lOutputRow As Long
Dim szData As String
Dim iCurrColumn As Integer
Call FindRowArray(lInputRows(), , COL_PROJECT)...............
im creating a spreadsheet at work that gathers project and FTE figures.
I have attached a copy, but ive had to totally simplyfy it to get it to the required uploadable size.
Basically each team member goes into the sheet tab with their name on it (the full version has about 40 sheets), enters the total hours per week they will be spending on each project.
Then the manager opens the spreadsheet, clicks on get data then enters the date. Data for the selected date is then shown. However I need excel to go into each persons sheet, take the names of the projects that will be worked on (obviously the ones that are not blank) then report the names onto the front sheet under "name of projects".
can anyone help me with this? as you will see, im self taught with vba etc so its probably not the best but im trying.
I'm trying to make an excel spreadsheet for a Kareoke business - what I want to do is have a database of CDs that they have in 1 sheet - this will include Song Name, Artist, CD Title, Track Number and CD Number - then I want the user to be able to go into the next sheet and type in the CD Number into a specified cell and thus will import all the information for that CD number into the required fields. I have attached a copy of what I sort of want it to look like. I don't really know the functions of Excel, but I'm quite computer savvy and should be able to work it out with instruction - please note, I've never used a "macro" and don't know what they are - so if I need to use a "macro" would you be able to give me a link to somewhere that explains what they are...and how to use them?
View 3 Replies View RelatedI 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 RelatedI've been working on this for a while unsuccessfully. Any way I can create a simple report that is autopopulated with the data from a schedule. The report worksheet would need to be organized by division and AM/PM. see attached for further explanation. I've tried things like "Index(Match)", but it's too cumbersome and not automated.
View 5 Replies View RelatedI have a report (roughly 4000 lines) which I need to rearrange for pivot/power-pivot use.
The structure for each row/record is:
col1 geography
col2 area
col3 customer
col4 product
col5 price
col6-65 monthly sales units - 5 years (columns labelled Jan 2009, Feb 2009 .... Dec 2013)
I want to rearrange the data as:
col 1-5 unchanged
col 6 month (data Jan 2009, Feb 2009, etc.)
col 7 sales units
Currently i have 4000 rows/records (each containing 65 fields). Iwant to end up with 4000 x 60 or 240,000 rows/records of 7 fields each. Is this possible through a data import wizard or VBA routine?
I am connecting Excel to a database and there is a field called Data Created which is timestamp. Data comes out nicely in a Pivot Table
1. I would like to be able to group by month but the grouping fiction is grayed out
2. How can we convert the timestamp to only date format. I am able to select only Date for that column but it seems that it keeps the time, even though it only shows the date in the pivot table the bar above shows date and time.
I am interested in finding vba code that I can enter as an add-on for a weekly training report that I receive.
An example of the weekly report is attached. A Computer based training program populates the reporting tool with the date that the course was completed. Each week a report is generated as attached with the dates completed filled in the matrix.
The goal is to: Report the total percentage of training completed. I would like to be able to run a vba add-in that will determine what rows have entries. Perform a countA on the date fields. Sum the counta's and populate a cell with the % complete. In one simple touch of a button or keyboard function that can be ran each week without editing the code. These reports are filtered by department and the size of the matrix change all the time.
The hang up I am having is making the code dymanic enough to figure out what cells are filled and calculate and populate by that factor.
I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet to help.
The suggestion of an Index with a Match Array worked but the problem is that my spreadsheet has over 5000 rows and my data has to meet three criteria = 15000 cells to cross reference. And I need to set up a spreadsheet that would do about 300 arrays. So obviously my excel is soooo slow I can't even get 10% complete.
Any other suggestions? I've read (from Dave I think) that database is a powerful alternative but have not been able to find any info on this and it's not an Excel function. For your reference I'm attaching a demo spreadsheet of what I'm trying to do with the two Index + Match arrays that work for small files, not mine.
I looked into Pivot Tables but it can't handle all the row and column headings.
I am building a tracking process for marketing and relationship management purposes. My company has a database (Advisor's Assistant - which the server is on site) that is for lack of a better term very limited. I have tried to identify if we have the capability to use SQL in excel to pull the information we want but that avenue looks bleak, since my co-worker that is pretty tech-savvy has had very little luck working with the database provider to get to information we want.
Anyway, I have determined that I can get the information I am interested in by way of several reports that the current database will do and printing them to a .pdf file. Then by way of a program called Able to Extracted I can get them into an excel format. The problem I am trying to solve is using excel to pull the information I want out of these twice converted reports into a format that means something in excel.
Only the reports reach excel they have many empty cells and some of the information is offset and does not follow the same pattern as you scroll down through the report. I have attached part of one of the reports. I would like to automate the process of searching the data and creating a new format that I can use a pivot table to create reports off.
I'm going to try to explain this as accurately as possible. This is my first post, and I'm seriously struggling with VBA, but i'm confident that someone out ther will have done this before.
I have a single workbook which has a single datasheet, and further sheets representing different months of the year.
Sheet 1 ("Data") contains the following columns and data:
DateStart MileageEnd MileageBusiness TripBusiness Mileage
01/05/2008013 get 13
02/05/200813260
03/05/200826100 euro 74
04/05/2008100113 adf 13
05/05/20081131130
06/05/20081131130
07/05/20081131238 alex begg1125
08/05/2008123812510
09/05/200812511288 service 37
10/05/2008128812980
11/05/2008129813110
12/05/2008131113780
13/05/2008137814000
14/05/2008140014560
15/05/200814561490.10
I then have a series of Summary sheets which are supposed to summarise the data on this single data sheetl. But this is where I have issues.
Basically, from the list of data above (running from 1st may 2008 - 31st December 2008), I want to pull out, per month all of the trips in that month. So for May (shown above) I need the date, business trip name, and the business mileage where the business trip name <> "".
To compound the issue, the columns that these are moving into are columns 1, 2 and 6 in the summary spreadsheet.
I have used some example code from cpearsons website to give me a list of nonblank cells in the Business Trip column, and this has worked, but I can't pull the other two columns out effectively.