I need cells to be able to only allow multiples of fifty and a message to pop up of invalid entry for everything else.
View 9 RepliesI'm looking to put a formula in the last column (Card Valid/Invalid (N3:N8)).
I want this formula to say IF Project Name OR Start date OR Due Date or Type OR Author OR VS are empty then I want the last column to say Invalid else if all of these columns are filled in then give valid. Also if a completed date or no. of review loops are filled in but not both then give Invalid but if both are filled in then as well as all the rest of the info give Valid.
Project Name - Valid/Invalid is B2-N2
I'm trying to attach an Excel file but I keep getting a message stating it's an invalid file. I don't remember having trouble before attaching here, but I am now. What must I do?
View 9 Replies View RelatedI have a Configuator sheet and the way it works is I have 6 different descriptions (plug connector, socket connector, length, jacket type, color, gauge). So when you choose a Plug Connector only the socket connectors that go with that plug are available to choose using data validation. Then depending on on the plug and socket connector it gives you the lengths that are available and so on.
Then I have a button with a macro that matches the descriptions you have chosen and returns the following: part number, Description, case qty, Available Stock, and Price. My issue right now is Not all plug connectors share the same Socket connectors so when someone changes the Plug Connector and has a invalid Socket connector from a previous selection the value returns as #N/A.
So what I would like to do is when I run my "Find Cable" Macro it will match each selection and if one is invalid returns a message box telling you which field is invalid i.e. socket, length, jacket type, color, gauge.
VBA code. I think I need a worsksheet change event. Here is what I'm looking for:
If cell K1 = 3 and if the value of any cell in column A is < 80000 or is > 86666, or if the five digit number begins with an alpha, then a msg box will pop up stating "Invalid range".
More background:
The user will be manually inputting 5 digit numbers into column A, but if they've selected cell K1 to equal 3 from a dropdown menu (which the user also does), then values entered that are less than 80000 or greater than 86666 are 'out of range', and the user should be alerted and if possible the number they entered gets cleared.
Additionally, some of the numbers the user could type in might begin with an alpha prefix, such as "G1234" - these would also be considered out of range when K1=3, so if the conditional statement could also recognize this it would be useful.
Column A will be blank until the user types in the 5 digit number, so the code only needs to trigger if the number typed into a cell in column A is outside of the aforementioned range.
So to sum it up once again, I'm looking for a msgbox (and clear cell) if the following criteria is simultaneously met-
*If K1 = 3 (meaning that K1 equaling anything other than 3 wouldn't cause the msgbox)
*And values typed in column A are less than 80000 or are greater than 86666, or begin with an alpha (meaning that values typed in between 80000 - 86666 wouldn't cause the msg box.)
I am trying to develop a system to allow me select different parts from multiple sheets and then add them into a bill of material on a separate sheet.
[URL] ...........
I have written some VB script the best I can and it includes a 'commandbutton' to make it easy for the user of the workbook to use.
This is how it works:
Go to 'Step 1 - Manifold 8640' tab and highlight one of the yellow cells.Once you have selected a yellow cell, you then click the 'select' button above.The value (along with other values) are copied to the 'Smart Calc' sheet.Then you go to 'Step 2 - Gland Plate' tab, select a yellow cell, click the 'Select' button.Notice how the 'Step 2 - Gland Plate' value is entered into a different group in the 'Smart Calc' sheet - this is great................BUT
The issue I am having is:
If the user goes to 'Step 1 - Manifold 8640' tab, highlights the desired yellow cell then presses the 'Select' button twice, it will add two lines of data in the relevant 'Step 1 - Manifold 8640' section....This is ok.But when the user clicks a third time, the 'Step 1' data will overflow into the 'Step 2' section of the 'Smart Calc' tab...because the script tells the data to look for the next available line to write to. This is what I am trying to avoid!
Instead, i would like a message box to popup to indicate to the user that they cannot add anymore data into into the 'Step 1' or 'Step 2' sections of the 'Smart Calc' tab until they clear the data from within the relevant section in 'Smat Calc'.
I would like to set limits on where each 'Step 1' or 'Step 2' data is written to the 'Smart Calc' sheet (so it is within its relevant section). As you can see the script basically looks for the next available cell but this isn't really good when the data 'overflows'.
Note that you are able to highlight the yellow cells in the 'Smart Calc' tab and press the delete key, it will delete the data to clear the line. That is how i designed it to work so if the user makes a mistake they are able to just delete the relevant line and enter new data.
I recorded a function and got the message box "Invalid Outside Procedure" when I tried to run the macro. I don't understand what it means or how to prevent it.
View 2 Replies View RelatedI am writing a roster spreadsheet and it has cells with drop down lists using validation lists to fill in the start and finish times, as we run a nearly 24 hour business it is possible to accidently start someone at 2.00am and finish them at 10.00pm. In the cell which calculates the time rostered I want it to display a messagebox if the hours exceed 10 hours or go into negative.
I need to "wife proof" this sheet as this stuffs up the total calculated hours and total costs.
Would I do this through validation on the cell with the formula calculating the times orr though a macro on the sheet module?
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
I have attached an example. on the "master" sheet in column L I need a vlookup which will look up the information in column I & J and return the appropriate amount for that from column C of the "sheet3" tab. If there is nothing it should just return 0.
Sample Macro jscalem.xlsx
Is it possible to have a forumla that gives a True or False (perferably if it's possible for a conditional formatting to change colors) for multiples of a number. I would input a number in the cell and it would change the cell color if it not a multiple of say for example 13.
View 5 Replies View RelatedI need to be able to round a given number (stock required) up to the next multiple of another number (pack size), as in the attached example.
Also, is there a way I can round to the closest multiple, ie either up or down, depending on which multiple is closer? I haven't decided which way I want to go yet!
How do I round percentages, both up and down, to the nearest 5%?
Ex. 82% to 80% as well as 84% to 85%
I need the output in B1 to be in multiples of the value in A1, rounded up.
example:
Value in A1 is 6
random formula in B1: =IF(AND(N2>$AA$1,S2>0),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2>$AA$1,S2=""),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2<$AD$1,S2=""),ROUND((30/$AD$1)*AD2-Z2,0),IF(AND(N2<$AC$1,S2=""),ROUND((30/$AC$1)*AC2-Z2,0),IF(AND(N2<$AB$1,S2=""),ROUND((30/$AB$1)*AB2-Z2,0),IF(AND(N2=M2,N2<$AA$1,S2>0),ROUND(30*(AA2/N2)-Z2,0),IF(AND(N2<>M2,N2<$AA$1,S2>0),ROUND(AA2*(30/$AA$1)-Z2,0))))))))
So if the resulting value of my B1 formula is 2.33, I'd like to have the output be 6. If the resulting value is 14.55, I'd like the output to be 18.
Is ther a way to search a column from M9 down and delete all numbers that are not multiples of 25. Also delete shiftup column N with it? i.e.
From:
1200.......399
1225.......700
1250.......325
1260.......320
1274.......500
1275.......251
1300.......102
To:
1200.......399
1225.......700
1250.......325
1275.......251
1300.......102
I'm trying to make a sales chart defining the top 6 most popular items I'm having a problem.
Attachment 316136
I need to create a vlookup to find a cell and returns all cells that are underneath, in the example:
jicRD.jpg
I need to find the color and return all cells that are down
I've got a head-scratcher here that no one in the office can figure out (at my level anyway). I'm trying to count multiples of the same value within the same cell. For instance, if one cell's value is:
2, 2, 4, 5
I need my '2' count column to return two '2's. I'm using a wildcard because the cells will contain the numbers 1-7, some multiple times, and usually in different orders. The wildcard formula I'm using only picks up one if multiples of the same value are entered in one cell. Here's the formula:
=COUNTIF($C$4:$AG$60, "*"&"2"&"*")+COUNTIF($C$4:$AG$60, "2")
This formula only returns a value of one in the '2' count column. It can't find the second '2' for some reason. I haven't been able to make COUNTA work either.
i have created a file for tracking my employee trandiness however, when i want to count the M, P, X ,L & AP keys which i already created it gives me OK for one month but when i tryied to calculate more than sheet "month" gives me error !! i used the below farmula :
=COUNTIFS(tblJanuary[@[1]:[31]],KeyPersonal)
how to calculated the above fx in every month. !!
I know Excel quite well but this problem has me beaten and I have searched high and low to know avail.
I have an input column of departments and names which is ever-changing and currently I have hard-coded them into a spreadsheet to run a couple of drop down columns. However, I really want this to be automated and no longer hard-coded so that names are in the correct departments automatically.
to create the following formula which I'm now repeating in more elaborate forms. At the moment I am using the formula below but I need to get the bit in red in the second half of the formula to search on more than the one condition.
Currently it is searching a postcode abbreviation field and is picking up all those with 'BS' however I don't just want to search on BS. I would like to search on BS, BA, SN, TA, GL. I have tried putting different combination using commas and plus signs etc in but excel doens't seem to like the way I'm doing it.
=SUMPRODUCT(--(Sheet1!$T$3:$T$10000=A49),--(Sheet1!$S$3:$S$10000="BS"))
My question is presented, in details, in the attached file. Only Range 1 (B6:B15) should be generated by "Excel". (the numbers in Range 1 were typed by me as trials only...) (All other Ranges should not be touched). The values in Range 1 can be Multiples of 10th. only. (10, 20, 30, 40, etc....)
View 3 Replies View RelatedI am setting up a time and attendance system.
What I want to do is calculate the overtime that someone has worked but in multiples of 15 minutes.
Example, if someone worked 20 minutes over they would be paid for 15 minutes overtime.
If someone worked 31 minutes over they would be paid 30 minutes overtime.
The possible overtime someone could work in one day is 6 hours.
I want it to return the overtime in decimal numbers (e.g 0.25 for 15 minutes overtime).
I have attached a sample spreadsheet.
I would prefer this to be done in VBA if possible?
I would like to be able to add a standard set of new worksheets in varying multiples to my workbook from time to time.
How would I:
1. COPY the entire contents of a worksheet I've named 'Last' (content may be added to from time to time - so 'copy all' rather than a set range, so that:
2. Each worksheet created after 'Last' is named in turn 'Vendor1', 'Vendor2' etc
Sometimes I will need to create, say 10 further w/s 'Vendor1' to 'Vendor10' other times maybe 50 w/s. or more.
After these new w/s are created they will be manually renamed to the name of the actual vendor - so this naming convention of Vendor1 etc, will not cause problems with existing named worksheets.
However, the position in the sequence of 'Last' will obviously change as new w/s are added so the VBA would need to take this into account.
How do I create a code that record on row 1012, starting with column N, will count the number of rows between each "0" record in the range F7:F106. Perform the same function for number "0" in the range G7:G106 and recording in N1013.
Perform the same function for number "0" in the range H7:H106 and recording in N1014. Perform the same function for number "0" in the range F7:H106 and recording in N1015.
I am trying to create a spreadsheet containing a table of dimensions that are multiples of up to six loadable variables. I work for a stainless steel tank manufacturer. The body of tanks are formed from coil stock of varying widths. Typically these might be 1200mm, 1500mm, 2000mm and possibly 1800mm as well as the imperial equivalents. This could mean up to eight variables but in reality all are unlikely at any one given time.
We are told the volume that the tank is required to contain and a target preferred diameter. The theoretical height is therefore derived from this information. But now comes the tricky bit. It is desirable, to minimise cost of welding by minimising the number of strakes or wrappers. Can anyone give me a formula or whatever that can be used to do this and hopefully generate a table of close heights up to say 40 meters. Thus allowing an operator to select from say five presented options.
I have a table in a spreadsheet that the three principal widths of 1200mm 1500mm and 2000mm generate. It was created for me a while ago by a gentleman working at the Amsterdam Museum that I’m happy to send for perusal. The trouble is he never told me how he did it and no matter how hard I try, I cannot discover the method or formula. The first column gives the total combined height, the second the number of 1200mm wrappers, the third the number of 1500mm and the last the number of 2000mm. It is that simple
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
I need a basic formula that will take my start time from my end time and round it up to the next hour with a two hour minimum. I formated my cell to not have decimals and I tried to use round and roundup along with and if/then formula but no luck. If it makes any difference, all times are in 24hr format.
View 6 Replies View RelatedIs it possible to use design mode to create a button for multiples macros?
What i want to do is create like a list, and every selection run a different macro. I have a report that i want to use macro to make different filter selections, it would be about 20 different filters combination, so 20 buttons would be a mess... Is that possible or can i do it in another way?
And is possible to link the filter value to a different cell?
I would like to add a piece of code into the user form that will check and verify if a part has already been added so as to avoid multiples in a user-driven/created database.
here is a repost of the current code i am using for the user form (I have posted it before in another thread .. Blane245 helped me out with a different question I had)