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.
I want to create an input box that will allow you to input the column number it should start in... so instead of it being fixed at A, you can choose column B, C, D all the way to the end if you like.
Sub SplitSheetsNoDelete1()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Lmainsheet As String
Dim Lrow As Integer
Dim lcontinue As Boolean
Dim LColamaster As String
Dim lcolatest As String
'Retrieve name of sheet that contains the data
Lmainsheet = ActiveSheet.Name
'Initialize variables
lcontinue = True
Lrow = 2.....................................
I have a variable, ColumnX. I want to use VBA to generate some formula referencing ColumnX. For example: I want to sum the values in A1 to A10. ColumnX has been set to "1" which is the number of column A.
Range("A11")= "=sum("A1:" & ColumnX & "10)"
Obviously the above code doesn't work because I'm putting a number where VBA expects a letter. How can I rewrite my code so that A11 will entered with formula that sums A1 to A10?
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 have a column of data (A1:A38) that I need to convert to a row (A1:AL1) and I can't figure out how to do it. Forgive me if the answer is in the forum but I don't know excel well enough to use the proper search terms. I tried searching on pivot which only yielded pivot tables which I do not *think* apply to this.
View 2 Replies View RelatedWE have many spreadsheets with 1000's of part numbers that need to be converted--- currently the spreadsheets have data in B1 and B2 (part number and description), B3 and B4, and so on--- we need to be able to take B1 and send it to A2, so the part number and the description then end up on the same row--can anybody help this Excel novice convert this data??
View 14 Replies View RelatedI have a excel spreadsheet that is setup as follow:Johnson, Ashley0916BF00001U9/16/2009Test1POSITIVE0916BF00002R9/16/2009Test1POSITIVE0916BF00001U9/16/2009Test2NP0916BF00002R9/16/2009Test2NP0916BF00001U9/16/2009Test3NP0916BF00002R9/16/2009Test3NPBlack, Tom0517BF00012R5/17/2009Test1POSITIVE0527BF00003R5/27/2009Test1NEGATIVE0601BF00009R6/1/2009Test1NEGATIVE0517BF00012R5/17/2009Test2NP0527BF00003R5/27/2009Test2NEGATIVE0601BF00009R6/1/2009Test2NP0517BF00012R5/17/2009Test3NP0527BF00003R5/27/2009Test3NP0601BF00009R6/1/2009Test3NP
First column is in goups: First row of each group contains customer's name then followed by test codes. Test codes are usually repeated three times per customer but they have different names (listed in third column as Test1, Test2 and Test3) and different results (listed in 4th column as POSITIVE, NEGATIVE or NP).
What I want to do is to convert this data into a way that each row has One customer name and the columns in front of each customer lists Tests results and dates. If a customer has multiple test codes, then one row to be created for each test code.
Customer NameTest 1 DateTest 1 ResultTest2 DateTest2 ResultTest3 DateTest3 ResultJohnson, Ashley9/16/2009POSITIVE9/16/2009NP9/16/2009NPJohnson, Ashley9/16/2009POSITIVE9/16/2009NP9/16/2009NPBlack, Tom5/17/2009POSITIVE5/17/2009NP5/17/2009NPBlack, Tom5/27/2009NEGATIVE5/27/2009NEGATIVE5/27/2009NPBlack, Tom6/1/2009NEGATIVE6/1/2009NP6/1/2009NP
I have 02 separate excel workbooks
01 = source file
02 = data list
The workbook "source file" contains all the data
The workbook "data list" contains data for work with Drop-down list. And i can easily select my required description by just dropping down the list.
In case, i have new description i add it in the source file and it is updated in the working "data list" sheet.
This applies to Field device column also. But i want to improve the selection criteria by creating such script or formula which could check the input of the description and automatically selects the field device and its relevant signal (output type).
Screen shot is attached : Automatic Selection.jpg‎
I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1.
A B C D E
1 X X X X
2 X X X X
3 X X X X X
4 X X X X
5 X X X X
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. !!
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 need a macro that allows me to select a matrix of cells and converts it into a single column elsewhere, pasting the second column below the first and so on. Is it possible to select the matrix, hit a macro key and then paste it in my selected location?
View 11 Replies View RelatedI have a set of training information in the rows, with dates under it which maps back to names of employee in the left hand side. How do I convert that to Name of Employee Training attended dates expiry date.
View 8 Replies View Relatedhow to create some code that can convert any column number to it's respective letter, from 1 = "A" to 16384 = "XFD". The code is posted below. Enjoy, or let me know how you would improve it.
Code:
Function colNumToChar(colNum) As String
Dim Num1, Num2, Num3 As Integer
Dim Let1, Let2, Let3 As String
[Code]....
I have a file with a lot of numbers, but one column with single digits. These digits have a cooresponding model name. I want to convert them all to their model name. There are only 4 model names. I tried a nested IF statement, but felt this wasn't working properly or was not the proper formula choice.
A Find/Replace would work, but it picks up the single digits elsewhere in the sheet. I realize i could copy/paste my column to a new sheet and from there convert/paste, but I am trying to get better at Excel.
This macro is extreemly basic but i have no skills in excel with macros. Basically i have 'Column A' with stats that i need input into 'Column B', however i need then to be input inside a field that is already there.....
View 4 Replies View RelatedI 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 some assistance with converting a date based upon a time in column A. I have a number of dates in column A as follows:
01/13/2009 11:50 AM
01/05/2009 09:14 AM
01/12/2009 05:30 PM
01/12/2009 02:30 PM
01/12/2009 01:45 PM
I need to convert these values to another date base upon the values in column A.
If the time < 2:00 pm then leave date as is, if time > than 2:00 pm then add one day. The expected results in column B would be as follows
01/13/2009
01/05/2009
01/13/2009
01/13/2009
01/12/2009