Choosing Cell With Minimum Value
Feb 15, 2009
I am working with a spreadsheet that has quite a few products and each product has a column for each vendor of that product with the price associated with that vendor. So, I have five columns total:
product, vendor 1 price, vendor 2 price, vendor 3 price, vendor 4 price. I want to have a 6th column that will show the preffered vendor, which of course is selected by the lowest price. I have been able to accomplish this by chaining IF statements together, but it is getting VERY cumbersome for more than 2 vendors.
Ideally, I would like to tell Excel to look at the 4 price cells, choose the lowest one, and return a column number, which I can then associate with a vendor.
View 4 Replies
ADVERTISEMENT
Mar 25, 2007
I have not located anywhere an answer how to use in vba dropdown list, when you choose a cell it displays in the TextBox the cell right to it? How is it done by using VBA? Here's a picture about what I am trying to accomplish:
View 6 Replies
View Related
Oct 24, 2008
I need to choose the content of a cell by the calendar date ( or system date). Before the date, I need the cell blank (or a 0 would be acceptable). On or after the specific date, I need to have a number appear in that cell. For example, what I need is: Before 10/20/2008, cell B5 should be blank. After this date cell B5 should be 8.
View 9 Replies
View Related
Jan 4, 2010
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
View 3 Replies
View Related
Dec 24, 2009
I want in A1 to find minimum SUM if no minimum number in row.
Here is example attached: ...
View 9 Replies
View Related
Mar 20, 2014
If the result in cell i1 less than 1 to change it to 1
View 2 Replies
View Related
Feb 22, 2013
I am trying to write a macro where the cell value will be decreased by 1 each time the macro is run, down to a minimum value of 0
The code I have to do the reduction is below, and Im guessing its a simple one liner to check the value before applying the reduction:
Sub DecreaseValues()
'
' Decrease values by one Macro
'
'
Range("B29").Select
[Code] ........
View 1 Replies
View Related
Sep 25, 2009
I have a row that is a percent of sales with this:
=(F4*0.2+G4*0.1)*I4
"I4 will either be 1.0 or 0.5"
Now I want to have it so if this equation equals less than 100 with I4 being 1.0 to equal 100 OR if I4 is .5 then it should be 50. I have looked all around and I am no excel wizard so this is troubling me significantly.
View 4 Replies
View Related
Aug 4, 2012
I would like to generate an average of Row 1 to Current Row in a column. The Average column is the column that I would like to know how to generate.
Date
Value
Average
08/04/2012
5
5
08/04/2012
10
7.5
[code]....
I got close with =AVERAGE(OFFSET($D$1,COUNT($D$1:$D2),0,COUNT($D$1:$D2))), but after a couple rows of it being correct, the average starts to fall.
Using the above:
Value
Average
Should be
7.07
7.07
7.07
7.23
7.120
7.15
[code]....
View 2 Replies
View Related
May 24, 2014
I'm trying to analyze the pricing we get from our shipping courier. Although the courier gives us incentive discounts, they also have a minimum charge that supersedes the discount. In other words, suppose the "Minimum charge" is set at $6.24. If the published "list rate" on a package is $10 but we get a 50% discount on all shipments, instead of paying $5, we pay $6.24. Basically, between the post-discount amount and the "minimum charge" we pay whichever is greater/higher.
Now, to the Excel part: I have cells with simple formulas applying our % discount to our carrier's published list rates to calculate the resulting price (i.e, where cell A1 contains published list shipping rate and cell B1 is our discount %, so in cell C1, I have a formula: =A1*B1). I would like know how, if the result of the formula in cell C1 is less than $6.24, that the cell could display $6.24 rather than the actual result?
View 2 Replies
View Related
Dec 29, 2008
I have 4 cells and I want to highlight the lowest valued cell.
At the moment I have the conditional formatting formula of
=AND($C$5=MIN($C$5:$C$10),$C$5>0)
However if C5 = 2 and C9 = 0 then C5 doesnt get highlighted as 0 is lower than 2.
However I do want it to be highlighted. How can I ignore the zeros in the range im testing?
View 9 Replies
View Related
Feb 2, 2010
I have a column that indicates the earliest date that a project will start. There is a Project Minimum Date, Activity Dates, Sub Activity Dates and Sub Sub Activity Dates.
When a Sub Sub Activity Date is entered, it goes up to the Sub Activity Date and so on.
I have a formula =SMALL($E6:$E224,COUNTIF($E$6:$E$224,0)+1) that will select the earliest date out of the sub sub activities and sub activities but if one of the cells is blank in the sub activity or activity, then a #NUM! error is returned.
View 9 Replies
View Related
Nov 20, 2007
If I have a column full of data (call it 5000 data points) and use =min(a1:a5000), how do I find the cell this function is referencing without sifting through 5000 entries?
View 6 Replies
View Related
Apr 8, 2013
i have the sheet which have the table which look like
Person task done
A 5
B 4
C 1
D 1
now i want is there is any way to find minum value in task done colium and return the person name and like in this exapmle, 2 person have done have done minium work.. it tell me both those name
View 5 Replies
View Related
Jun 6, 2007
I want the user to be able to choose a printer but not print at that time.
1. Store the active printer as a variable using application.activeprinter
2. Have a dialog box (the printers available) popup and have them choose one, then store that
3. I know how to do the rest, but I will have the word document print out with the selected printer, then use the application.activeprinter reset to the original one
View 9 Replies
View Related
May 19, 2007
I have had a lot of luck finding what I need from the search areas, and I even found some information on the formula I am trying to build. The problem is I don't understand it and I need some help. First let me set it up for you. (I do not know the formula)
If cell L125 is has a value >0.00, I need to locate the smallest value the range of cells C125:F125, I then need to subtract L125 from that number, otherwise enter nothing.
This really has me baffled. I tried and I tried but it will not find the smallest value then subtract L125.
View 9 Replies
View Related
Feb 12, 2010
I have a monitoring system that records a data point with a date/time stamp several times a day at random intervals. For each reading I want to calculate the change compared to the first reading that was more than 24 hours ago, which could be anywhere from 1 to 20 rows above the current one. Hence with the timestamp in col A and the value in col B, the formula in col C, for example cell C20, needs to read something like =B20-Bxyz, where xyz is the row number of the first reading that is more than 24 hours, i.e the first row xyz where A20-Axyz >1.
View 4 Replies
View Related
Jun 18, 2014
I am trying to assign rooms to tenants. I would like to set up a function that reads a tenant's top 5 choices for a room, and looks up a column to see if the choice has already been selected before moving to the next choice. Then if all 5 choices have already been selected, the cell reads error.
This is how it would look : Excel Pic.jpg
View 3 Replies
View Related
Feb 9, 2007
Is there a function in Excel that would randomly select one of the 4 specified numbers?
View 13 Replies
View Related
Nov 24, 2011
I'm trying to set up a consolidated spreadsheet with a number of tabs and a master sheet to work on. I want to set up this master so that I can type the sheet name into one box and in the next box it will use the sheetname I've just typed in to then pull the array off that sheet. I'm using this with VLOOKUP but when I try and type A1! in in the array section, it doesn't seem to like it.
View 3 Replies
View Related
Jun 16, 2007
In my program I want some labels to be printed on printer P661 (black/white) and other labels on printer P656 (color).
Q: is it possible to do this in vba for my code?
The labels are made in Word and printed from within Excel.
Sub PrintNACL_LABEL()
Dim oWord As Object
Dim sPath As String
Dim iCnt As Integer
sPath = ThisWorkbook.Path & Application.PathSeparator & "NACL_LABEL.doc"
iCnt = Val(InputBox("Hoeveel exemplaren?", "NACL_LABEL", 1))
If iCnt >= 1 Then
Set oWord = CreateObject(Class:="Word.Application")
With oWord.Documents.Open(sPath)
.PrintOut Background:=False, Copies:=iCnt
.Close False
End With
oWord.Quit False
End If
Set oWord = Nothing
End Sub
The specific printers in the network are:
Color ---> "\PlantijnP656 op Ne00:"
Black/white ---> "\plantijn2000oldP661 op Ne02:"
View 9 Replies
View Related
Oct 7, 2006
I'm trying to write an Excel program to calculate golf handicaps. In column A, I have 20 unique dates. In column B, I have corresponding 20 scores. In column C, I'm trying to create a corresponding notational mark, "*", indicating the 10 lowest scores that are also the oldest. However, I'm running into problems as I may have duplicate scores with different dates, i.e. 3/22/06 will have a score of 85 and will be the 10th lowest score, but 4/1/06 also has a score of 85.
View 9 Replies
View Related
Oct 18, 2006
I have attached a sample of what I am trying to do. What I want to do is to be able to go through each day sheet and choose the name and times from the list box's I've created and have it show up on the summary page to match. So if I'm scheduled to work on Wednesday at 9am... I want it to show up on the summary page under Wednesday at 9am that I am working, but If I am not working I want it to be just an X to show up.
View 5 Replies
View Related
Jul 7, 2007
I would like to get some help on choosing the right files through vba codes. I have the dates in cell a3 and b3 and all my files ends with the date. The user inputs the date and the code should get the data within that date.
I would like to get some thoughts on how to get the value of cell a3 and b3 and open only those files from the folder.
View 8 Replies
View Related
Mar 18, 2009
I'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time.
Here is the ....
View 3 Replies
View Related
Dec 23, 2013
I'm looking for a way for excel to output data to a cell dependent on what I choose from a drop down menu.
For example. I have a drop down menu with 3 options. 1,2 and 3. What I want is that if I choose option 2, then in cell A2 (or whatever) would output the value of 2, say "2222". Or if I choose 1, then A2 would output the value of 1, say "12345".
View 6 Replies
View Related
Jan 24, 2012
How to open or Print a doc or pdf file from a combobox on a userForm
I have a folder full of doc files. I used a userform on which i put a combobox which contains those files' names and a cmd to open the file.
I managed to copy all the files names to the WorkSheet and listed them in the combobox.
@@@ I need a VBA code to do the following :
1- looking for the file name which matches the one i choose from the combobox
2- if there is a match, show " match" on a lable.
3- when i click the "open" cmd i created on the userform, it opens the file. or even print it and close it
4- prepare for another match.
View 1 Replies
View Related
Apr 3, 2009
If I have a very long password would this make unprotecting the sheet harder?
Or do the programs available on the net extract the data from else where - Making it pointless choosing a complicated password?
View 9 Replies
View Related
Aug 14, 2008
I'm trying to create an invoice with customer list, and the problem now is I'm trying to find a list-dependent data way to input my customer's address.
At the moment, all I can do is a dependent dropdowns from a sorted list (see method here). So it's kinda troublesome in such that my list (of customers) opens up a list for address that has only one option (since there's only one address for each customer).
I hope there's a method out there whereby I can have a list of customers, then select the customer I want, and the address of this particular customer will appear in another cell below automatically (without the need of selecting it in another list).
My address is actually split into three different cells - Address, Building & Postal Code.
View 3 Replies
View Related
Jun 13, 2014
I'm currently trying to put together a search function of sort onto an excel document to look for entries of "Y" based on the choices in a drop down list to populate a list of names that have a "Y" next to them. I've searched the net and read up on IF, VLOOKUP and HLOOKUP commands but I can't quite string something together. An example of what I am trying to achieve is below;
Raw Data:
type1type2type3type4room1room2room3room4
AndyYNNNnYNY
BillYYNYYYYY
ChrisnYNYYYYY
DaveYNNNYYYY
EddyYNNNYYYY
FrankYYNYYNNN
GilesYYNYnYNY
HarveynYNNYNYN
IannYNYYYYY
JohnYYNYYYYN
KyleYYNYYYYY
Search drop downs:
Typetype1
Roomroom1
End result (based on the example of Type1 and Room1):
Bill
Dave
Eddy
Frank
John
Kyle
View 7 Replies
View Related
