When Dragging Formula Down The Formula Skips X Number Of Cells
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
ADVERTISEMENT
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
Apr 4, 2014
I want to put in cell I3 the value from cell C1, to I4 the value of C49 and so on. How can I do it?
View 5 Replies
View Related
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
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
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 1, 2014
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?
View 1 Replies
View Related
Aug 2, 2014
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 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
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
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
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
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
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
View Related
Mar 10, 2013
I've created a user form to enter some values in a few adjacent cells. When the user clicks on a button, I want several other cells to be loaded with special formulas that refer to the new values. This happens on a row by row basis. First, a working formula in that cell which is AB5 is:
Code:
=IFERROR((IF(AK5
View 1 Replies
View Related
Nov 4, 2008
I have 5 columns...I would like a formula to say if number in one cell and none in the other 4 cells to say "yes" otherwise say "no"
View 9 Replies
View Related
Jan 29, 2010
I want to drag a formula however i want one of the values of the formula to change and the other not to change. for example:
c1=a1+b1
c2=a1+b2
c3= a1+b3
: : :
how can i make it do this when i drag? a1 is not just a constant and it depends on other parameters.
View 3 Replies
View Related
Nov 15, 2009
How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example: I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2. Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.
View 3 Replies
View Related
Jul 28, 2014
I am creating a fantasy football draft board using excel. To keep it short and sweet, each individual team (one per column) has a $200 budget to draft players. Each team must fill 14 roster positions (one per row), but here's the catch; you must spend at least $1 on all of the 14 players on your roster. So you have a maximum bid amount which is your remaining budget subtracted by $1 for every remaining spot you have left to fill.
I have already created a "remaining budget" field which subtracts each drafted player's inserted dollar amounts from the $200... easy. Beneath this, I'd like to create another cell per team that tells you your "maximum bid," which is essentially 200, -1 for every cell that remains blank. I have played around with the conditional IF formulas but cannot seem to have it account for multiple cells.
As a workaround, I have made columns hidden beneath the board which contain the conditional IF, ISBLANK fields which are -1 if true and 0 if false. Then creating the "remaining budget" field that subtracts these values from 200. This works, but it's not perfect, as when you have multiple players you still need to draft and you've exhausted your budget only saving $1 for each, it overstates your "maximum bid" by $1, or when you have $X left and you need to only fill one more spot, it ill tell you your "maximum bid" is $X-1.
View 5 Replies
View Related
Feb 13, 2014
I'm trying to create a "master file" that can be used later (a tool other uses can pull up) on with datasets of varying size. I'd like to "data dump" a table of individuals with subsequent columns of various characteristics, then automatically compute various things. So then a user can dump their data into my file and "get the answer" immediately. I've seen some stuff on running one calculation on a dynamic number of cells, but let me illustrate why this is different. I want to perform a dynamic number of calculations, see below:
Each row (record) is a person, with some characteristics (numerical). Ultimately I have a few columns on the end with some computations (Excel formulas) I'd like to do. I'd like to do this for each person (that is, each row) in that data that I dump in. But the thing is, ahead of time we don't know how many people will be dumped in, and that changes (because I'd like this to be a tool...). So how do I tell Excel to compute things if there is a person in that row, but do nothing and leave cells blank if there isn't a person? Ideally I don't have millions of ifs testing for blank cells either. I'd like to do this with Excel functions only and not VBA, if at all possible.
View 1 Replies
View Related
May 24, 2006
I have cells C22:C27 with a number that could change each week. How can I get a total of the number of cells that = 0,1,3,4,5,6,7,8,9,10,11,12?
View 3 Replies
View Related
Dec 18, 2011
I need to formula to count the number of cells that meet the criteria below,
Find the letters 'AT' in some part of the cell and a blank cell next to it...
Doing a different formula for each, doesn't bring out the correct figure.
View 3 Replies
View Related
Sep 12, 2013
Formula to pull the lowest number that falls in Cells I2:L2 and the number must be 0 or Greater. Some of the numbers might be negatives and I am trying not to pull those numbers.
View 3 Replies
View Related
May 14, 2014
I have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.
Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.
So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.
View 5 Replies
View Related
Jan 14, 2014
I'm looking for the easiest way to count the number of occurrences within a cell range.
The formula that I'm currently using is:
=COUNTIF(D$5:D$8,"a*")
This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;
When I want to find multiple values in the range and count them all, I use this formula:
=COUNTIF(D$5:D$8,OR("a*","b*","c*"))
View 9 Replies
View Related
