Formula Not Recalculating When Dragged
Aug 24, 2007
I have trouble using the =Concatenate() or =(A1&B1) to combine data in one cell. It will work for the first cell but then when I drag the equation it gives me the exact same result as the first cell. But when I click to look at the formula it looks good and when I hit Enter the cell reads the correct value. So I have to open each individual cell and hit enter. What is goin on here. I have attached a small example.
Also, sometimes I will type in a formula and hit enter and it just displays the formula. It does not return a value.
View 6 Replies
ADVERTISEMENT
Apr 1, 2008
From the title "Filling Down Formula From Source That Goes Horisontally" I hope that someone understands what I mean. If I try to autofill a formula where the source cells that I want to autofill is organised in an horisontal fashion instead of vertical fashion, then the autofill always goes in an vertical fashion. Is there a way to get around this, macro, formula?
View 8 Replies
View Related
Sep 21, 2006
My worksheet "Ledger" has a column of 1-31 days, with row headings of MC/Visa, Amex, Disc & Cash. I also have sheets "1-31" within the same workbook that contain the data I need under each heading. I can get the data for "Amex" in sheet "1" to appear in the first cell underneath "Amex" in the sheet "ledger" by typing the formula ='1'!$B$29.....
View 6 Replies
View Related
Dec 13, 2013
I have a variety of different order forms in workbooks titled IT1314-0001, IT1314-0002, IT1314-0003...etc... They're all stored in the same location, but they need to be on different workbooks as each workbook is sent as an order form to a different organization, for that organization to fill in. All of the order forms have the same layout, but the user will input different information onto them.
In a summary workbook of all of these Order Forms, I need to be able to drag down an INDEX formula, such that the INDEX looks in a different workbook (the next workbook in the sequence) with each row. As you go down the rows, it should look like;
INDEX(‘[IT1314-0001.xlsPARTS A & B!$D$5:$E$5, 1, 1)
INDEX(‘[IT1314-0002.xlsPARTS A & B!$D$5:$E$5, 1, 1)
INDEX(‘[IT1314-0003.xlsPARTS A & B!$D$5:$E$5, 1, 1)
INDEX(‘[IT1314-0004.xlsPARTS A & B!$D$5:$E$5, 1, 1)
Can this be done without VBA?
View 7 Replies
View Related
Feb 8, 2010
need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?
View 4 Replies
View Related
Jan 14, 2014
I am creating a tool that is populating information off of another excel document and presenting the information in a different format then the data originally appears. Basically I am unsure how to create a formula, for example, in cell A1, that as I drag it down (A2, A3, etc) the column letter in the formula advances but the row number remains the same. In another words as the formula moves into cell A2, the "Sheet1!D3" changes to "Sheet1!E3", where as normally it would advance to "Sheet1!D4". I just started back up in excel, im sure this is way easier then I am making it seem but I have been unable to come up with a solution.
View 4 Replies
View Related
Aug 13, 2007
With the assistance of SHG, Parsnip, Dave & Daddylonglegs I have manage to put together a little app that assist our staff with scheduling dates in a calendar to avoid a specific trend.
Code provided by SHG has worked perfectly; see below:
Public DaysOff As Range
Function datNext(datLast As Date, DaysOff As Range) As Date
Dim datBeg As Date, datEnd As Date
Dim iLastPd As Integer
Dim iDay As Long, nDay As Long
Dim iYr As Integer, iMo As Integer
Though the built in DAY function on the calendar is affecting the dates that are calculated cos the function PD also references this. Whenever I change the year or month on the calendar; it changes the UDF, which is not supposed to be. If the recommended dates are going change all the time; as a result of this, it makes this app useless.
How do I get around this? Also is this what is causing the UDF to recalculate?
View 9 Replies
View Related
Jun 22, 2014
I have two Columns
AC Tm%T%
DOMEQ25%26.5000%
INTEQ17%18.0200%
DOMPR10%10.6000%
INTPR 0%0.0000%
ALTIN 0%0.0000%
DOMFI29%30.7400%
INTFI 6%0.0000%
DOMFR10%10.6000%
CASH 3%3.1800%
Total 100.00%99.64%
The Tm column is the original model percentages for each class. Class INTFI is to be excluded (6%) so I need to adjust each of the other % accordingly and still add up to 100. I thought you simply increase all other % by the excluded amount but I end up with 99.64.
The equation I am using fot T is =C15+($C$21*C15) where C21 is the 6% cell in Tm.
View 1 Replies
View Related
Jun 2, 2006
I am using the data analysis add-in to generate a random number (using the random number generation) using a normal distribution. The problem is, i want it to recalculate these numbers every time the worksheet is recalculated but the numbers seem to be values only.
View 2 Replies
View Related
Jul 31, 2007
It is working, but the problem It doesn't change the color number automatically after I change the color. There is any way to set up that option so can change automatically?
Function CellColor(rCell As Range, Optional ColorName As Boolean)
Dim strColor As String, iIndexNum As Integer
Select Case rCell.Interior.ColorIndex
Case 1
strColor = "Black"
iIndexNum = 1
Case 53
strColor = "Brown"
iIndexNum = 53
Case 52
strColor = "Olive Green"
iIndexNum = 52
Case 51
strColor = "Dark Green"
iIndexNum = 51...................
View 7 Replies
View Related
Jun 27, 2008
I have a workbook in xl2002 that has a data sheet with approx 50 columns and 500 rows which is populated from a querytable querying another workbook. There are no formulas on this data sheet.
I have another 5 sheets, only one of which actually has formulas on it and these refer to the Data sheet above (some fairly beefy Sumproduct formulas in the main, but only 1056 in total (only about 650 of these are Sumproducts - the remainder are simple = A1 type or Match formulas with the third argument as True (ie quite efficient)).
There are also 3 dynamic named ranges in the workbook, which are utilised by the Sumproducts/matches. An example Sumproduc is as follows:....
View 9 Replies
View Related
Aug 18, 2009
I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround.
The Sheet works in the following way:-User enters values, clicks a button to run a Macro,-Macro calculates all the ranges required to return a price-In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells-As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons.It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround?Macro security is set to low.The Errors I receive are the following:The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error"If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error".Any of these run from a UDF in Excel 2000 works.I have a sample workbook if this isn't clear
View 9 Replies
View Related
Dec 18, 2008
How can you prevent a cell which contains a validation list from being dragged?
View 9 Replies
View Related
Nov 16, 2009
How to create a macro tool to check whether all the formulas are dragged down correctly? Because someone may accidentally make some changes to some cells.
For example,
A1=IF(NOT($B1), -SUMIF(PCAInfo'!$C1, AQ$10:AQ$150),0)
A2=IF(NOT($B2), -SUMIF(PCAInfo'!$C2, AQ$10:AQ$150),0)
A3=IF(NOT($B4), -SUMIF(PCAInfo'!$C1, AQ$10:AQ$150),0)
A4=IF(NOT($B4), B4-SUMIF(PCAInfo'!$C4, AQ$10:AQ$150),0)
The code could test out
A2 is correct,
A3 and A4 is not correct.
View 2 Replies
View Related
Nov 13, 2006
I am trying to find a short way of creating a list using the drag down tool. Normally this isn't a problem when the numbers are at the end of the data, however they are in the middle so the drag down tool does not like it and will only copy the same data into the cells below.
Eg. the list I am creating goes as follows:
Dicso 80Gb: Cyber Records Barcodes: CR 001. ai
Disco 80Gb: Cyber Records Barcodes: CR 002.ai
Disco 80Gb: Cyber Records Barcodes: CR 003.ai
...and so on.
How do I create the formula to recognise that I want the number within the text to increase by 1 each time?
View 7 Replies
View Related
Oct 8, 2007
I currently have a VB macro written where under certain conditions user forms are shown for the user to select options, display messages, etc. When any of these forms are on the screen, and someone drags them, the menu image is 'dragged' across the screen. Is there a way from preventing this from happening?
View 14 Replies
View Related
Sep 6, 2007
I am looking for a formula that I can drag down a column that is pulling data from left to right at the top of my spreadsheet. See the attachment.
View 5 Replies
View Related
Dec 5, 2008
Is there a way I can stop a range of cells from recalculating once the cells in that range have a certain value?
I'm a complete newbie when it comes to any kind of programming and so...I'd need a little bit of handholding with this.
View 9 Replies
View Related
Oct 7, 2009
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating.
I've got a formula
=IF(COUNTA(A1)=1,TODAY(),"")
When something is typed in Cell A1 the cell with the formula will input todays date.
Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.
View 6 Replies
View Related
Jul 30, 2014
I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.
Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example
Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc
Cells A2:A6 contain the individual constants, a, b, c, etc
I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g
In cell B10: =a+b*A10+c*A10^2
I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.
View 2 Replies
View Related
Dec 22, 2009
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
View 9 Replies
View Related
Mar 27, 2014
I have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.
View 2 Replies
View Related
Aug 24, 2014
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
View 3 Replies
View Related
Apr 23, 2009
I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.
I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6
View 3 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 16, 2007
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text.
When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.
View 10 Replies
View Related
Apr 9, 2009
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.
To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.
Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
View 14 Replies
View Related
Aug 12, 2007
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
View 9 Replies
View Related
Jan 31, 2008
I am looking to average a range of cells which won't always be the same size. How do I create a formula array that will omit empty cells in my formula.
View 9 Replies
View Related
Jun 1, 2006
I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.
View 4 Replies
View Related
