I have two sheets in excel, one contains a list of names and the second is a basic lookup sheet for mail merge purposes.
Basically I want to write a formula in column A (of sheet 2) [current formula is =LabelNames!A2] and in column B the formula is =LabelNames!A3.
I want to drag these formulas down so each one jumps by 2... Ie:
=LabelNames!A2
=LabelNames!A4
=LabelNames!A6
formula that will produce the number sequence as shown in the third column of the table. The sequence starts at 1 and if the colour changes then the number is 2 and if the colour changes again then the number is 3 and so on. The part I'm having difficulty with is that if the name in the first column changes then the sequence must reset to the number 1 and become 2,3 etc. as above when the colour changes.
Peter
Blue
1
Peter
Blue
1
[Code]....
How can I create a sequence with a formula starting in A1 that will give 1,1,2,2,3,3 and so on horizontally?
View 3 Replies View RelatedHave this formula which works fine for finding the largest sequence in a list. (c/o Domenic from [url]
=MAX(FREQUENCY(IF('Overs-Unders'!B3:B1827"",IF(ISNUMBER(MATCH('Overs-Unders'!B3:B1827,{0,"n/a"},0)),ROW('Overs-Unders'!B3:B1827))),IF(('Overs-Unders'!B3:B1827="")+ISNA(MATCH('Overs-Unders'!B3:B1827,{0,"n/a"},0)),ROW('Overs-Unders'!B3:B1827))))
Now i need to:
(a) from the cells B5:CC5 that this formula runs through find the highest figure and return the name in Row 1 of that column.
(b) adjust above formula to get something now that ignores any run that contains 7 or more consecutive "n/a"s
(c) get a formula that counts the latest run. eg. from the bottom up (at the moment data only goes down to row 200)
Using VBA; How do I copy a formula in a cell to the next row and at the same time incrementing the row numbers in the formula?
For instance: Formula in D1 is = A1 + B1 + C1
Copy the formula in D1 to D2 but then D2 must contain the formula = A2 + B2 + C2
How to make formula for following situation:
Worksheet1RangeA contains a list of names in A2:A50 with corresponding scores in B2:B50
These values per name should be copied into Worksheet2RangeB, which contains a larger range of names in A2:A100 and scores in B2:B100. The names have a different sequence than Worksheet1.
Is there an IF (or other) formula that can take a scores from Worksheet1, look for the corresponding names in Worksheet2 and copy the Worksheet1 scores into the Worksheet2 column, each for the correct name?
That means every name appearing in Worksheet1 will have a copy operation of the score to its corresponding name in Worksheet2. If a name in Worksheet2 does not appear in Worksheet1, the score colum in Worksheet2 will get 0 or a fixed value, say 25.
I have a simple formula which refers to a cell on another sheet which I need to copy down a summary page so that every time I go down 2 cells in my summary it only moves down 1 cell in the sheet it refers to
Example
Sheet1 CellA1 = Sheet2!$A$1
when I copy this formula to Sheet1 CellA2 i need it to be = Sheet2!$A$3
obviously if I took the absolute references off and copied it down it would move 1 cell at a time but i need it to move 2
Not sure if this needs some code to work which I can assign to a Ctrl+? as I need to repeat the copy down over 100 times (however not limited to 100)
I am trying to copy the following formula down to additional cells
='Drawing 1'!$D$13:$F$13
but each formula must change on each row so would be
='Drawing 2'!$D$13:$F$13
='Drawing 3'!$D$13:$F$13
I am using a formula to create a value. I copy the information to another sheet and want to sort the data another way. When I sort the data I lose the value. Is there anyway to just copy the value to the new sheet instead of the formula as well.
Sub points()
Range("H2").FormulaR1C1 = "1"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>=R[-1]C[-1],R[-1]C+1,1)"
Range("H3").Select
Selection.autofill Destination:=Range("H3:H450")
Range("H3:H450").Select
Range("A2:I450").Copy Destination:=Sheets("AllRaces").Range("A1354")
Sheets("AllRaces").Select
End Sub
I'm building a master data spreadsheet to hold all of our data for the next fiscal year. There are about 300 data elements divided by month (our fiscal year is July-June). The intention is to go in monthly and enter data. How do I create the formulas and then copy them across the rows without getting the #VALUE! error or #DIV/0! error since I don't have data in the columns yet? Ideally, I'd like the formulas to live in the background and have the cells blank until I enter data for the particular month.
If it matters all my formulas are something similar to =IF(B4 = " ", " ", B4) or =IF(B4 = " ", " ", B4/B5)
How can I copy down an indirect formula? When I copy it the lookup reference doesn't change. My formula is: =IF(INDIRECT("Q1")="",INDIRECT("R1"),INDIRECT("Q1"))
but when I copy down the cell reference stays the same (I need to keep the indirect formula because I'm adding columns in column Q but it needs to reference column Q even when columns are added). From reading through some other posts I believe I need to add a ROW() or COLUMN() formula in there somewhere.
I have a formula that returns a string using the concatenate function. Now I want to copy the string to another cell without copying the formula. The string will become a record in a CSV file. I cannot figure out how to make what shows on the screen a label cell instead of a formula cell.
View 3 Replies View RelatedI need a code that looks through Column A (account numbers) then adds up the profit from Column X for each account number. The total profit for each account is then sent to different sheets. Account 100 profit sent to Sheet ‘S1’, Account 200 profit to Sheet ‘S2’, and so on…….The profit figure will go to Column C (Sheets S1,S2,S3..), in the next empty cell.
I have a code here that works, but problem is it places a formula in the cells in the output sheets. I dont want any formula there, just the value that is calculated. This is because everyday there will be new values in the 'Summary' sheet, and if the macro inserts the same formula everyday I data(profit figure) from the previous day gets distorted.
copying a formula. Basically i want to paste special a formula result automatically.
I have a if function set up so when 2 days corespond then in the 3rd cell it records the value, however when ever the date changes the value disappears, i can keep the value u there by paste special but i would love to set this up as an automatic thing once a value is recorded it records the value and deletes the formula, or the value doesnt change when the date (thus the condition) changes...
I am unsuccessfully trying to manipulate and copy a formula.
Column A would typically be used as a benchmark for one retailer and have the base price with the other columns simply showing differences in margin etc if the price was changed. This would typically mean the BWP and fixed terms etc would be constant.
However in this circumstance I need to ascertain for different retailers a,b,c,d, etc the shelf price and compare their NTS and % Margin. Whilst I can enter the different prices and change the terms as required, the whole sheet seems to link back to column A rather than simply drawing from the info in each respective column.
a
b
c
Price On Shelf
20.12
20.05
18.87
Shelf Price Excl. GST
17.50
17.43
16.41
[code]....
I have obviously hit a combination of keys that has turned this function off. I can no longer drag and fill cells with a formula, it will only copy the original text/value into adjacent cells. How can I fix this??
View 9 Replies View RelatedI want to copy the following function via vba to a number of cells
=countif(i10:ah10,"does not comply")
i basically want row to do a a comparison from i10 to ah10, row 11 to do a comparison from i11 to ah11 and so on.
i tried this as a first step
[vbcode]
Workbooks(WorkBookFileName).Worksheets(TabID).Cells(m, fc + 8 + 1).formula = COUNTIF(I97:AH97,"does not comply")
[/vbcode]
but i got the error
"compile error: expected: list seperator or)
and i tried this one
[vbcode]
Workbooks(WorkBookFileName).Worksheets(TabID).Cells(m, fc + 8 + 1).text = "=COUNTIF(I97:AH97,"does not comply")"
[/vbcode]
and got the error compile error: expected : end of statement
i read some place to ensure that the reference is copied, one can use the following
[vbcode]
wks.Cells(i + 13, 7).FormulaR1C1 = wks.Cells(i, 3).FormulaR1C1
[/vbcode]
but cant get through the first step, where i actually use vba to make the first entry
I have the following Formula in D2 which counts up and at the d5000 call it has...,
D2:
=IF(($A2 & $B2 & $C2 ""),IF(COUNTIF(CSM!$C$2:$C$5000,CCH!C2)>0,"YES","ADD TO CSM DATABASE"),"")
E2:
=IF(($A2 & $B2 & $C2 ""),IF(COUNTIF(Skyline!$C$2:$C$5000,CCH!C2)>0,"YES","ADD TO Skyline DATABASE"),"")
F2:
=IF(($A2 & $B2 & $C2 ""),IF(COUNTIF(Canada!$C$2:$C$5000,CCH!C2)>0,"YES","ADD TO Canada DATABASE"),"")
D5000:
=IF(($A5000 & $B5000 & $C5000 ""),IF(COUNTIF(CSM!$C$2:$C$5000,CCH!C5000)>0,"YES","ADD TO CSM DATABASE"),"")
E5000:..............
I have two ranges of cells
1. A12:F28 where some cells contain data and some do not
2. C127:D132 that contains no data
What I want to do is: If A12 is not blank I want to show the value of cells C12 and F12 in cells C127 and D127
The next non-blank cell in column A will display the values in cells C and F in cells C128 and D128
The selection of the A column cells will be random, i.e A12, A22, A15.
Is there a formula that can be written for cells C127:D132 that will make this happen? This needs to be a formula because my users (and my boss) do not understand VBA and will not accept any code.
I will use the COUNTA function in cells C127 through C132 to select the row as the number of non blank A column cells increases.
I got a sumif formula, where I sum the hours if a certain person is represented. =SUMIF(K12:R12;"TW";K11:R11) Hours in row 11 and the person in row 12.
The patern in my excel document is that every second row is hours and persons. Hours on odd row number and persons on even.
My problem is that when I want to copy my formula down, it goes one row down to =SUMIF(K13:R13;"TW";K12:R12) I want it to go two rows down to =SUMIF(K14:R14;"TW";K13:R13)
I'm annualizing monthly returns. I have the formula for a specific year and would like to drag the formula down to annualize the monthly returns for the following years of data (1982-2013).
The monthly returns start in cell C4, with January 1982 and go down monthly in the C column.
My formula for annual returns is
for 1982 is {=PRODUCT(1+C4:C15)-1}
for 1983 is {=PRODUCT(1+C16:C27)-1}
for 1984 is {=PRODUCT(1+C28:C39)-1}
and so on.
How do I copy or drag the formula to annualize the rest of the years while keeping the pattern above without manually typing each year?
I bought a new computer with Windows 7. A file that is fine on my old unit does not work on the new one.
Column "H" should provide a list of birthdays comming up in the next two months, but something is not working.
I have the following macro which works fine accept for the fact that it copies the formulas from other worksheets, where as i only want to copy the values.
View 3 Replies View RelatedI want to do is copy a formula from one workbook to another workbook that I have open - have tried just copy and paste (only get the result, no formula is copied) and have tried paste special, but there doesn't seem to be anything relevant on the list. I'm sure there is a simple way of doing it.
View 2 Replies View RelatedI am doing a VLOOKUP and i need to copy it over 2000 cells, problem is when I copy it, the array changes, not just the Lookup_value
Example:
=VLOOKUP(A19,Sheet2!A2:B408,2,0)
this is the first cells equation and when i try to copy it to the next cell or do a fill it changes it to:
=VLOOKUP(A20,Sheet2!A3:B409,2,0)
Obviously the Lookup_value is doing the right thing, but the table array is not. and I know i have done this before, not sure why it is doing it now.
Im using the below formula to paste information into a new spreadsheet based on the if then statement. However, as i paste the formula down the sheet, it doesnt reference all the cells. The formula stays on the cells it referenced initially. How can I make the formula reference all the cells as i drag it down?
=IF('[All Cases ATL.xlsx]New_All_Case'!$C$2="DA1",IF('[All Cases ATL.xlsx]New_All_Case'!$T$2="A",'[All Cases ATL.xlsx]New_All_Case'!$E$2,""),"")
I would like to copy only the IF Formula without copying the cell reference. Say for example I have a formula shown below:
BEFORE
A1=IF('PW216'!G7>=0.9999,0.999,'PW216'!G7)
AFTER
A2=IF('PW216'!AC7>=0.9999,0.999,'PW216'!AC7)
My formula is =IF('PW216'!G7>=0.9999,0.999,'PW216'!G7) is in Red Font color. I want to copy only the formula but when I paste it, it will as well copy the cell reference.
I want to copy a D1-C1 formula all the way down column E. If I don't have any data in column D1 and C1, however, I don't want anything to appear in Column E.
Is there a way to hide the formula once I have built it for the all of Column E? The reason I need this to happen is because I can't have any zeros popping up in Column E, because I am calculating a running average of Column E and "zero" values would throw off my average.
I have a macros which will search down column "A" and when it finds the word "Total" it inserts a new line above it. How can I add to the Macros, so that it will make it copy the formulas in the cells directly above the new line and paste them onto the new line. I would like it to only copy the formulas in Columns "C" to "F".
I am having a problem combining two databases. I have database E and F. I need to take any price that is $0.00 from Database E and replace it with the data from database F. The catch is that there isn’t always a price to replace it with. Also, there are parts in database E that do not appear at all in database F. Below is an example (there are about 20,000 lines of data total)
In the examples above I have placed both databases together. Column four has the database designation. As you can see the 1748 Hose reducer has a price for F but none for E.
Basically I need some formula like the following:
If (part number xxx) and (part number XXX) from column 1 are the same, replace the price data from E with the price data from F but only if E = $0.00
Is this possible?