I am trying to do is have an additional row, or rows entered after after the last available row is filled. I've written a macro that searches for the last data set in a column, and will then copy the row above and insert, then copy the formating and formulas down, but I can only make this work by having the user click on a control button.
I would like to make this macro work automatically when data is enterted into the last row in the quote form. Below is what my current macro looks like:
Sub AddLine()
With ActiveSheet . Unprotect Password:="*********" ActiveSheet. Range("m17").End(xlDown).Select ActiveCell.Offset(1, 0).Select Selection.EntireRow.Insert ActiveCell.FillDown ActiveCell.Offset(-1, 0).Select Selection.EntireRow.Select Selection.Copy ActiveCell.Offset(1, 0).Select Selection.EntireRow.Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveCell.Offset(1, 0).Select .Protect Password:="*********" End With End Sub
I want to concatenate several cells, some of which are formated as fractions, and have it display those numbers as a fraction. When I perform the function, it displays the cell in its decimal form.
Example: =CONCATENATE(E2," ( ",F2," in. "," ",G2," ",H2," in. ", I2," ",J2," in."," ) "," color #")
Where: F2, H2 and J2 are fractions.
Result: 12" Stretcher ( 11.625 in. x 23.625 in. x 3.625 in. ) color #
For making quotes at work, I'm looking to have the quotes named automatically. The format of naming our quotes is the date (in this format) then quote name: yymmddNAME (Example for today: 090720EXCEL)
I'm using the =NOW() function for the date (Cell A1), then doing a custom format to turn the format into yymmdd then in a different cell I'm putting in the "NAME" (Cell A2) and what I want to do is combine the 2 cells (Cell A3) by doing: =A1 & A2 but when i do this, the format of the date gets messed up.
Is there a way to have it show the number of days, hours, minutes and seconds? So it will say 45:1:23:42? (45 days, 1 hour, etc...) Or something along these lines?
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost Col B = The number of the month when the costs are starting (1=Jan etc) Col C = The duration or the number of months for which the costs are to spread Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
I am trying to find a formula for the output cell. Suppose the designated cell = 7 due to formulas above it. So that number is always given but can change. I then need a formula for the ouput cell that says if the designated cell is greater than or equal to 1, and less than 10, then the output cell should equal .034. So suppose the designated cell = 18.2. Then I would want the output cell to be .072 since it is greater than or equal to 15, and less than 25. The template I am using has 23 rows of numbers like above. I didn't know how to do it for 23 rows though. If/Then doesn't work because it is 23 statements long (well over the seven limit) and the vlookup only works when the cell is equal to a number and not a range.
I'm trying to use the formula vlookup, but running into trouble. I'd like to use =VLOOKUP(3000&A1,E:F,2,0) but this isn't working correctly because it doesn't recognize "3000&A1" as a number? Is there a way to do this? This will be used in a VBA code, so I prefer the solution in VBA, but either way is fine. See attached for more info.
Is it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:
A1= "1" A2= "2" A3= "3"
Then I type in the simple formula in the cell B1= "=A1"
and then drag down but it skips 3 cells so then
B5= "=A2"
B9= "=A3"
If it didn't skip then B1= "=A1" B2= "=A2" B3= "=A3"
this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.
In sheet 1 I have all the data needed for sheet 2, organizing in row, which means for a particular cargo, its details will be all having same row number, different column alphabet.
Cargo A data 1 data 2 data 3 ....... and so on Cargo B data 1 data 2 data 3 ....... and so on
In sheet 2, I have a form in which I wish to have it auto fill in the cargo particular by inserting in just the row number for the cargo in sheet 1.
Cargo A
data 1 =Sheet1!A#
data 2 =Sheet1!B#
data 3 =Sheet1!C#
"Cargo A is row 2, then # should be substitute with 2"
For instance I have a formula such as =sum(A1:A9). But imagine that the range is not always till row 9 but depends on the total number of rows that are in the table. How can I change that 9 so that it takes a number equal to the total rows in the table?
The final formula I want is not that simple one. It is actually
I want to change 1047 by the relevant number of rows. Also, I do know how to do it in VBA, but I have a problem with the length of the formula there (not that one, but other bigger than that)
This does not seem to be working for me. I need to check one number against another and if the one cell is larger then the other subtract it or just the reverse.
I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?
I need a simple formula that would save me some annoyance. Basically I have like 20 columns filled with various numbers. At the last column I like to take the last number in that row. However each row "last number" is in different column. So how do right formula that would basically take the last value in that row?
I have illustration here. column 1 column 2 column 3 column 4 Final Value ROW1 3 5 7 7 ROW2 2 2 ROW3 1 4 4 ROW4 8 8
What I want to do is write formula in Final value that will take the last number in the row.
I play Keno a lot and have what is probably a very simple formula request. If I’ve got for example this result pasted into Excel: 06 12 15 21 23 27 28 31 32 34 48 49 54 56 60 67 68 74 76 77. How would I go about determining if a pair or two numbers had come in?
Say I was looking for 31 and 76, is there some way I can get Excel to recognize the two spot hit then post in response either 1 (in bold) if yes or 0 (not bold) if no the two did not come in together? Note this would not be only one or the other, they both would have had to hit at the same time.
I have a formula that computes the row number in B58. I would like to combine the row # in the formula after M_MAN!A1015 for eg M_MAN!"&B58&" instead of having to type this. I can then use the foormula to copy the balance of the formulas
Is it possible to determine how many times a formula will be used? Here’s my problem.
I have items that are bought, and then altered. So I need a cell to determine how any times my ‘IF’ formula is used. My current formula is:
Formula entered into D3.
=IF(D2>0,D2,””)
Row 2 is the amount and Row 1 are dates.
If I drag the formula to the right, E2 will be equal to 0 so will enter 0. But I need it to say what ever is in D2 3 times. (As this particular item takes 3 weeks to do) So D2 & E2 & F3 will all have whatever was entered in D2.
In a cell there would be a formula, an "if" formula and I want to know is there something in Excel that will lock in a true statement. Like lets say in cell "A1" a formula would read =if(b1>1,.001,.002), I want to lock in a number when the statement is true. Cannot be change no more after when it is true.
If/when I have removed information from a row, can anyone tell me how to remove empty row but leave row numbers intact and following each other in numerical order.
I have a monthly report on an excel spreadsheet that I must sum two columns from the previous month for every row in the sheet. I wish to take the value from column B and add to the end of the formula in column A. For instance my column A would contain the following: "=1200+6595+2599+275"
Column B would be a single number, i.e. "3200"
I want to be able to click a button and get "=1200+6595+2599+275+3200" in column A and place a "0" in column B for every row on the sheet. I have a pretty good understanding of VBA, but I am still learning the Excel object model.