Create Function To Choose And Display Optimal Configuration Of 5 Box CBM
Feb 27, 2014
I am creating a box CBM optimizing tool at work for shipping our products internationally so that the sales department and logistics can estimate shipping costs and weights with out needing me to physically pack everything in advance. I have broken every product we sell into individual CBM's as well as the boxes that we use to ship. I have also set it up to give a break down of "boxes needed" to accommodate the approximated CBM by box size.
How to create a function that will take that info and calculate which combination of boxes will utilize the available space... In other words, how do i take my break down of the 6 box sizes available, with the "# of boxes needed" data i already have, and have it tell me which combination of boxes will utilize the maximum percentage of space, or leave the least amount of unused space based off of the cumulative CBM being shipped.
Snippet attached:
Capture.PNG
View 1 Replies
ADVERTISEMENT
May 24, 2009
I have code (below) that works if the range "ImportFile2" contains a valid value but I want users to be able to select any .csv file preferably from a directory which should be held in a cell.
I added ...
View 9 Replies
View Related
Jun 27, 2007
I have a number of different formulae I want to invoke according to a choice. I am able to request the formula I want by entering them ‘in line’ as value1, value2 etc into the CHOOSE formula … e.g. =CHOOSE($H$1,IF(N3>O3,1,""),IF(N3<O3,1,""),IF(N3=O 3,1,””))
but this is cumbersome and confusing since I have a growing number of choices and some of the formulae are quite long. I want to enter them in a list and extract the formula which corresponds with the Index_num in the CHOOSE formula. I also need the cells in the formulae to be relative so this rules out using NAMES to refer to the formulae.
View 4 Replies
View Related
Jul 16, 2008
Title should read "Data Validation Lists"...oops
I'm trying to format a spreadsheet so that based on the value in Column A, Coulmn B displays one of two spearate dropdown menus.
I know the following formula will not work, but that is the effect I want in Column B.
View 7 Replies
View Related
May 28, 2014
I have this issue where I get an #N/A error when I i run a VLookup in cell H7. I have posted the worbook.
I want to find the value in column D for the value you choose in cell G2
Excelforum.xlsx
View 4 Replies
View Related
Dec 16, 2008
I have a workbook with the twelve months of the year as seperat worksheets. a thirteenth worksheet is a "total" page. I have three different names to choose from a drop down list in collumn E. I need a formula that will say something like, "if JanuaryE4 = John Doe, total page A1= January A1" Is this possible?
View 5 Replies
View Related
Dec 17, 2009
I currently use a Core2 Duo PC and often max out the both CPU for extended periods when recalculating large (30MB+) spreadsheets.
Wondering if anyone has experience with Quad Core desktop PC's and large files and if there is a noticeable / significant performance improvement with more cores..
Ram doesn't seem to be an issue.
View 10 Replies
View Related
Dec 16, 2008
Example:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
View 5 Replies
View Related
Jan 31, 2014
We work in a Work book with Various sheet.. in this workbook A master sheet (masterdatasheet) & many other datasheet (Each sheet with diff data)
We want a VBA for my master sheet with a Combo box or list type data validation.. where we choose Sheets Name ......... after choosing datasheet... those sheet data Dispaly in My Master data sheet....
with MasterdataSheet we are caclucating some calculation which is which is sown in bottom line in this sheet
My Sample file is attached with 2 datasheet.. (in my original sheet more then 50 sheets so we enclosed only 2 sheet for trail baisis)
(My Hiden sheets protection password is san19241 or san if u want to open those files).
View 10 Replies
View Related
Jun 11, 2014
can buy stock glass in several standard sizes. Specifically:
12x16
16x20
20x24
22x28
24x30
24x36
26x32
32x40
36x48
40x60
I use these stock sizes - to cut custom sized pieces. Example - if I have a piece that calls for a 15x18 - I will take a 16x20 piece and cut it down. I have all different kinds of size combinations that come up and am looking for a way for Excel to calculate the most efficient stock size to start with. Trying to do so using a series of Vlookups and if then's - but seems to be overly complicated and not always accurate.
View 5 Replies
View Related
Nov 13, 2009
I've been assigned a task of finding a combination of three or four machines. For example AD,AJ,AQ, and AB would equal 7 therefore it would be the best combination of workstations for that cell. However, I'm having an issue that if AD, AJ, AQ, and AB are being selected more than once.
My question is, how can I analyze all the data and determine the best combination given the relationships for each row given the column.
3 = Absolutely Necessary
2 = Extremely Necessary
1 = Necessary
0 = Do not associate
View 14 Replies
View Related
Jan 21, 2008
What I want to do: find the four cell that have the same value "99" in belows configurations
A B C D
1 99
2 99
3 99
4 99
A B C D
1 99
2 99
3 99
4 99
View 9 Replies
View Related
Aug 15, 2013
Text configuration - how can I tell a cell to pick up a phrase from one cell, but if that phrase is equal to one particular phrase, then produce a blank.
View 3 Replies
View Related
Jan 18, 2009
I have a workbook with several worksheets in it. 4 of them have macros written in them, some do not. I protected each sheet with a password and made it so that users couldn't select any "locked" cells, and did this on ALL the worksheets. I noticed now that, just upon opening the workbook (not running any macros yet), on the sheets with macros I'm able to select locked cells (which is wrong), but the sheets without macros still function correctly. I think that this may have to do with Excel's default protecting arrangement where you can select either locked or unlocked cells. I specifically locked all worksheets with the correct arrangement but every time I open the workbook some of the worksheets default back to Excel's default arrangement. What can I do about this problem? I need this configuration to follow the workbook and not the specific computer it's on. Many users are going to use this, and can't be able to select locked cells. My macros do unprotect and re-protect the sheets to run, and I'd wonder if I can edit these to protect the sheets in a different configuration (which I don't think I can do with regards to being able to select locked cells) but these macros don't even run before this happens, and the worksheet open event doesn't involve any unlocking or re-locking (it just disables the cut and drag-and-drop functions.
View 8 Replies
View Related
Aug 11, 2008
I need to optimize an Excel model with 5 variables each of which cam be any value from 1 to 20. The solution space, therefore, contains 3,200,000 combinations. I have a software(Palisade Risk) which can give out a random sampling of this solution space. I need to know how many combinations can be considered as a representative sample of this solution space - e.g. can it be 1%?.
View 5 Replies
View Related
Aug 30, 2013
Is it possible to calculate the current optimal fantasy football team from the attached data and constraints listed on the 'solver' tab?
View 2 Replies
View Related
Jan 11, 2010
I would like to create a formula that allows the destination cell display a number depending on a word that is typed in the source cell. The source cell and the destinating cell will remain the same two cells.
example:A2=IF(A1="WORD1",1.5,0)
If i type WORD1 in A1 (source cell) then A2 (destination cell) will display 1.5, but how do i make this formula work for a multiple of different words.
example:A2=IF(A1="WORD1",1.5,0 IF(A1="WORD2",3.0,0 IF(A1="WORD3",2.5,0)))
this formula doesn't work, but is basically what i am looking for the formula to do - WORD1 displays 1.5, WORD2 displays 3.0, WORD3 displays 2.5 etc where the source cell and the destinating cell remain the same two cells, it is just the word and its value that change.
View 4 Replies
View Related
Jun 26, 2013
I have table in excel sheet and i want Create an Excel User Form display automatically to this table, how?
View 3 Replies
View Related
Feb 16, 2007
I have this formula =TEXT(inventory!I2,"mmm-yyyy") which takes the date from another spreadsheet, but anytime that the date is missing, it susbstitutes for Jan-1900. Is there any way to revise the formula to leave the missing dates blank in the cell? For example, if the date is missing on the other worksheet, in this case, the inventory worksheet, is there any way for a blank to appear, instead of the Jan-1900? Also, anyway for this formula to spell out the month, and not just the abbreviation?
View 2 Replies
View Related
Feb 3, 2010
I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet.
Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this:
No. Timestamp Tags Value
1 1-Jan-2010 Tag A 18
2 1-Jan-2010 Tag C 20
3 2-Jan-2010 Tag A 20
4 3-Jan-2010 Tag B 17
5 3-Jan-2010 Tag C 19
6 4-Jan-2010 Tag B 18
7 4-Jan-2010 Tag A 20
8 5-Jan-2010 Tag A 22
9 5-Jan-2010 Tag B 18
10 5-JAn-2010 Tag C 20
View 9 Replies
View Related
Mar 19, 2013
how I can build a search engine. I have attached a file with an idea about how I would like it. What I would like to have is a sheet with a search button. If the correct number is putted in this file the corresponding data should appear in the same sheet. I do not know how to make this at all!!
This is just a small file but if i know how to build it, it would concern a much bigger file and maybe even some graphs (if that is possible)
View 8 Replies
View Related
Sep 5, 2007
Im trying to find a formula that when a cell is empty ie this cell is linked to another cell on another worksheet and info has not been entered or is 0 then i want the cell to display the the current date ie =NOW()
View 10 Replies
View Related
Nov 8, 2012
Sheet1
Upon clicking any cell in Sheet1, it will automatically filter based on cell A and B.
Sheet2
Automaticall display filtered criteria based on double click from Sheet1
For example: when I double click on C1, on Sheet2 will automatically diplay filtered data based on A1 and B1 and same thing goes to if I double clik on C2 on Sheet2 will automatically diplay filtered data based on A1 and B2.
View 4 Replies
View Related
Dec 23, 2013
I have an excel file with a large amount of employee data in it and want to create a search facility that will run on variable search functions and display the information on the screen
I want to be able to enter variable search functions as follows:
Employee Number; shows all information on employee
Division: shows all employees in division (possible from a list of all divisions)
Appraisal Eligibility : Applicable shows all the applicable employees
Job Role: shows all the employees with the same job role (possibly from a drop-list of all roles)
I know its easier in Access, but all records in Excel as a legacy and don't have time to create an access database currently.
View 1 Replies
View Related
Apr 30, 2009
I am trying to write a function that will display the first Monday of the year based on the user entry.
For example, if I enter;
5/1/2011, It should produce 1/3/2011...
12/31/2012, it should produce 1/2/2012..
View 4 Replies
View Related
May 29, 2013
I'm trying to create a dynamic msgbox that will display what data has been updated based on checkbox selections in the userform. I've named my checkboxes as Carey, Keith, and Juliet.
Ideally if only Carey's data has been updated, I'd like the msgbox to say
' Data has been Updated for:
- Carey '
If Carey and Keith's data has been updated, I'd like the msgbox to say
'Data has been Updated for:
- Carey
- Keith '
etc.
MsgBox ("Data has been Updated for:" & vbnewline & _
If CAREY.Value =true then "- Carey" End if & vbnewline & _
If KEITH.Value =true then "- KEITH" End if & vbnewline & _
If JULIET.Value =true then "- Juliet" End if & ")
View 3 Replies
View Related
May 29, 2014
I'm currently using this formula:
=INDEX(B4:B64,MATCH(I5,A4:A64,0))
If I5's value is displayed more than 1 time in Column A, I want the information in Column B to appear in the same cell the formula is in in Column J
View 3 Replies
View Related
Jan 1, 2012
If I do =sin(radians(30)) I get .5 and =sin(radians(45)) I get .707106
These are of course correct, but I would like to get 1/2 and sq root 2/2 (with the actual radical sign).
View 9 Replies
View Related
Jan 29, 2014
I am using a VBA macro (Using mouse clicks as inputs for neuroscience experiment?).
What the macro does is it inputs a timestamp every time a keyboard combination is used (alt+right arrow in this case).
The part of the macro which inputs the timestamp is like this:
Sub AltRight_Sub()
On Error Resume Next
Cancel = True
Cells(Rows.Count, 2).End(xlUp).Offset(1) = Format(Now, "HH:MM:SS")
End Sub
I would like the timestamp to be displayed as HH:MM:SS.00, i.e. show the milliseconds. I tried playing around with the timestamp format (changing it to = Format(Now, "HH:MM:SS.00" or = Format(Now, "HH:MM:SS.0;@")) or = Format(Now, "MM:SS.000") and I tried using = Evaluate("=NOW()") instead of = Format(Now, "HH:MM:SS".
View 6 Replies
View Related
Aug 1, 2008
I am running excel 2003 and I am looking for a function that will display the filename or part of the file name in a cell. I know you can do this as part of a header or footer but is there anyway to have an individual cell display the file name of the document?
View 9 Replies
View Related
