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 have a database with different categories assigned to employees. I wan't to do the following. I will use just as an example, I want to some how accomplish: If Cell A in worksheet 1 equals cell C in worksheet 2, then replace Cell L in worksheet 2 from cell b in worksheet 1. Basically we have a database with phone numbers with corresponding purchase codes for each phone number and their names. The problem is, all the purchase codes are incorrect on the spreadsheet. We have an updated spreadsheet with just phone numbers and no names. I need to somehow pull the proper purchase code from one worksheet to the old document replacing the old purchase codes.
know while doing paste special whether we can copy the data in reverse manner instead of regular pattern. Here, I would like to explain my query in detail.
As you all aware that we use paste special command for multipurpose requirements such as breaking the links/transpose etc. But, here I need to copy the data from last row figure as first in the same context first one as final.
Ex: if Data is 123456 and if we paste the data by using paste special, data will be same may be in in row /column. Here, I wanted to copy the data as 654311 which means reverse. Hence, kindly guide me whether is there any command/formula for doing the same.
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 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.
I 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.
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??
I 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 a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
I have a range of weekly data that I need to copy into another workbook, paste it below the data that already exists there and then delete the data from the original workbook. I would love to do this with vba but after hours of searching on how to do this my brain is frazzled.
I have 50 workbooks that I need to import weekly into one master sheet (Master.xlsm) but they need to be done individually after the weekly data has been checked. The master sheet will therefore have existing data and the new data needs to be appended at the bottom. Also column A will be blank in both workbooks so to find the last used row it will need to look in column B.
The number of rows in the weekly sheet will also vary rather than be a fixed range so I guess the last used row will also need to be found there too.
The attached file is a cut down version of my working file showing where the data starts on Row 14, I won't need to copy the headings.
X Y Z Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep A B C
The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.
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 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.
I 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.
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?
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.
