somehow i managed to do this at first and then forgot to save my personal work book and cant remember how to do it.
What it is, is if cell A1 has a certain phrase in it ie Customer Reference then i want to insert four columns before it so it ends up in cell E1.
The other thing which i never managed to do is. I have 5 workbooks. master, book 1-4. I want to past all info in columns A:T from Book 1-4 into Master in the next available row.
I have two variables - the number of stages, and the number of people.
The stages will start in say C22 through G22 (if there were 5 stages). The number of stages will vary upon a user-inputted number in A1.
The names of people will start inB29, so I'd like to insert the simple formula (=Max(C23:C28)) across cells C29-G29 (again with my example of 5 stages), with relative references to the columns, of course.
Ditto for the next name in B30, etc, so that I get the max value for Person#1 in Stage 1, 2, 3...Person#2 in Stage 1,2,3...
Inserting the formula over the dynamic ranges.
I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.
Sub split_For_Database()
Dim No_Of_Cells As Integer
Dim Start_Cell As Range
Dim Cell As Range
Dim LastRow As Range
Dim i As Integer
Dim lRows As Long
Set LastRow = Range("C65536").End(xlUp)
i = 0
Set Start_Cell = Range("C2")
Set Cell = Start_Cell
Do While Start_Cell.Address <= LastRow.Address
Do While Cell.Offset(0, 1) > 0
i = i + 1
Set Cell = Cell.Offset(0, 1)
Loop...................................
Original
01012014 DAVE JOHN
02012014 MAT
03012014 CHRIS MIKE PHIL
What I want
01012014 DAVE
01012014 JOHN
02012014 MAT
03012014 CHRIS
03012014 MIKE
03012014 PHIL
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
I have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times.
View 3 Replies View RelatedI have a time sheet in which i need user to enter a time sheet number in cell c14
I need a formula for that cell so the user can input the time sheet number but only be able to do this if the time sheet number they are imputing is the next number in sequence of the last one used.
I need this for a tracking sheet of scores. For example, 1 gets 100 points, 2 gets 90 points, 3 gets 80 points, etc. I need to set it up for 10 places. I have no idea and have fiddled with it for two hours now. I need to be able to put a 1 in the cell and 100 appears after I hit enter, etc.
View 5 Replies View RelatedI am unable to insert columns on any worksheets. Even a blank worksheet. The insert column function remains disabled. Work sheets are not protected. What can I do to activate this function?
View 10 Replies View RelatedI want to compare Columns A and B and Insert value in C.
So if Column A is null, then place value of column B in C, otherwise place Value of A in C.
Worksheet full at Column IV how can I insert more
View 1 Replies View RelatedI have this excel file which has data in it. However, this data will come in everyday. Eg, A1 to A10 is QWE, A11 to A20 is RTY, A21 to 30 is UIO. But as I said earlier new data will come in everyday. For eg, it will become A1 to A15 is QWE, A16 to A30 is RTY and so and so forth.
I need to insert 2 rows after QWE, RTY, UIO. But as data will come in everyday, I cant standardise my columns to insert the 2 rows.
I am looking for a VBA code (or alternatively a way of doing this in normal Excel) that can let me insert a column every X columns (for example every six columns).
Ideally I would want a code that can let me insert a specific column (the same) every X columns, but a formula that inserts a blank column every X columns will hopefully do.
I have many excel sheets with many columns. I want to insert new columns but after every 2 columns, starting from column M.
View 9 Replies View RelatedIn my spreadsheets, I want to insert a formula in the existing columns of data. This is to be done after every 2 columns. There are 25 rows as well from 5 to 30.
View 9 Replies View RelatedMy question is if there is any way I can make the "X" number typed in a cell be the X number of columns inserted after/before a cell, B12 (for example).
View 2 Replies View RelatedI have three colums on a spreadsheet the have a formula that calculates by taking the amount dividing it by 365 days then multiplying that by say 35 days. The day portion of the formula is the same in all cell in the three columns the number is the one this that changes by cell.
Is it possible to have a pop up box allowing the user to enter a number that will then be inserted into the formula. Instead of hitting F2 and manually entering the number.
I have attached an example of what I need the macro for. I really need it to look at the columns with the names (Girth Weld, Weld) and the Joint length column to make its moves. Mainly the joint length. I think that would get me on track and give me some room to explore and learn what else I need to do. I love to try and figure things out but this is killing me!
[URL]
VB:
Sub LineUp() 'assumes data in order and concates unique
Dim i1 As Long, i2 As Long, n1 As Long, n2 As Long
Const StartRow As Long = 5
[Code].....
What is the easiest way to stop users inserting new columns or rows into a workbook? Is it a case of protecting each sheet individually?
View 6 Replies View RelatedI am unable to insert sheet rows or columns in a microsoft office spread sheet in a specific file. Other files are Ok.
View 14 Replies View RelatedI have a macro that inserts a blank column for every sheet in the workbook. The problem i have is that i only want a limited number of columns inserted. I only want a column to be inserted if the sheet tab has the name "detail" in it. I have tried for hours to get this to work, but i keep getting errors. Here is the code i am using.
View 4 Replies View RelatedI would like to select a number of columns and then run a macro that would insert two columns after each of the selected columns.
Or is there perhaps a faster way than using a macro?
I have to work on sheets with variable columns.
So for eg Col A Col B Col C Col D are fixed and then there are variable no of columns - Col E Col F and so on....
I have to insert columns after the last colum ie in Col F in this case.
However, the new column (lets call it Col G) will give a value with formula that connects Col E value with Col B (fixed)value. Again Col H (another new col) will also give a value with formula connecting Col H with Col B(fixed).
Easy to do in normal scenario but when I record macro in excel ( i cannot write VB), the variable columns make it difficult.
This is for my fantasy football league, so perhaps being familiar with fantasy football will be of help. It's somewhat complicated, so I'm just going to explain what I would like to do: Upon opening the Excel file, there will be a few questions. The first will ask what type of draft, with just two choices, A or B. Below that, it will have inputs asking how many teams, how many players per team, and, if option B was chosen, how many dollars per team. After answering the questions, there should be a button that will "generate" what I'm about to describe.
Choosing option A or B will result in two completely different sheets: -Option A will have X columns for the number of teams entered, and Y rows for the number of players per team. If possible, something will pop up allowing the user to input each team name.
-Option B will be a bit more complicated. I have a template of what one column will be for each team, and would need to have that repeated for the number of teams entered. Additionally, the number of players per team and dollars per team will need to be placed into an existing formula that will perform certain calculations. And, if possible, the number of players per team will alter the column template if the number is over a pre-defined number. And, like the previous option, it would present a pop up allowing the user to input each team name.
I apologize for the length of this question, and while it is too extensive to ask someone here to do, I would like to know what I can expect if I try to achieve this. Will I need to know programming code? Can you point me to some articles that will help me in terms of turning an inputted number into a spreadsheet with that number of columns after hitting a "submit" button?
My F4 key (which I use constantly) works for every "repeat" function except for inserting rows or columns. I can repeat every other option EXCEPT inserting rows/columns. I just had Office 2007 uninstalled and had Office 2003 put back on my PC. My IT group can't figure it out.
View 3 Replies View RelatedI have a column that I want to insert number 1 through ? incrementing by one. Can I do this simply without having to type in each one manually?
View 4 Replies View RelatedI've a list (Taken from this website; [URL] of 4900 rows with each row have 4 numbers if I want to add or insert one number( from 1 to 50 ) to each row with no duplicate to make it 5 numbers in each row? Is there a formula or Vb code to do this?
Code:
123411256212783129104121135512123161213147121543812162191217471012181911122023
12122230131224501412253315122641161227491712284218122932191234462012364421
1237392212384523124048241359251368261374427131013281311272913123630
[Code]...
To my calculation Numbers (1:50) have to be use 98 times;(50*98=4900) since each numbers in the list used 392 times(392/ 49 = 8)since there is no duplication when adding numbers (1:50) to rows each numbers must pair with evry other number in that list 8 times.at the moment with out adding the 5th number each number pairs with any other number exactly 24 times but adding process finish it should be 32 (24+8).
Is there a way to insert a specified number of rows. For example, I want to insert 500 blank rows today but maybe tomorrow it will be 650 blank rows.
View 1 Replies View RelatedI've created a spreadsheet that tracks problems using an assigned problem number. Attached is the spreadsheet.
I need assistance in inserting a new row and incrementing the sequential number after pressing the "Get/Assign New NCR Number" button. I am successful at getting a new row added, but am not able to increment the number. In addition, I would also like to have the table locked so that no deleting or amending the sequential number is allowed.
I work on a daily basis with spreadsheets in excel. The number of columns is the same, but every single spreadsheet has a different number of rows. I recorded this macro in a table with 1196 rows and I would like to use this macro also in other tables with a different number of rows.
Sub City2()
'
' City2 Macro
'
'
Selection.Copy
Cells.Replace What:="POMPANO", Replacement:="Pompano Beach",
[Code].......