Increment Part Criteria Of Formula When Dragging/Copying
Sep 22, 2007
As per this screenshot,
I am trying to work out a method which will allow me to drag the forumula which is in cell B13 from B13:B17, and have in increase the date serial number by one for each row by which it descends.
In other words I want to drag the formula down to, say B14, and have it increase the date serial to = that which is in cell A14.
[the obvious benefit of being able to do this is that I don't have to manually adjust the date serial in each cell on each new row]
View 9 Replies
ADVERTISEMENT
Nov 29, 2012
[URL]
I have data in worksheets that make up the 52 weeks of the year. Each sheet is numbered like this "WK1" "WK2" and so on... up to "WK52"
Each sheet is setup exactly the same so all cells are the same.
So I have a summary sheet that structures the data from all the week sheets to plot to graph all within the same file.
Thing is when I link the first cell to the first sheet say WK1 and then try to drag the formula down to the worksheet number does not increment
Tried the code in the beginning of this post and it displays the worksheet and cell number in the cell cant get the actual data to be displayed.
When I drag this formula down I want it to index the worksheet number
=WK44!$AG$14
Like this
=WK44!$AG$14
=WK45!$AG$14
=WK46!$AG$14
This is what I get in the cell when I use the formula given in the post
WK01! $AG$14
View 4 Replies
View Related
Mar 3, 2007
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 Related
Jan 15, 2008
I have a spreadsheet containing quite a few lookups etc. I am trying to copy a cell across about 10 columns. This has to be done for about 50 different rows on about 20 different sheets, so I am looking for an alteration to the formula to help me rather than typing in the formula over and over;
='Basic Labour'!AD6*Rates!$E$526
Say this is in AD6, then in AE6 I would require
='Basic Labour'!AE6*Rates!$E$527
and so on across the region to be autofilled...
(It is multiplying the same cell in a different sheet against the 'next row down' in a rates lookup sheet).
View 4 Replies
View Related
Jan 5, 2008
I need to check the result of the difference between 2 cells in the same column. But It needs to be done with an increment of 2 e.g. A2-A1 then A4-A3 and so on.
View 5 Replies
View Related
Apr 27, 2014
I am creating a training document for work; the sheet I'm creating is a summary sheet which works out how many people answered questions correctly, incorrectly (bringing in from another sheet), number of questions answered and percentage of correct answers.
Looks like:
Correct: =COUNTIF(sheet1!E2:E36,"correct")
Incorrect: =COUNTIF(sheet1!E2:E36,"incorrect")
Number of questions answered: =SUM(C4+C5) (correct+incorrect)
Percentage right: =SUM(C4/C6) (correct/number answered)
I need to drag these 4 formulas down into another 400+rows, however in the correct and incorrect formulas I need it to keep the 2:36 but change the e to f, g, h, etc.
View 14 Replies
View Related
Feb 26, 2014
I'm having to copy formula on a tabulation sheet that compiles information from other worksheets, and I need to change the formulas in each row to refer to the successive worksheets. Right now I don't know any other way than changing the formula in each cell, and this is taking way too long.
Example:
One cell's formula: =COUNTIF('5'!$F$21:$T$50,TABULATION!C$5) In the next row down, I need it be: =COUNTIF('6'!$F$21:$T$50,TABULATION!C$5), then =COUNTIF('7'!$F$21:$T$50,TABULATION!C$5), and so on.
[URL]
View 4 Replies
View Related
Mar 16, 2009
to copying a formula from one column to the next adjacent column and also incrementing the 2 numeric values inside the formula. Please see attached sample.
One value increments with each new column whereas the other increments with each new row. Please see attached sample.
And in the last row there is the average formula which must copy the new column and average the new column.
I tried to create a Macro in attached but it did not work with formulas.
View 8 Replies
View Related
Jan 12, 2014
I have a spreadsheet serving as a simple document register. Column A is a unique document reference number, which is a simple concatenation of some text which prefixes all of the document numbers, plus the date in "yyymmdd" format (from column B) and a 3-digit sequence number (from column C).
Column B has no formula - my macro just pastes a timestamp in there.
Column C is a sequence number field which has a formula comparing the "yyymmdd" part of the timestamp with the cell directly above it. If they are different then the sequence number becomes "001" - if they are the same then the sequence number is incremented by 1, so all docs created on the same day have an incremental sequence number.
Here's the problem. When I want to add a new document I need to copy the last row down to the next row (in preparation for the next document to be created by my system) and there are two ways I've been doing this - one which works and one which doesn't.
This works > Say my last row (with the hidden formulae) is row 13. I select A13:C13 plus the three blank cells directly below (A14:C14) and do a Ctrl+D to copy them down. All is well.
But the following doesn't work - why? > I select the entire row 13, copy it, then reinsert it at row 13 so the formulae in row 13 get shifted down to row 14. What goes wrong here is that my formula in column C which WAS comparing the date in B13 to the one in B12 NOW compares B13 to B11
I've attached an example spreadsheet to try and demonstrate the problem. Sheet 1 is the original sheet. Sheet 2 is the "copying down" method which works, and Sheet 3 shows what happens when you copy the entire row 13 and "Insert copied cells".
excel-insertrow-formula-problem.xls
View 9 Replies
View Related
Aug 4, 2009
I have this If statement on one of the cells
=IF(A7=AQ6,AQ32,IF(A7=AR6,AQ32,IF(A7=AS6,AQ32,IF(A7=AT6,AQ30,IF(A7=AU6,AQ29,IF(A7=AV6,AQ33,IF(A7=AW6 ,AQ33,IF(A7=AX6,AQ31,""))))))))
If I want to change only A7 to A8, A9, A10 without changing the other parts of the formula, how do I do that. I need to copy this formula in about 300 rows.
View 3 Replies
View Related
Mar 14, 2013
Is there a way to copy a formula from Cell A1 down to A1500 withhout dragging the fill handle?
View 4 Replies
View Related
Aug 16, 2007
I have a cell with a value of, Text 1 and in the next field I want it to display Text 2, then text 3 and so on.
Is there any way of doing this? I guess I am looking for something like a1+1, just a shame it doesn't work.
View 9 Replies
View Related
Jul 22, 2014
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.
Something similar to this B1 "=A1(skip3cells)"
View 4 Replies
View Related
Jul 14, 2009
i have a sheet with alot of data, its a contacts list. colum A is Company name, B contact name, C address, D postcode. One row = one contact. On the next sheet i want to have on column with each contact set out as if it wer on an envelope, so i did the formula:............
if i select all of this space and try to drap the formula down the next cell fills with =sheet1!A11......I want it to say =sheet1!A3. If i cant drag it down then I am going to have to manually write this formula 100000000000 times.....anyoning.
View 5 Replies
View Related
Dec 19, 2007
How Do You Drag This Formula Down A Column & Make It Progress And Still Have $'s
This:
=$a$1
=$a$2
Not This
=$a$1
=$a$1
I Dont Want To Have To Drag The Formula And Then Manually Put In The $'s
View 9 Replies
View Related
Oct 12, 2009
when a user answers yes to a question a ref number is generated. Which works fine. The problem that im having is when a no is changed to yes the ref numbers get incremented but they all get misaligned.
Not sure if the tabel will come out right when i post it.
Where the no has changed to yes it has changed all the ref numbers below the no
5050Yes51Yes51Yes52Yes52No52Yes53Yes53Yes54No53Yes55
View 9 Replies
View Related
Jan 31, 2007
Here's my Formula:
Range("H2:S2").Select
Selection.AutoFill Destination:=Range("H2:S2702"), Type:=xlFillDefault
Range("H2:S2702").Select
This is dragging the formula to the end of the report. My dilemma is that this was captured with the macro recorder. Is there a way to change "S2702" with whatever row Column S ends with? Meaning this formula will be used on different sized reports so the numbers will vary. I just need a direct statement to compensate however large the report is.
View 9 Replies
View Related
Nov 4, 2013
How to copy part of a cell to a new cell when there are no spaces or specific features to use in the formula. My cell contains a data and time as such: 2013-10-22T14:05:34Z
My hope is to keep the data 2013-10-22 in one column and transfer the 14:05:34 to a new column...if the 'T' and 'Z'
View 2 Replies
View Related
Mar 13, 2009
I have a lot of titles like "A Company - Strategy A", "A Company - Strategy B" and what I am hoping to do is strip out the part after the hyphen to the cell directly above. This is the case across about 200 columns, I'd imagine this is pretty easy?
The attached spreadsheet makes it very clear.
View 10 Replies
View Related
May 26, 2006
I am having issues with this sheet. i am trying to get this formula to stick, when i drag down or across. its not holding the formula true to the first column. i am unsure of what this is called or else i would be using more specific words.
View 3 Replies
View Related
Mar 26, 2014
I am trying to get this tricky formula to work with vlookup and if error. The problem is that excel will not recognize the formula when I try to drag it down, but instead repeats the same numbers over again. I've tried some combinations using vlookup, rows, and if error, but it doesn't seem to work. So I'm trying to drag down the formula from g3:g99, to reference either cells b8:99, or whichever value I enter into f1, on down to b99.
View 4 Replies
View Related
Feb 11, 2009
If I need to replicate a formula in some cells, I usually drag it down or sideways. Is there any easier way to do this? I have large numbers of cells into which to drag the formula. Can I specify which cell range the formula is applicable to (i.e. A1:A10,000 or something) instead of dragging? If so how?
View 5 Replies
View Related
Aug 21, 2013
Let's say I have a value in B2, and a formula in C2. If I drag the formula from C2 all the way down to C20 (spreading it 18 times), the formula wants the value in B2, B3, B4, etc.. down to B20. How do I drag a formula, but have it always reference B2?
View 1 Replies
View Related
Oct 21, 2012
Im working on a financial forecast in ecxel and I'm trying to drag a formula from left to right, while referencing horizontal cells from a different tab:
Specifically, I have created a "loan schedule" which shows 12 monthly interest payments starting in cell B46 and ending in cell B57.
In a separate tab labelled "Balance Sheet", I would like to show the first interest payment ('loan schedule'!B46) in cell C28. Next, I would like to select the cell and drag it to the right so that the remaining 11 interest payments will be transferred into D29, E29, F29 etc. Unfortunately,the spreadsheet doesn't reference to the vertically listed interest payments from the loan schedule but instead references the cell to the right of the interest payment. Using "$" seems to be of no support.
View 9 Replies
View Related
Dec 9, 2012
I have been using the following formula to do conditional summing over multiple worksheets:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$AB$2:$AB$13&"'!b3:b39"),$B16,INDIRECT("'"&$AB$2:$AB$13&"'!c3:c39")))
The formula works fine, but when I drag it to the next column c3:c39 stays the same. How do I get that reference to change when I drag the formula over multiple columns.
View 1 Replies
View Related
Jul 10, 2014
I am using the formula below to sum across several worksheets, and the formula I am using works fine, but when I copy the formula across to other cells, I can't figure out how to have it update my cell range:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!A:A"),$B4,INDIRECT("'"&$A$1:$A$4&"'!G:G")))
The range A:A is ok to be static, but I need the G:G to be dynamic so when I drag it to the right it will change from G:G to H:H. I:I, etc.
View 6 Replies
View Related
Feb 19, 2014
I have a workbook with multiple sheets interacting with one another. On one of them the user is prompted to make lists of expenses in multiple columns. The column labels are in row one, and row two has the sums of all the cells below. I have every sheet in the workbook protected, so that only the cells that need to be modified are unlocked. The problem is that the user can drag cells around and change the range of the functions in row two (locked cells).
For example, A2 has the function =sum(A3:A100). But if the user drags the values in A3:A5 to A6:A8, the formula in A2 changes to =sum(A6:A100). Is there anyway that I can allow the user to drag cells (this could be a useful feature), but keep the formulas in the second row fixed?
View 5 Replies
View Related
Jul 12, 2011
appending excel sheet number in a formula. Here is the example.
In an excel book, I have sheets 1 to 50. In each sheet i have a standard format to enter the details. And in one sheet I want to keep some summary with required cells. I made a table by applying formula, for example in cell A1 i kept formula ='9'!$D$9. In this '9' refers sheet9. In the similar way i want to capture the data from all sheets.
A1 cell ='9'!$D$9
A2 cell ='10'!$D$9
A3 cell ='11'!$D$9....etc
Is there any formula to append the Sheet number automatically (like dragging the shell) intead changing sheet number manually. because it is wasting so much time.
View 3 Replies
View Related
Sep 15, 2008
I have a formula that I'd like to have increment by one, but I can't figure out how to do it.
Here's the formula:
=IF(INDIRECT("'Supervisor'!B"&ROW()-4)="Y",E12-D12,IF(INDIRECT("'Supervisor'!B"&ROW()-4)="N","0",IF(INDIRECT('"Supervisor'!B&ROW()-4)="","0")))
I'd like to have the -4 in the equation increment to -5 when I paste it to the next row without having to fix each one of them.
View 14 Replies
View Related
Dec 3, 2008
I have a worksheet that I need to filter on 8 columns. I need to do this in the form (Col A = Bob OR SAM) AND (Col B = Apple OR Pear OR Banana) AND... etc for 8 columns.
I started this thread and we reached the conclusion that I needed either a macro to list out all the possible combinations on separate lines, or a complex criteria formula.
I'm trying to put together the complex criteria, but I am further stumped by the fact that some of the columns contain multiple terms with comma delimiters, so I'm trying to match part of a string, rather than a whole string, so the usual = ISNUMBER(MATCH(Sheet1!A2,Sheet2!$A:$A,0)) won't work. I tried using =ISNUMBER(FIND(Sheet2!$A:$A,Sheet1!A2)) instead, but I think this fails because it should be an array formula. I tried normal entering and CSE entering, and neither work, so I think this is a dead end.
View 9 Replies
View Related
