I 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 have a computer build worksheet I use to configure the cost of my notebooks.
There are 2 worksheets, on one I have all my data seperated in 2 colums. One column is the name say Intel Core-Duo 2500 2.0 Ghz, and next to it the price, say $300.
The other worksheet is the actual form for "building" my systems. Right now I am using combo boxes that feed their output to another cell, then a 3rd cell with complex nested IF statements to retrieve the data from the 1st worksheet.
This way I have the form look like
1. Combobox selection: Intel Core-Duo 2500 2.0 Ghz
2. It feeds a second cell its value 1 (if this is the first value in the list)
3. 3rd Cell reads second cell value with a complex nested IF statement: 1 and goes back to the data sheet and reads the price from the second column: $300
This is a total pain in the butt when I want to add products or change their order in the data sheet. Can someone point me to a direction that this could be made easier, a good book on the subject and what I should be looking for and whatever syntax I need.
Here is the file: [url] You need to have iterations set for the sheet to work properly because there are circular references to calculate percentages of the total for shipping insurance paypal fees and ebay fees etc.
I have a spreadsheet and im trying to create a sports simulator..
Up the top I have a drop down menu with the teams listed, now what I want to do is when I click on the team I want the players from that team listed below it..
Could someone explain to me how to acheive that?
For Example.. Where it says 'Brisbane Broncos'... Say I change that to 'Canterbury Bulldogs' when I change it to that I want the spreadsheet to update cells A5:21 with the 'Bulldogs' players..
I have my data with column A being the Week ending date (a saturday date) for which the data was entered. In column B it is simply the Month that the data was entered. On another tab...the user can select weekly or monthly. What I would like is a validation drop-down that corresponds to the selection.
IE - If weekly is chosen, the validation will allow the user to select a week that is found in Column A in the data. If monthly is chosen, the validation will show the months from Column B instead. Is it possible to have one validation dropdown, that the validation LIST can be conditionally select based on input in another cell? (A1 being Weekly or Monthly - and B1 being where the validation dropdown would be)
I'm having major issues getting this to work correctly. Is there a way in VBA to code conditional formatting so the entire row of a range's color is changed if the condition is met? I have about 7 different colors I'm using, but if I use the following code (only partial is shown below), it only changes the specific cell (in my case, in column A), and I want the entire row (say, A4-AI4) to change colors, dependent upon what is in cell A4.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set MyPage = Range("$A$4:$AI$33")
For Each cell In MyPage
If cell.Value = "LH_SAS_1603" Then
Selection.FormatConditions(1).Interior.ColorIndex = 40
End If
If cell.Value = "LH_SAT_1603" Then
cell.Interior.ColorIndex = 37
End If
If cell.Value = "MH2" Then
cell.Interior.ColorIndex = 39
End If
Next
End Sub
I also tried the following, but it never stops looping through since I obvi didn't code it correctly:
Range("A4:AI33").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A$4=$A$37"
Selection.FormatConditions(1).Interior.ColorIndex = 37
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A$4=$A$39"
Selection.FormatConditions(2).Interior.ColorIndex = 40
Range("A37").Select
End Sub
I am trying to create a macro that works with a range of cells and identify the five largest numbers in the range. The following are the steps.
1. I select a range of cells and run the marco.
2. The macro will identify the largest number in the selection and make the cell color red.
3. Then it will find the cell with next largest number and turn the cell orange...and so on till the 5th largest number.
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)
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 Replies View RelatedWe 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)))
I 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.
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View RelatedI tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:
1. 4 applications per date
2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application.
3. once a cell is clicked (i.e. marked), it cannot be altered.
4. each click will automatically increase the total by 1.
I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m
ad = Mid(ActiveCell.Address, 2, 1)
m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................
I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
I am working on a project where user has to select an item from a listbox, however there is a condition that if user select an item, other items of listbox should be disabled so that he/she can not select any other item.
Is there any way to do that as I tried
VB : Listbox.enabled = False
and
VB : Listbox.locket = True
in Listbox - Click/Change but no desirable results got.
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
I'm using the following code in a worksheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
col = ActiveCell.Column
Range("output") = ActiveCell.Offset(0, -(col - 4)).Value
End Sub
In case it's not obvious, the macro places the value in the active row and 4th column of the worksheet into the range "output".
The problem is, the worksheet is large and somewhat slow to recalculate. This macro forces a recalc on any selection change, but i only need it to run when the row selection changes, not the column.
I'm sure there's a straightforward way to reprogram this.
I'm trying to build an investment calculator. Tried searching "calculator" as keyword but return nothing similiar.
There are eight cell : [ A ][ B ][ C ][ D ][ E ][ F ][ G ][h]
[ H ] is price of the leverage
[A] is X balance
[b] is X lot size
[C] is X amount of money use to get x lot
[D] is X percentage of money use to get x lot
[E] is X point gain
[f] is X money gain
[G] is balance + money gain
Ignore the formulae for leverage to determine 1 lot price. Take 1 lot = 250.
What I'm looking for is when I enter any value at cell B, C or D, it will automatically calculate the value for either B, C or D cell.
Let say, the balance is 10000.
If I enter 2 at cell B. Then, it will calculate the value for cell C & D.
If I enter 750 at cell C, then it will calculate value for cell B & D.
If I enter 25 at cell D, it will calculate value for cell B & C.
how to use OR Selection in excel.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies View RelatedIf the selection (or any part of the selection) = bla bla bla
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4