Conditional I Think If Statment Maybe More
Jan 19, 2009
Ok ive been playing about with excel for the past week to get a spreadsheep up and running so i can see the totals of some things and now its getting a little complecated for me (ive done the easy stuff lol)
Ok here is the situation and i'll try and explain what im doing as best as i can. Im trying to make a spreadsheet that shows how much a resorce can be refined so lets say in a1 to c1 i have 400 in each but i can mix a with b and b with c at the ratio of every 20 of a and c to ever 10 of b
so somewhere in my spread sheet id have the result 20 and 20 as products ato c can make 20 of product d and e i hope that makes sence?
for every 20 + 10 of a(400)+b(400) = 20
and for every 20 + 10 of c(400)+b(400) = 20
so if i had 600 300 600 instead of 400 i can only make 30 of product d of e (id have to choose)
View 14 Replies
ADVERTISEMENT
Jun 8, 2006
I have a list of codes. Some of them may be duplicated. I want to use the conditional formatting and the IF statement to highlight in a particular colour any codes that have appear twice or more. I used to know how to do this but now I cannot get it right.
View 2 Replies
View Related
Oct 22, 2004
I need to format the background color of a row using an if statement or something similar. If have a certain cell has a specific value I want the entire row that the cell is on to have a specific background color.
View 9 Replies
View Related
Sep 5, 2008
How can I write the equivelant of an if(and( statment in VB? What I would imaging it would look like is this...
If _________ = true
AndIf ________ = true Then
____________
End if
It seems like this should be simple, but after searching, and searching the help files, the internet, and this website, I am no closer to figuring this out.
View 9 Replies
View Related
Apr 7, 2009
I have another question with my red sox scores excel sheet. On my overview I want to be able to select any team, and view the indiviual stats against them. For the life of me I dont' know/can't find how to even do a simple yes no if statment to what the value in the drop down menu box is.
View 3 Replies
View Related
Jan 9, 2009
I don't even know if using the IF statement is the right function, but anyway. I am using VLOOKUP to complete a supply list, but of course if the supply name isn't on the first spreadsheet then the result is NA. Instead I want it to return 0.
View 3 Replies
View Related
Jul 16, 2008
I would like to check whether 2 conditions are satisfy in an if statement. One condition is of data type text and it is stored in a variable calls search1 whereas the other condition is of data type date and i intend to store into a variable calls search2. Could someone tell how to put search 2 into the below code. Basically, i want it to be like
if Cell A is equal to search1 and CellB is equal to search2 then
return value that is associated with the search in cell c
ActiveSheet.Cells(r, 13).FormulaArray = "=MIN(IF(Sheet1!$A$1:$A$20=""" & search1& """,Sheet1!$C$1:$C$20))"
View 9 Replies
View Related
Nov 23, 2009
why the code below is very slow with the extra if statment in it (marked with **) i have googled around and found that my for staments are correct i have tried screen updating etc. i have added a timer to see what diffrence it makes, what i have is 3 sheets 1st has about 100 rows with a number and time same as second but that only has 2 rows of the same the 3rd sheet has about 100 rows of just a number what the code does is loop through the 3rd sheet (build list) when it finds a number it looks on the first sheet to find the same number then gets the data in the second coloum and so on until it reaches a cell that says stop.
i have added a button to start this process, with the ** line in it takes 311 seconds with out it it takes 0.17 seconds
View 9 Replies
View Related
Jan 11, 2009
I have been trying to get a formatting programme to work for well over a week and no matter what I try I seem destined to fail. I am trying to use an IF statement in VBA to format a cell if the result of the IF is true. The values in the cells being formatted have been generated from a VLOOKUP statment (looking at a table on another sheet of the workbook). I have tried to use conditional formatting but cannot get past the limit of 3 conditions.
Note in the code below I have values that may appear the same but to make them unique they have a single (or double) space before and after the character. Not sure if the code I have below is workable, if anyone has a better idea please let me know (its a little long winded). I am currently self learning VBA (through forums and the like) and below is the best I have come up with... however when I try to run it I only get a Run-time error 1004, application-defined or object defined error.
View 4 Replies
View Related
Sep 3, 2009
I would like for the If statment to hide multiple labels and buttons. How can I add more than one condition after Else:?
View 3 Replies
View Related
May 31, 2009
I am trying to use an IF statment to look at 2 values, 1 from a defined range and the second based on offsetting the the first to Format a number of cells with a worksheet. Affectivley if the First Value is "E" and the value two columns to the left is a Sat or Sun I wish to format the row slightly differently from if it is a weekday. (There will be an M and a L as well)
View 5 Replies
View Related
Jul 8, 2006
If you look in the file you will see a records sheets and a form sheet. I want to see if a record has been broken. All the times are in seconds and the distances are in centimeters. How can I find a certain row, compare and act based on an if statment?
View 2 Replies
View Related
Apr 22, 2009
I decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?
View 14 Replies
View Related
Mar 20, 2013
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
View 3 Replies
View Related
Apr 15, 2014
Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:
(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.
However I can't figure out how to manipulate it into a percentile keeping the condition of
IF(DATA!$B$15:$DZ$15=1000)
View 2 Replies
View Related
Feb 9, 2009
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies
View Related
Mar 13, 2008
I have a list containing blocks of stock price values with each block representing a series of values at a given time e.g
1400 156
1400 99
1400 74
1400 86
1400 256
The number of entries may vary for each block but is never more than 60. the next block may be e.g.
14:05 15
14:05 42
etc
and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............
View 5 Replies
View Related
Feb 9, 2010
I have five fields that have a conditional format applied to them: (see attached).
Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
View 2 Replies
View Related
Feb 16, 2009
I have a dataset of annual values which I need to combine into an array. Each year's data is in a separate worksheet, and contains between 4000 and 6000 rows. I set up a master sheet listing all unique locations for the entire period of record, finally got over the tics and brain damage from that one, and put in a VLOOKUP function for each column to retreive that year's data (examples-
=VLOOKUP($A2,'1995_5580'!$A$2:$E$5581,5,FALSE) =VLOOKUP($A2,'2002_6052'!$A$2:$E$6053,5,FALSE)). Works great. I still need to get something other than #N/A for not found values, but I can figure that out. Checking the results, I discovered that a very small percentage of the locations have more than one entry. In order to make this matrix useful, I need to have a sum of all values that match a given location. VLOOKUP only gives me the first match.
Now, the problems- I can't post a workbook, because it's confidential data. I would really prefer to stick with functions and not macros, because a lot of macros seem to get picked off by the firewall (I'm working on a network drive). I've seen similar problems addressed here with PivotTable (won't work here because a large number of locations are missing in one or more years and each year has a different number of data rows) and array formulas (data is on 23 separate worksheets). I really, really don't want to go through 100K+ rows of data to manually find duplicates and enter them into the master sheet. Not only would it short out my few remaining brain cells, it's inelegant.
View 2 Replies
View Related
Apr 22, 2014
how to view changes on my spreadsheet.
I have a tab called SAP DUMP where we copy our raw data to, from here we manually copy and paste into another sheet called ORDER TRACKING SHEET. When we do a fresh dump (overwrite) of raw data into the SAP DUMP tap we need to see if the there is a new entry which needs to be copied across to the ORDER TRACKING SHEET. I've got a vlookup to show this. The one I can't work out is that on the ORDER TRACKING SHEET I also need to see if there are any entries are now no longer on the SAP DUMP tab. This is where I thought a condition format might work, color the line red if it's no longer on the SAP DUMP tap. Can't make it work.....
View 2 Replies
View Related
Apr 14, 2008
I have to determine based on average number of weeks on hand whether an Item is Overstock, Obsolete, or Normal. I've got that taken care of with some If statements (I'm sure there's a cleaner way to do this but I've not figured that out yet.)
But I need my if statements to be conditional to whether or not an item is seasonal. with 10,000 active SKU's Its a little bit much to manage those in my excel copy of the item table so I wanted to make an exclusion table where I could just key the item in and the months it would be considered active in stock. This needs to go into the If statement on the items table that looks at the current date and says if it's less than six months old it's a "new item" but if it's six months and a day then it's a normal item, then ignore both if it's seasonal and just be a seasonal item if it is on the exclusion worksheet.
I'm attaching this incase anyone can offer any ideas. It would be much appreciated. I had to delete a ton off of the spread sheet to get it to upload, and the data is dummied to keep company inventory confiential.
View 14 Replies
View Related
Dec 4, 2008
I have this spreadsheet I'm making for Christmas to keep track of my wifes and my purchases. I'm having problem with a formula tho and I'm not sure how to go about this. Basically I have a "name" column. As you will see. At the bottom I have "total spent on cristina" "total spent on jon"
I need like an "IF" condition that will look at that name column and say, "if name is Jon" add up the amounts in the "total cost" column according to what Jon spent. If name is cristina, add up her amounts.
View 3 Replies
View Related
Dec 9, 2008
In a column, there are 4 option for each cell -
"PAYMENT DUE"
"PAID"
"UNDERPAID BY..."
'OVERPAID BY..."
These are filled in by a formula
I would like to make them 4 different colours but excel only allows 3 options
View 14 Replies
View Related
Dec 22, 2008
How can I summate cells in a column *except* for those rows which contain a specific value in another column?
=SUM(A1:A10 where B<row num> !="SOLD"). I'm trying to add up the weights of items which are not yet sold.
View 3 Replies
View Related
Jan 30, 2009
I have a spreadsheet with days of week and places saved as text values, and I need to get averages for specific items,
for example,
Day of week in A1, place in A2, number in A3
I want to get in A4 say, the average of the numbers for all sundays at cambridge.
Any ideas how I could do this?
View 9 Replies
View Related
Mar 3, 2009
Maybe I should say conditional product of conditional sum. Eitherway, the values in column J are summed if P is column R for the repective row.
View 4 Replies
View Related
Mar 5, 2009
I have a hard time when it comes to "If then" statements and conditional formatting. I have some data that I am collecting and entering into a spreadsheet for a couple different companies. Once I receive paperwork from those companies for the data collected I mark the data with a blue colored text. I would like to have a second sheet that generates what companies have not sent me their paperwork. "If the data is black text, then generate <name of company> on second sheet." The list is small right now, but as the project continues it will grow very large and it will become difficult to track. I will attach a copy of the spreadsheet for you to see the situation. I would like to learn how to do these types of formulas, is there a tutorial geared towards these types of situations?
View 2 Replies
View Related
Apr 7, 2009
Each row in my file describes a country and a year. I have data for some years; I would need a way to copy these observations to the following years until there is a new value (conditional to being same country). On the other hand, if there was no value for that country, I would need a ".".
View 4 Replies
View Related
May 18, 2009
I have an excel document that has one columb (I) of cells that changes color depending on a specific date (columb h) . The cells that have conditional formating to change the row color based on where it is ( =MOD(ROW(),2)=1), stays blank and the proper color until columb A is filled in. The others show up red. How would I code/format the lines that aren't included in the conditional formating to always show white when no value is entered in columb a.
View 9 Replies
View Related
Jun 30, 2009
conditional value return. See the attachment.
View 3 Replies
View Related
