The attached file contains the brief but in short I'm looking for an automatic report to be generated from a given dataset. Conditional Lookup functions could be useful, but I'm getting lost in the middle of the large data. I've tried to summarise the requirements on the file. Based on the given data the output table should show which product, which SKU have zero value in which Class by Area?
View 5 RepliesI have a schedule sheet (on week days what assignment is be to taken in concerned period).
I need a report based on the same. i.e. I need to generate a report for each individual.
I have attached a sample file, in which I explained the requirements.
I have written the VBA code to color any figure above 15, but it display rows above 15 as red including other rows.
Below is the VBA code
Code:
Sub FillCells()Dim lRow As Long, lColumn As Long
'Using Cells property to refer to range
'Loop through rows
For lRow = 1 To 10
'Loop through columns
For lColumn = 1 To 5
[code].....
Which shows the output as Workbook1.jpg But my query is how to get only rows above 15 with red color
I have a grid that I need to lookup and return the project hours for each system from the data set. The grid has the projects listed in the rows and the systems listed across the columns. The data set has a list of each projects systems hours. I am having problems with the formula when the data set has multiple records (multiple systems) for each project. It seems very simple, but I just can't get my arms around it. Please see the attached for example.
View 7 Replies View RelatedWhat I have got is a list of exams and subjects on the first sheet from cells A3 to A whatever. This list varies in length from a few exams to many dozens.
Under each exam is a list of subjects.
i.e.: Under the math exam is addition. multiplication, division etc
On columns B through ZZ and beyond. I have individuals names.
Under those individuals names (opposite the exam row) I have pull down boxes saying if the individual has passed failed or did not sit .. etc.
What I would like to be able to do is to automatically generate a second sheet with the individuals name on it with a report of what exams they took with what result. Of course it would be nice it was nicely formatted with the headings of all the different exams and the subjects taken arranged in order.
If an exam was not taken it should not appear in the generated sheet
I have a fairly good knowledge of Visual Basic though I have not had much to do with VB for scripting.
The following code works very well up to a point. See previous thread Automatic Report Generation.
As I use it to change my yearly summary sheet all at once. However as it stands I need to insert a seperate line of code for each month and cell that I need data from I will have over 2000 seperate lines when I am done. Also if the file does not exsist yet then the cell returns a #REF (error 2023). And none of the other formulas in spread sheet will work. Is there a way to have the cell return a zero if the file has not been created yet?
Is there a way to try and automate more of the code below? Cell references will be all over the place. All the months for the year will be needed.
I am looking for a formula that would lookup multiple values/result, corresponding to one given value. I have attached a spreadsheet.
Column A and Column B consists of data set.
Column J has the "LOOKUP VALUE"
Column K is where we want the lookup results. We would require the result to be in ascending order which would determine the distribution.
Example1.xlsx
want to generate automatic report from excel work sheet
View 1 Replies View RelatedI came across a code on your website that looks across numerous sheets and stops at the first match. I have a similar need but only i do not want the code to stop when it has found the first match. I want the code to lookup from 1 column in 1 sheet to another workbook with 20 odd sheets, then want it to return a findings report or show all matches.
View 3 Replies View RelatedI would like to automate a sheet to report statistics of projects based on a single cell that is the year that a user manually inserts. I've got a separate worksheet with all dates manually set up for next 10 years and would like it to be read by formulae in a reporting worksheet based on the year that user enters.
So if I specify year 2014/15 (financial year), I would like the cell C1 under "Reporting" sheet to display 1/07/14 predetermined in "Dates" B3, if I specify 2015/16, the cell C1 will read 1/07/15 from "Dates" B6 and so on.
I could do it with "IF" function but there will be too many nested IF functions in a lot of cells (hundreds if not thousands). This may make my file size very large.
Attached is a simplified example of what I mean.
I have been asked to create a clash grid that shows when promotions for specific customers are on for specific products.
I've got all the information collated, and I suck it all up into a cover sheet that splits it by product, by customer, and then shows the weeks each product is on promotion. The issue I have, is I wish to colour code each customer so it is easier to read for those who aren't excel savvy.
I've tried conditionally formatting it, but have stumbled a couple of times. I can write an 'if' statement for it, but can't work out how to get the conditional formatting to follow the statement (I'm not sure this is the best way to do it, but figured it was worth explaining).
Attached is a 'How it is' and 'What I want it to be'.
StarkeyExample.xlsx
In the attached spreadsheet, I have a limit value which is used to set the color of other cells based on their values.
So if the value is greater than limit, the color is red.
When I change the limit, I do see the changes in color.
However, I also want to show only the red values.
I am having trouble finding an efficient way to expand a set of data that I have by adding another column to it.
Look at attachment : help.xlsx‎
I can do it manually but I have 5000+ rows of data that I need to selectively expand do accommodate the data from the new data set.
I 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.
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 am using excell for our estimating sheets. I am trying to automate some of the processes to ensure there are less mistakes.
I would like to automate the process so that if a number is put into the quantity column then certain cells from that row are copied to a different sheet.
I just dont know how to do it? Do I use Macros or Formulas?
I'm trying to set up a conditional format using a two way lookup. The chart is 15 colums across and perhaps 500 down. Index / Match functions do find the correct value - BUT - the challenge is there ARE duplicate values in either the column or row that are "all" getting highlighted with the conditional format (color a cell).
View 3 Replies View RelatedI'm new to excel and am attempting to use an IF(AND function that requires the use of two LOOKUP tables for the IF conditions, I have been unable so far to generate the answer I'm looking for.
I have a set of data in two columns, column A which has a series of dates from 1/09/2013 - 30/09/2013 (multiple for each date), and column F which has a series of 4 digit numbers (employee numbers). I also have two columns where I can enter in values for LOOKUP tables, one column for dates (O2:O4) and another for employee numbers (Q2:Q17).
What I am trying to achieve is a True/False answer in a final column for IF A2 = value from column (table) O2:O4, AND F2 = value from column (table) Q2:Q17. For the querry to be true the values in column A and column F must meet both conditions, be from a date in column O2:O4 and also be an employee from column Q2:Q17
=IF(AND(A2=LOOKUP(A2,O2:O4),F2=LOOKUP(F2,Q2:Q17),"Yes","No")
Currently I get a #NA error and I know its due to my formula not being anywhere near correct, as far as I can tell it's a result of the LOOKUP part of the formula I'm attempting to create, as it generates a #NA area when the data does not meet the criteria in the columns (tables), I have attempted to include a ISNA section to the formula to alter the result if the data does not meet the two conditions but when applied to the whole data set it only responds with a False answer even if the data meets both conditions.
I need to return the project codes for the project with the top 5 sales volumes within each Region for each product. If say, I have the following relevant columns (Project, ProdCode, Region, Sales) in columns A:D
View 9 Replies View RelatedI am trying to modify this formula to do one more thing, but I can't quite figure out how to do it. I have part 1 and 2, but part 3 is giving me a very hard time. The formula is not working correctly. It is supposed to do several things.
Do an HLookup for a row and return the last value in the row.If there is no value (blank), the value should be "Incomplete."If HLookup finds a value of "Fail," it should look to see if there is a value of "Pass" BEFORE (to the left of) "Fail." If this exists, then it should return a value of "OK."
Here is what I have so far....
Is there a way to specify conditional formatting using an Hlookup function? I want to format a cell with a specified color based on a horizontal lookup (see attached).
View 4 Replies View RelatedData for once source
Column A shipping container numbers (alpha & numerical)
Column B the container size code
Column C relevant date of arrival
Data from another source
Column D shipping container numbers (alpha & numerical)
Column E the container size code
Column F relevant date of shipment
What I want is:
If Column A containers match with Column B containers Then in Column G the containers that matched and Column H the container size code
Column I the date of arrival
Column J the date of shipment
Vlookup seem to be an answer, but can’t get it to work. I am not good in VB, so may be formulas would be better Named range and IF & Match FORMULA may be?!! The list will be approximately 20,000 numbers long. Have attached the sample file.
I have an "actual build" worksheet, which allows the user to select which materials were used, material properties (e.g. material grade etc.), including its corresponding length. The user can enter all these in manually, or select the material name from a pre-defined list (Reference worksheet contains this information), and then the associated properties are automatic lookups from the Reference worksheet.
So all this is fine / completed, and the LOOKUP returns its theoretical length from the reference table. However, its actual material lengths will almost never match the theoretical length, so what I want to happen is for a conditional format to highlight the cell, to notify the user that they need to enter a manual value. The auto-looked up length is still useful a proposed build on the worksheet, but having it highlighted to ensure it is manually entered later is the objective.
The other thing is it can't just check if it's a formula, it has to check if it is a formula with a MATCH/LOOKUP function. E.g. it needs to be unhighlighted if someone has a basic formula, ie = 2*0.42 if there two of the item etc..
Hence, I was thinking of having a conditional formula that "format only cells that contain" and then format only cells with "specific text" and "containing" and "MATCH(" text function, however this doesn't seem to work.
Length cell formula:
=OFFSET('Reference- Materials'!$N$5, MATCH($BO32, Materials_Item_Number, 0), 0) * $AH32
I recently learned how to count cells in a range based on the value from another column (excel 2007) How to count cells in a range based on the value from another column but now how I can go about returning a value from another column that matches the conditional counting. For example in the table below I'm first wanting to find the rows matching "chr15" from column A that also have a value from column B that is greater than 25,000 and less than 3,000,000. But what it I wanted to instead report the corresponding values in column C? I've played with VLOOKUP to no avail but I'm not sure if that's the right line of thinking. The answer would be the values in bold.
A B C
1 chr2 12008 AA
2 chr2 149700 BB
3 chr15 51 CC
4 chr15 5624 DD
[Code] .........
The Problem: There is data in a step chart from left to right. On a separate step chart, Conditional formatting is set to lookup values from the first step chart and highlight non empty cells in the row. The formatting should only format the first non empty cell from left to right in the row. After formatting the first non zero value in the time line the formatting should cease. Note: the conditional formatting value and the value of the cell being formatted are not the same in either value or time but are related. The formatting is to illustrate the relation
Current Formatting formula:
=VLOOKUP($B395,$B$5:$DB$211,K$242,0)
Syntax for Vlookup(Lookup_Value,Table_array,Column_index_num,Range_Lookup)
We have the following formula in our timesheets. Basically when we enter a Job number in one cell in say Tab1, it will return the matching project name in a different cell that we have next to the project number. The job number match is done in the tab named "ProjectSchedule", where all the details of the project are listed. We need to extend the match range to include another tab named "CustomSchedule", but I can't figure out how to make the first formula search jobs from both tabs.
Current working formula:
=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202,0)))
What I'm trying to accomplish, but it not working is something like this:
=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202&CustomSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202&CustomSchedule!$B$3:$B$202,0)))
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies View RelatedI have a 1000x1000 cell grid on one of the worksheets in my workbook where each cell contains one of the following four possibilities:
1) Blank
2) A string that is not defined in a lookup table (*lookup table = a named range on a different sheet)
3) A string that exactly matches an item in the lookup table
4) A long string that contains an item in the lookup table (which can be found with a FIND function, for example)
I would like to apply conditional formatting to my 1000x1000 grid, to color each cell according to the lookup table, where the last column has the name of a color that I can match with my conditional formatting setup.
I have solved everything except for No. 4).
2) is taken care of by using "Format only cells that contain" with "No Blanks"
My solution for 3) - using "Use a Formula to Determine Which Cells to Format" - uses
=VLOOKUP(A1,lookup_table,2,FALSE)="fill color name" So, for every different color I want to use, I have a different Formatting Rule in the Conditional Formatting dialog.
But 4) is tricky, because VLOOKUP can only return the result for a specific cell value.
If my cell contains France;Germany;Italy, I'd like the color to be according to what I set for "France" in my lookup table. Or, if my cell contains Germany;France;Italy, I'd like it to be set based on what I decided Germany is in the lookup table (i.e. "blue").
You can notice that one thing that is consistent is that I separate my "sub-strings" that are contained in the lookup table with a semi-colon.
So, one way might be to see if FIND finds a semi-colon, and then grab the string that goes before that character position.
I’m creating a spreadsheet to control corrective actions. Each corrective action will have a date that it needs to be completed by. When the completion date plus one day is passed, I need the cell that the completion date is in, to turn a specific colour and a warning mail to be sent via outlook When a further six days have passed I need the cell to turn a different colour again and another email is sent
Ive generated the following code to call an Outlook subroutine if the correct conditions are met. The subroutine runs ok but I can get the sheet code to run.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
If IsDate(Target.Value) And Now() = Target.Value + 1 Then
Target.Interior.ColorIndex = 3
Call Mail_with_outlook
ElseIf IsDate(Target.Value) And Now() = Target.Value + 6 Then
Target.Interior.ColorIndex = 5
Call Mail_with_outlook
End If
End If
End Sub
I'm trying to sort some data that is generated from a PDF. When I copy the PDF into Excel all of the data is placed into one cell. Is there a function or formula I can use in VBA or within the spreadsheet to accomplish this?
For example, the cell may contain "Employee John Employee Steve Employee Dave Employee Jed Employee Mark" in cell A1. I want the function to list John, Steve, Dave, Jed and Mark in separate cells. So I need to get rid of the space bar hits, the word employee and to separate the names to different cells.
A1 currently is "Employee John Employee Steve Employee Dave Employee Jed Employee Mark"
I would like to create a formula to have cells:
A2 = John
A3 = Steve
A4 = Dave
A5 = Jed
A6 = Mark