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.
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?
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.....
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)
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?
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.
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?
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.
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.
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...................
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:....
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
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.
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?
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?
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.
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.
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
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.
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.
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?
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....
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.
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.
