Not Repeat The Same Formula In Each Cell, Over And Over Again
Feb 27, 2009
I have a 34-page workbook. On Every Row (starting on line 4), and on Every Page (except the first "Summary" page) is repeated, this same formula....over and over again in column "N":
=IF(A4<>"A",(IF(AND(A4<>"A",OR(J4="",J4="NT")),"OK","WARNING: EXECUTION NOT EXPECTED OF LOW PRIORITY TEST CASE!")),IF((LEN(J4)=B4),"OK","WARNING: STATUS DOES NOT EQUAL TC COUNT!"))
So, since this keeps repeating over and over again, is there some way (either by formula, macro, or whatever), that I can prevent the constant repeating on every line, to bring the file size back down? [Note: This formula alone actually added almost 5MB to the file size!]
Two things to know:
- The formula above is seen on row 4 (that's why you see "4" all over the place). On Row 5, it would be "5" instead, and so on...
- The formula above always starts on row 4, and goes down to the "last used" row, in column "N" (so there's other information in that last used row as well).
....hopefully this is a very easy thing to do?
(PS: Thanks PeterSS for the help creating the above formula in the past...hopefully it can now be made less repetitive to conserve ~5MB of file space!)
View 10 Replies
ADVERTISEMENT
Aug 27, 2013
how to get it to fill down. Basically depending on the contents of column D, a formula (which also needs to fill with the rows) is copied from sheet 2 in to column K.
Code:
Sub Copy()
With Sheets("sheet1")
typ = .Cells(Rows, Count, "D").End(x1Up).Row
[Code]...
That's what I have. I am totally stuck. the formula needs to relate to column G and fill down too. this is the formula
HTML Code:
=IF($G2=2,$G2*90,IF(AND($G2<4,$G2>2),"$270.00",IF($G2>6,360+(($G2-6)*50),IF(AND($G2>=4,$G2
View 2 Replies
View Related
Jan 14, 2013
I need a macro to find the last cell in the column, then copy the formula to the next blank cell. Then, it goes back to the last cell (above) and paste's values. Then, go to the next column and repeat the process. I can do this but have to call each cell separatly...however, I would like to do it in a loop to simplify things. It would be great to even be able to just set the start and ending columns. Here is my current code:
Dim rng As Range, aCell As Range
Set rng = Range("C8, D8, E8, F8, G8, H8, J8, K8, L8, M8, N8, O8, P8, Q8, R8, S8, T8, U8")
For Each aCell In rng
Selection.End(xlDown).Select
Application.CutCopyMode = False
[Code] .......
It does not go to the next column, instead it stays in the same column and repeats the process.
View 8 Replies
View Related
Feb 27, 2014
how I can have a formula repeat down a column five times before it changes to another formula? For example. Say on tab 1 I have a list of products. On tab two I have five codes that repeat down the page over and over again. On tab two next to the repeating codes I need to repeat product one 5 times and then skip to product 2 on the 6th row and repeat five times and then skip to product 3 on the 11th row and repeat 5 times and so on?
View 10 Replies
View Related
Dec 15, 2008
How do I keep repeating
If Range("E22").Value Range("G3").Value Then
Rows("22:22").Select
Selection.Delete Shift:=xlUp
Until E22 does = G3
Then I need it to go to Row 23 and do the same.
My goal is to leave only rows (between rows 22 and 50) that have the same value in its column E that match cell G3.
View 9 Replies
View Related
Jun 9, 2014
I'm trying to create a formula (if possible) that will repeat column headers down a row and repeat every nth time. I have text values in cells E6:AL6 and would like to use a formula that will make it so that E6 will show up B2, F6 will show up in B3, G6 will show up in B4, etc. And then when it gets to B35 (AL6), B36 will then revert back to E6, and the loop will continue - B37 will be F6, B38 will be G6, etc.
My thought is to create an offset/address of some sort that will read as OFFSET(E6,0,x+1) and if x>35, start back at 0. However, I don't think you can create such a formula using an offset?
View 5 Replies
View Related
Feb 19, 2010
I have a formula that is working, but I want to repeat it every seven columns. I know I can drag it and release it (or drag a few and release them in the appropriate column), but at this point I have so many columns that it's quite cumbersome to do that. Is it possible to create a macro that will automatically repeat the formula in every cell seven columns to the right (the same row for all)?
My formula is in cell K7 is:
=IF(AND(K24<=0%,K24>=-2%,OR(J32="T",J32="F")),1,"FALSE")
Now I want that formula to repeat (with the proper adjustments) for cell R7, i.e.:
=IF(AND(R24<=0%,R24>=-2%,OR(Q32="T",Q32="F")),1,"FALSE")
and then to automatically continue like that for cells Y7, AF7, AM7, and so on.
View 6 Replies
View Related
Aug 21, 2009
I'm looking for is to repeat this formula within the cell, so that it can pull the information of not only the first match, but all matches proceeding it. Right now it only pulls the first match it finds, but I would like it to add every time a row matches the criteria of the formula.
=(LOOKUP(2,1/(('Paste SFHS'!$A$2:$A$9=A2)*('Paste SFHS'!$B$2:$B$9="08-Wave/Stu. Goals")),'Paste SFHS'!$C$2:$C$9))
View 5 Replies
View Related
Feb 4, 2009
I have a column of text strings in a2.a??? this is my data. Each text string has a persons name somewhere in it with other text around it.
In column c2.c??? i have text names of people names im searching for. eg. bob,jane,harry
what would be a pratical use of the vlookup function to return the name in column b2.b??? if the name from C was located in the string in A this is a non case sensertive requirement.
View 9 Replies
View Related
Jul 8, 2009
I am in the process of developing a budget template for next year and here is what I want to accomplish through Macro to avoid the tedious mannual manipulation process (hope that's achievable). BTW, I am only an entry-level Macro user who has no background in coding.
I have attached an Excel sample but let me explain:
In each of the expenses tabs (1, 2, and so on (only 1, 2 given in the sample)), I want to be able to select a range of the data cells (A19:Q34 in "Expense 1" and A10:Q28 in "Expense 2") and run this Macro so that:
1. 10 blank rows be inserted between selected data rows
2. A sum created for each month from the four rows below) with different color
3. A Year to Date Actual and To Year End Forecast created based on the VLOOKUP of the "P&L" matching the account code by month
4. Finally grouping of the first 9 rows.
the above is what I have manually created for the first account code in the "expense 1" tab. I have 7-8 expenses groups and about 200 account codes. So mannually creating the above for each expense groups is not that terrible if this will make life easier for Macro.
I would think this is a very typical Macro issue but can't really come up with anything.
The other issue I ran into the VLOOKUP for the YTD Actual and TYE Forecast. It is to look up accont code in the "P&L" tab and match the #s. But the column numbers in the VLOOKUP do not change automatically when I copy across. Thus I have to change mannually, which is really a pain in the butt. I also know I can't copy down because that will change the account code that I want it to match. Because to be able to copy the VLOOKUP formula across the same account code, I need to use the $ to fix the account code. But to copy down I think I need that to be without the $ sign. Any solution on that?
View 9 Replies
View Related
Mar 31, 2014
I wish to create a spreadsheet which has formulas which would enable me to see if a patient has had more than one referral created within the next 30-90 days [the actual number of days not important - the ability to modify the formula to reflect which time period required would be cool] and also to see if a patient has been referred for the same medical condition again. Thus in my mind at least two different formulas - has the patient been referred again within a time period of days and has the patient been referred again for the same medical condition.
Attached is an example referral spreadsheet I have created
View 4 Replies
View Related
Apr 9, 2014
how to make conditional formatting for border line (top & bottom) after repeat 5 data/text ....
it's possible with CF formula and do not vba?
see my excel file attached..
View 4 Replies
View Related
Oct 28, 2008
I need to repeat the following rows and formula with column a starting at 9150 and going through 15,000 in 50 point increments. Need formula I can copy and paste easly. see the attached example.
View 4 Replies
View Related
Dec 29, 2008
I can get only so far, then stumped:3 columns (Hours, Rate, Amount). "Hours" is a 'List' with data from from another sheet. Data is named 'Worked'. 'Worked' is all 2 decimal numeric (represents total time worked), except first item called "Live In"
IF 'Hours' is "Live In", I can use: =IF(A1="Live In", SUM(B1*1))
How can I make it conditional so that if it's not "Live In", then it will SUM(A1*B1)?. SideNote: 'Worked' LIST has total time with minutes expressed as 1/4 of hour (i.e., 1.25 = 1 hour, 15 minutes).
View 5 Replies
View Related
May 20, 2013
Keep getting compile error.
Sub CreatingCT()
'
' CreatingCT Macro
Dim ConsolidatedTrend As String
'Path to File
ConsolidatedTrend = "(d:2013"&(IIF((MONTH(NOW())+1>9),"(1","(0")&(MONTH(NOW())+1)&") "&TEXT(DATESerial(YEAR(NOW()),(MONTH(NOW())+1),1),"MMM")&"Consolidated Trend - "&YEAR(NOW())&" "&MONTH(NOW())+1&" + "&(12-(MONTH(NOW())+1))).xlsb)"
ActiveWorkbook.SaveAs Filename:=ConsolidatedTrend, FileFormat:=xlExcel12, CreateBackup:=True
End Sub
View 5 Replies
View Related
Feb 8, 2014
I would like to repeat a formula and make its dynamic depending on the request.
The formular that I would like to repeat is:
=IF(ISERROR(INDEX(Data!$A$2:$G$14997,SMALL(IF(Data!$A$2:$A$14997=$D$2,ROW(Data!$A$2:$A$14997)),ROW(985:985))-1,3)),"",INDEX(Data!$A$2:$G$14997,SMALL(IF(Data!$A$2:$A$14997=$D$2,ROW(Data!$A$2:$A$14997)),ROW(985:985))-1,3))
View 2 Replies
View Related
Apr 1, 2009
I'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.
Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.
So I need something that can take each control number that has three cycles and add their # of 0:15 together.
The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.
Ultimately I would like the results on a separate sheet.
This is only a part of the file, there are over 1000 different control numbers.
View 14 Replies
View Related
Apr 24, 2014
Formula that will repeat a cell number as it drags down and as soon as the number changes. I am using helper column that shows the cell number. I need to drag this down about 1000 rows.
Excel 2010
A
B
C
1
Helper Column
Desired Result
[Code].....
View 6 Replies
View Related
Mar 23, 2014
I have values
2
8
9
As so on in my cells a1,a2,a3 as so on
And I want value of column a repeating five times of each cell value in column b
like result
2
2
2
2
2
8
8
8
8
8
9
9
9
9
9
and so on
View 3 Replies
View Related
Jan 28, 2008
I have a worksheet with a column with words, but a lot od the cells are empty. I want these replaced with the word above. I know I can do this with fill handle, but as there are thousands, it would help with a routine - a program - that I could run on the column and thus fill out all the empty cells.
View 12 Replies
View Related
Oct 12, 2009
I have 4 condition which change row 3 to different color: example of one - cells on row 3 will change to Blue "=$A$3:$AE$3" on condition =If($AE$3="Absent"; True;False).
How can I repeat this formula for rows 4 to 500. i.e. on each row cells A to AE should change color based on the value of Cell AE?
(Colum A was hidden, MUST be visible to work)
View 2 Replies
View Related
May 6, 2014
I have the following Macro to transpose data from a column into succesive rows. I need it to repeat, until it has processed all data in column A / until it reaches an empty cell in A.
Sub Macro1()
'
' Macro1 Macro
'
[Code]....
View 3 Replies
View Related
Oct 16, 2012
How can I repeat identical data in the previous cell. For example I have in Cell A1 - Marketing then I have in cell A2 to A3 same Marketing but it is not shown. Then I have in Cell A4 - Operations then Cell A5 to A8 same Operations but not shown. I need to have them all shown as in Column B,
A
B
1
Marketing
Marketing
2
Marketing
[Code] .......
View 3 Replies
View Related
Jan 6, 2014
I am new to macro. I am trying to convert the following from column 1 to column 2.
1
1
0
1
3
3
[Code] .....
In the left column, the numbers who are greater than 0 can have the same number of rows of zero.
Is there anyway I can do it to convert Column 1 to column 2.
View 5 Replies
View Related
Aug 14, 2008
I would like click in cell a6 and then click a button loaded with VBA code that would:
1. Offset over 2 and down 1 to c7 so that is the current cell
2. Repeat down until an empty cell is reached (our example will be c10)
3. Put the items found in c7, c8, c9 into a variable (is that right?)
Variable would then be used in a vba generated email that I have all ready to go. I would want each item placed in the email one after the other like:
Name
Date
Phone
View 9 Replies
View Related
Jul 14, 2006
what I need to do is , while an id value is constant copy 2 adjacent cells to a new location on the same row as the first occourance of the id value, then if the id is the same on the next row repeat the copy and add these 2 values to the cells next to the ones previously copied.
Once the Id value changes then the copy restarts on a new row which is the same as the first occourance as the new id value and repeats the same sequence as described in the first statement.
I have attached a sample spreadsheet showing an example of the source and how I would like it to end up, the colour is only there to seperate the outcome from the source and is not required in the code.
View 9 Replies
View Related
Aug 26, 2013
I am using the code below to combine the rang of cell values into 1 cell. with this code I need to select the range of cells manually and run the code then repeat it on the next row manually.
What I need is automatically find the range from selected cell till last cell in the row and run the cod for this range then do the same on next row until last row of the sheet.
Code:
Sub JoinAndMerge()
Dim outputText As String[code]....
View 1 Replies
View Related
Mar 4, 2014
I need to repeat the following copy past macro the number of times equal to the value in column B and repeat the process for rows 3 through 27. It would end up being a loop with in a loop. The data in C3:H3 will be pasted onto the "Fixt#" sheet as many times as the value in column B3. The idea is to create an individual entry on the "Fixt#" sheet for each row on the "Totals" sheet.
Code:
Sub Create_Fixt_SheetI()
'
' Create_Fixt_Sheet Macro
'
'
Range("C3:H3").Select
Selection.Copy
Sheets("Fixt#").Select
ActiveSheet.Range("B" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
View 3 Replies
View Related
Jul 7, 2009
I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above.
For example - before macro:
Joe Bloggs 2
Adam Wilson 10
Peter Andrews 0
Claire Burrows 6
After macro:
Joe Bloggs
Joe Bloggs
Adam Wilson
Adam Wilson
Adam Wilson
Adam Wilson....................................
View 6 Replies
View Related
Jun 11, 2008
I can export inventory data from my Point of Sale system that looks like this:
I need to manipulate it to look like this:
To do so I think I need a macro(?) to:
Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.
This would allow me to print labels for every item in my inventory.
View 3 Replies
View Related
